Today, we are going to talk about PostgreSQL Advisory Locks. This kind of locks are created by the application and developers and, they have meaning inside the application, PostgreSQL does not enforce their use and they are there to fulfil a business or coding specific case. I was going to try to explain and to add some literature around them but, after reading PostgreSQL documentation (can be found here) I do not think it is necessary because the definition it is easy to understand and, besides, on the same page we can find the other types of locks available giving us some extra context. Instead, we are going to see some real-world code as an example.

Let’s say we have our shiny service that runs multiple instances at the same time on our production environment and, on that services, we run a scheduled task that updates one of our database tables adding a different sequence number to the existing rows (buildings) for all the existing cities. Something like:

For some curious minds about the reason why we need this ‘occurrence‘ sequence, one of the cases can be to create an endpoint to allow other systems to synchronise these buildings. We could sort using the ‘registered‘ field but, it can happen that two buildings in the same city can be registered at the same time making it impossible to warrantee the information is going to be returned always on the same order, and this can cause synchronisation problems or even missing a building due to paginated requests. We want to be able to sort them in an immutable way.

Going back to the multiple services running the tasks, we can have some ugly situations were one of the tasks running is working already and, in the middle of updating a city, when another task in a different service start processing the same city, especially if we do this on batches due to the huge amount of data we store.

One simple solution of this is to use Advisory Locks allowing us, developers, to lock a city when the task is updating it. For this purpose, PostgreSQL offers us two nice functions to work with:

• pg_advisory_lock: Obtains an exclusive session-level advisory lock, waiting if necessary.
• pg_try_advisory_lock: Obtains an exclusive session-level advisory lock if available. This will either obtain the lock immediately and return ‘true‘, or return ‘false‘ without waiting if the lock cannot be acquired immediately.

The full list of system administration functions related with advisory locks can be found here.

For the purposes of the example code, we are going to implement, we will be using the second one because it makes sense if one city it is been processed, we do not want to process it again till the next scheduled time.

public void assignOccurrenceSequences() {
    final List<String> cities = buildingDao.retrievePendingCities();

    for (final String city : cities) {
        final int lockId = Math.abs(Hashing.sha256().newHasher()
            .putString(city, StandardCharsets.UTF_8)
            .hash().asInt());

        logger.info("Taking advisory_lock {} for city {} ", lockId, city);
        try (Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(true);

            final boolean lockObtained;
            try (Statement statement = connection.createStatement()) {
                lockObtained = statement.execute(format("select pg_try_advisory_lock(%d)", lockId));
            }

            if (lockObtained) {
                try {
                    final int updates = buildingDao.populateOccurrenceSequences(city);
                    logger.info("Assigning {} sequences for city {}", updates, city);
                } finally {
                    try (Statement statement = connection.createStatement()) {
                        statement.execute(format("select pg_advisory_unlock(%d)", lockId));
                    }

                    logger.info("Released advisory_lock {} for city {}", lockId, city);
                }
            } else {
                logger.info("advisory_lock {} for city {} already taken", lockId, city);
            }
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }
    }
}

On lines 5, 6 and 7 we create a unique lock id we will be using to establish the lock and make sure all the tasks running calculate the same id. And yes, before someone points it, we are assuming that ‘city‘ is unique. With this generated lock id, we can try to acquire the lock. In case of success, we proceed with the update. In case of fail, we skip that city and proceed with the rest of the cities.