Recently, I have experienced certain problems while executing multiple transactions concurrently on top of Postgres while working on my everyday tasks in SIXT. This persuaded me to write this blog. While Postgres is amazing at running multiple transactions at the same time, there are a few cases in which Postgres need to block a transaction using a lock. One has to be careful about which locks a transaction should acquire and the high-level abstractions provided by Postgres are difficult to understand. With this Blog, I will try to demystify the locking behaviours in Postgres and give advice on common issues faced.

PostgreSQL has amazing support for executing complex, concurrent and ACID transactions. To make sure that concurrent/complex transactions run perfectly Postgres uses several layers of locks to serialise changes to critical sections of the database.

We will cover 3 types of locks here:

  1. Table Locks
  2. Row Locks
  3. Advisory Locks

Table Locks:

Table level locks blocks read and write while executing DDL. Its primary purpose is to block read/writes when changes are made to the underlying table structure using DDL commands like ALTER TABLE. However, not all DDL commands block read/writes some block each other.

Whether a transaction can acquire a lock depends on whether its lock level conflicts with that of the holder(s) of the lock

I have tried to compile some of the most common Postgres commands and whether they can run concurrently with each other on the same table.

Concurrent execution with SELECT INSERT, UPDATE, DELETE CREATE INDEX, CONC, VACCUM ANALYZE CREATE INDEX CREATE TRIGGER ALTER TABLE, DROP TABLE, TRUNCATE, VACCUM FULL
SELECT Y Y Y Y Y N
INSERT, UPDATE, DELETE Y Y Y N N N
CREATE INDEX, CONC, VACCUM ANALYZE Y Y N N N N
CREATE INDEX Y N N Y N N
CREATE TRIGGER Y N N N N N
ALTER TABLE, DROP TABLE, TRUNCATE, VACCUM FULL N N N N N N

In the above table, Y signifies that the commands can run concurrently without any blocking and N indicates that the command blocks each other.

Example:

Psql session 1:

CREATE TABLE department {
    id UUID primary key,
    name text 
};

BEGIN;
ALTER TABLE department ADD COLUMN head_dept varchar;

Psql session 2:

SELECT * from department;
<Waiting for a lock>

If we go back to Session 1 and run COMMIT we will notice that the second session finishes immediately afterwards. Locks are always existing until a COMMIT or a ROLLBACK.

One other thing to be aware of is that Postgres uses lock queues. If we run an ALTER TABLE command it goes into the queue and blocks until all other existing queries on the table finishes to execute. And, any SELECT immediately followed will be blocked until the ALTER TABLE command is finished even if it is not yet running.

Row Locks:

Row locks serialises concurrent changes to rows. Every row in Postgres is protected with a lock.

Two types:

  • Shared – Multiple transactions can hold this lock concurrently.
  • Exclusive – Once transaction can hold this lock.

If concurrent transactions try to modify the same row, one of it will be blocked on a ROW LOCK. Postgres also offers commands to lock rows without even modifying them like SELECT … FOR UPDATE and SELECT … FOR SHARE, which lets the user temporarily prevent changes to a set of rows.

Concurrent access to row with SELECT SELECT FOR SHARE SELECT FOR UPDATE UPDATE, DELETE
SELECT Y Y Y Y
SELECT FOR SHARE Y Y N N
SELECT FOR UPDATE Y N N N
UPDATE, DELETE Y N N N

Example

Psql session 1:

INSERT INTO table department VALUES (‘91a36231-aea9-46ae-bc64-2cb2005d72a9’, ‘Chemistry’);

BEGIN;
SELECT * FROM department WHERE id = ‘91a36231-aea9-46ae-bc64-2cb2005d72a9’ FOR UPDATE;

Psql session 2:

UPDATE department SET name = ‘Physics’ WHERE id = = ‘91a36231-aea9-46ae-bc64-2cb2005d72a9’;
<Waiting for a lock>

Again, as previously discussed in the Table locks example if you run COMMIT in the first session, the second session finishes immediately afterwards.

Advisory Locks:

Postgres offers a really cool feature of providing lock mechanisms using application code and this feature is called Advisory Locks. These locks are database based. Advantage of having database locks is we can handle different threads properly in case of distributed processing or distributed locking. Locks are obtained on abstract numbers and what that number would mean is entirely up to the user.

Two types :

  • Session level
  • Transaction level

Session level:

This means a lock is obtained for a connection to the database. The lock requests can stack, so if you request a lock on the same number twice within the same connection you will get the lock twice but you also have to release the same lock twice before other connections can access it. Moreover, we can only release the lock in the same connection.

Examples:

Non-Blocking:

Session 1:

SELECT pg_try_advisory_lock(23); -- returns true, first instance of lock on number 23 was obtained
SELECT pg_try_advisory_lock(23); -- returns true, second instance of lock on number 23 was obtained
SELECT pg_advisory_unlock(23); -- returns true, one instance of lock on number 23 was released

Session 2:

