When scaling an application to multiple instances, a problem arises when multiple instances attempt to perform an operation on a single resource. Imagine what issues could occur when updating a single database row by numerous instances at once. For databases, we do have the option to use database transactions and locks. But what if we need to achieve concurrency control for 3rd party API calls? For that, we can reach for advisory locks.

Advisory locks are general-purpose locks provided by a database, which have application meaning only - thus are available for us to use, granted that we handle the locking and unlocking of locks ourselves. This is possible because the locks are not tied up with the table nor any records.

The input is a lock name, and therefore it accepts a simple string. The locks in MySQL are session-level, which means they are not bound to the transactions, and once acquired, they need to be explicitly released. Hence, it is not released when the transactions commit or roll-back. Once a particular lock is acquired within one session, it blocks any other incoming requests by another session that tries to acquire the same lock.

Let's look at the API functions that allows us to do so:

GET_LOCK(str, timeout) - Get a named lock
IS_FREE_LOCK(str) - Whether the named lock is free
IS_USED_LOCK(str) - Whether the named lock is in use and returns the connection identifier if true
RELEASE_LOCK() - Release the named lock

The behavior for locks in MySQL changed drastically after its upgrade from 5.6 to 5.7. Since that version, the GET_LOCK no longer releases any existing locks. It is a significant change, and I recommend using at least 5.7 if you want to use advisory locks effectively.

I recently came across a pitfall that I did not read the documentation on locking functions properly and did not notice the short sentence, yet very important -- "Names are locked on a server-wide basis". We have a development DB server that holds databases for multiple applications. It happened when we were undeliberately locking the same name, and I spent about half an hour analyzing why my application was at some point locked out. To prevent this, we added a prefix to the locks in the form that would form the lock like this appName/env/lock.

It is desired to achieve a transactional-level, rather than session-level, lock. From the documentation, the locks can be reused in the same session without being blocked; hence only once will it be needed to release. Thanks to this observation, we can use the transactional event listener to execute the code after completing the transactional. This is achievable thanks to @TransactionalEventListener(phase = Transaction Phase.AFTER_COMPLETION). Thanks to this mechanism, we can simulate the transactional-level lock. Whenever the transaction ends, the lock would be released.

Another point to consider is that the locks in MySQL are blocking. Unlike PostgreSQL where the API offers both blocking and non-blocking, MySQL is very limited in this way.

Implementation

The lock name is being hashed because the lock name has a size limitation of only 64 characters, and hashing the names seems to be a good way to approach this. I have chosen the SHA1, whose hashes are of length 40.

We use a negative timeout for the classic locking, which means indefinite timeout, forcing the clients to wait until the lock is truly released. This mechanism can be furthermore enhanced with a positive integer, checking the possible result (1 => success, 0 => timeout, NULL => an error) and handling this. However, I am using the simplest form purely just for the demonstration in this text.

As for the non-blocking method, we could use the combination of first checking the lock if it is used with IS_FREE_LOCK and then locking it, but we would lose the atomicity of such operation. Instead, we will be utilizing the timeout 0 to immediately check the result if the lock is being used or not.

The repository is implemented as following:

@Repository
@RequiredArgsConstructor
class AdvisoryLockRepository {

	private final EntityManager entityManager;

	void lock(String lockName) {
		entityManager
				.createNativeQuery("SELECT GET_LOCK(SHA1(:lockName), -1)")
				.setParameter("lockName", lockName)
				.getSingleResult();
	}

	void releaseLock(String lockName) {
		entityManager
				.createNativeQuery("SELECT RELEASE_LOCK(SHA1(:lockName))")
				.setParameter("lockName", lockName)
				.getSingleResult();

	}

	boolean tryLock(String lockName) {
		return entityManager
				.createNativeQuery("SELECT GET_LOCK(SHA1(:lockName), 0)")
				.setParameter("lockName", lockName)
				.getSingleResult().equals(BigInteger.ONE);
	}
}

As for the service, we are enforcing methods to be called only from active transactions.

@Service
@RequiredArgsConstructor
public class AdvisoryLockService {

	private final AdvisoryLockRepository repository;
	private final ApplicationEventPublisher applicationEventPublisher;
	
	@Transactional(propagation = Propagation.MANDATORY)
	public void lock(String lockName) {
		lock(lockName);
	}
	
	@Transactional(propagation = Propagation.MANDATORY)
	public boolean tryLock(String lockName) {
		return repository.tryLock(lockName);
	}
	
	@TransactionalEventListener(phase = TransactionPhase.AFTER_COMPLETION)
	public void unlock(String key) {
		repository.releaseLock(key);
	}
	
	private void lock(String key) {
		repository.lock(key);
		this.applicationEventPublisher.publishEvent(key);
	}
}

Usages

When executing an operation update, for example, for the user with ID 1, one would use

@Transactional
public void update(...) {
  advisoryLockService.lock("appName/dev/update/user/1");
  // logic for updating
}

Since this is blocking and we set the timeout for -1, the other clients and requests would have to wait until this transaction releases the lock.

As for the scheduled tasks, we would use the non-blocking variant. This ensures that only one instance of the application runs the task. The usage is as followings:

@Scheduled(cron = "...")
public void sendingReminders() {
  if (!advisoryLockService.tryLock("appName/dev/sendReminders")) {
      log.info("Could not obtain the lock for sending reminder emails. Skipping.");
	  	return;
  }
  // proceed with the sending email logic
}

Spring integration

If you don't want to be tight with the implementation of the database system, you could check out the DefaultLockRepository, an integration of Spring, to handle this for you. However, the locks are recorded in the supporting tables to create those needs. The schemas can be found here. This can be further combined with the JdbcLockRegistry, but this is not the main topic of this text.