Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Using Named Database Locks

DZone's Guide to

Using Named Database Locks

See how you can make and use table-agnostic, database-specific locks to help with your concurrency needs.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

In a beginner’s guide to concurrency, I mentioned advisory locks. These are not the usual table locks – they are table-agnostic, database-specific way to obtain a named lock from your application. Basically, you use your database instance for centralized application-level locking.

What could it be used for? If you want to have serial operations, this is a rather simple way – no need for message queues, or distributed locking libraries in your application layer. Just have your application request the lock from the database, and no other request (regardless of the application node, in case there are multiple) can obtain the same lock.

There are multiple functions that you can use to obtain such a lock – in PostgreSQL, in MySQL. The implementations differ slightly — in MySQL you need to explicitly release the lock, in PostgreSQL, a lock can be released at the end of the current transaction.

How to use it in a Java application, for example with spring. You can provide a locking aspect and a custom annotation to trigger the locking. Let’s say we want to have sequential updates for a given entity. In the general use-case that would be odd, but sometimes we may want to perform some application-specific logic that relies on sequential updates.

@Before("execution(* *.*(..)) && @annotation(updateLock)")
public void applyUpdateLocking(JoinPoint joinPoint, UpdateLock updateLock) {
    int entityTypeId = entityTypeIds.get(updateLock.entity());
    // note: letting the long id overflow when fitting into an int, because the postgres lock function takes only ints
    // lock collisions are pretty unlikely and their effect will be unnoticeable
    int entityId = (int) getEntityId(joinPoint.getStaticPart().getSignature(), joinPoint.getArgs(),
    updateLock.idParameter());

    if (entityId != 0) {
        logger.debug("Locking on " + updateLock.entity() + " with id " + entityId);
        // using transaction-level lock, which is released automatically at the end of the transaction
        final String query = "SELECT pg_advisory_xact_lock(" + entityTypeId + "," + entityId + ")";
        em.unwrap(Session.class).doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                connection.createStatement().executeQuery(query);
            }
        });
    }
}

What does it do?

  • It looks for methods annotated with @UpdateLock and applies the aspect.
  • the UpdateLock annotation has two attributes – the entity type and the name of the method parameter that holds the ID on which we want to lock updates.
  • the entityTypeIds basically has a mapping between a String name of the entity and an arbitrary number (because the Postgres function requires a number, rather than a string).

That doesn’t sound very useful in the general use-case, but if for any reason you need to make sure a piece of functionality is executed sequentially in an otherwise concurrent, multi-threaded application, this is a good way.

Use this database-specific way to obtain application-level locks rarely, though. If you need to do that often, you probably have a bigger problem – locking is generally not advisable. In the above case, it will lock simply on a single entity ID, which means it will rarely mean more than two requests waiting at the lock (or failing to obtain it). The good thing is, it won’t get more complicated with sharding – if you lock on a specific ID, and it relies on a single shard, then even though you may have multiple database instances (which do not share the lock), you won’t have to obtain the lock from a different shard.

Overall, it’s a useful tool to have in mind when faced with a concurrency problem. But consider whether you don’t have a bigger problem before resorting to locks.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,lock ,concurrency ,postresql

Published at DZone with permission of Bozhidar Bozhanov, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}