SELECT pg_try_advisory_lock(23); -- returns false, theres's still one locked instance left
SELECT pg_advisory_unlock(23); -- returns false, instance can't be released from another session

Session 1:

SELECT pg_advisory_unlock(23); -- returns true, last instance of lock on number 23 was released

Session 2:

SELECT pg_try_advisory_lock(23); -- returns true, one instance of lock on number 23 was obtained
SELECT pg_try_advisory_lock(1, 23); -- returns true, one instance of lock on numbers 1 and 23 was obtained
SELECT pg_advisory_unlock_all; -- returns nothing, but both instances above were released

Blocking

Similar to try_ locks above but it doesn’t return anything, when a lock cannot be obtained it waits until it can.

Session 1:

SELECT pg_advisory_lock(23); -- returns nothing, but finishes immediately which means that one instance of lock on number 23 was obtained

Session 2:

SELECT pg_advisory_lock(23); -- returns nothing and doesn't finish, waits for that one instance of lock on number 23 to be released

Session 1:

SELECT pg_advisory_unlock_all(); -- returns nothing, but the instance is released and the pg_advisory_lock function in Session 2 doesn't wait anymore, one instance of the lock is obtained for Session 2

Session 2:

SELECT pg_advisory_unlock_all(); -- returns nothing, but the instance obtained above for Session 2 is released

Transaction level:

These locks are at the scope of transaction level and are released as soon as the transaction is committed.

Example:

Transaction 1:

SELECT pg_try_advisory_xact_lock(23); -- returns true, one instance of lock on number 23 was obtained and released automatically if nothing more happened within the transaction

Transaction 2:

SELECT pg_try_advisory_xact_lock(23); -- returns true, since the release above was automatic, one instance of lock on number 23 was obtained and also released automatically

Transaction 1:

SELECT pg_try_advisory_xact_lock(23), pg_sleep(10); -- returns true, one instance of lock on number 23 was obtained and will be released automatically after 10 seconds

Transaction 2:

SELECT pg_try_advisory_xact_lock(23); -- if 10 seconds haven't passed yet, it will return false

Applications:

We have solved a couple of issues using the above Advisory lock concepts within our applications:

  • Distributed processing
  • Deadlock prevention

Distributed Processing

Usecase

As part of a recent fleet management project, we had a use case to develop an RPC endpoint which internally integrates with a third party API and facilitates downloading bulk data corresponding to provided parameters like makes and languages.

Iteration1

  • Implemented by calling the vendor’s endpoint for all makes and languages.
  • Tried downloading the entire data in a single transaction.
  • The issue with the above approach was it was taking a lot of time to complete.
  • Tried for 20 makes and 4 languages, it took more than half an hour

Iteration2

  • Since mostly our applications are deployed in a multi host kubernetes cluster, we thought of distributing the processing to all the 3 hosts in production.
  • Divided the bigger transaction into smaller transaction per make and langauge in the application using a for loop.
  • Used the Pg Advisory transaction lock with a hash value generated with hash(make + langauge) which is unique always.
  • When the loop described in point 2 is executed, it will execute simultaneously in all 3 hosts.
  • Since we have a transaction lock per make and langauge. So, if one host gets the lock for one make-langauge combination, the other host won’t execute it and will acquire a lock on the other make-language combination and so on.
  • Each host will execute and process a single make-langauge combination.
  • Processing this way helped us reduce the processing time from ~30 mins to a few seconds.

Deadlock Prevention

Usecase

We solved another interesting problem while building our fleet re-marketing platform. As a task scheduling solution, we developed a library called Batch-Executor. The tasks within this were scheduled using shedlock. It started to give us a deadlock when the scale increased. We solved this issue using pg advisory locks.

Problem

Shedlock tries to execute a transaction on a single host within a multi host kubernetes cluster. It has a parameter called lockAtleastFor where we specify the time for which the lock on the particular task should be held and this also means that the task should be finished within that time frame. Issue here comes when a long running transaction comes which does not get executed within the particular time frame specified as lockAtleastFor,so the lock is released without the previous transaction getting completed. Then the other transaction starts executing. Now, we have both previous and new transaction trying to access the same rows in the database which was giving us a deadlock like situation and our application comes to a halt.

Solution

We changed the locking mechanism from shedlock to pg advisory transaction lock. This helped us to remove the deadlock situation by releasing the lock acquired by a transaction only when it is completed. So, a new transaction will begin only when the previous one is finished and the lock is released. At any point in time there won’t be any 2 transactions conflicting with each other. They will be serialised

Conclusion:

Postgres locks aren’t scary once you understand their behaviour. Locking is one of the mechanisms that Postgres uses to safely implement concurrent ACID transactions. While you don’t even have to think about locking most of the time, you may occasionally see a transaction taking way longer than usual. Your first reaction might be: Why is it slow? But often the right question is: Why is it waiting for a lock? We hope this post will help you understand locking behaviours in Postgres and diagnose any database locking issues you might encounter.

References:

  • https://www.postgresql.org/docs/current/explicit-locking.html