Exploring Query Locks in Postgres

Understanding query locking is key to using postgres concurrently. So let’s look at an example to learn more about how locking works and how to see what’s going on within your database.

Playing in the sandbox

To play around with locks let’s first create a sandbox.

1
2
3
4
5
6
7
8
9
10

CREATE DATABASE sandbox;

CREATE TABLE toys (
  id serial NOT NULL,
  name character varying(36),
  usage integer NOT NULL DEFAULT 0,
  CONSTRAINT toys_pkey PRIMARY KEY (id)
);

INSERT INTO toys(name) VALUES('car'),('digger'),('shovel');

Now let’s open two postgres consoles to play in the sandbox. For clarification let’s name them Alice and Bob. You can SET the console prompt with:

1

sandbox=# \SET PROMPT1 '[Alice] %/%R%# '

Now Alice takes a look for what kind of toys there are.

1
2
3
4
5
6
7
8
9

[Alice] sandbox=# BEGIN;
BEGIN
[Alice] sandbox=# SELECT * FROM toys;
 id |  name  | usage
----+--------+-------
  1 | car    |     0
  2 | digger |     0
  3 | shovel |     0
(3 rows)

Note we introduce the statement with BEGIN to open the transaction explicitly and keep it open until we commit or rollback.

If Bob does the same he would get the same immediate output.

1
2
3
4
5
6
7
8
9

[Bob] sandbox=# BEGIN;
BEGIN
[Bob] sandbox=# SELECT * FROM toys;
 id |  name  | usage
----+--------+-------
  1 | car    |     0
  2 | digger |     0
  3 | shovel |     0
(3 rows)

That is the two SELECT statements do not interfere and can safely run concurrent without blocking each other. Well that’s what we expect from a reliable high performance database.

pg_lock

Nevertheless our transactions are still open. Let’s take a look on a third console, let’s call it Eve, what kind of locks are acquired.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

sandbox=# \SET PROMPT1 '[eve] %/%R%# '
[eve] sandbox=# SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();
  locktype  | relation  |      mode       | tid | vtid  |  pid  | granted
------------+-----------+-----------------+-----+-------+-------+---------
 relation   | toys_pkey | AccessShareLock |     | 6/268 | 45265 | t
 relation   | toys      | AccessShareLock |     | 6/268 | 45265 | t
 virtualxid |           | ExclusiveLock   |     | 6/268 | 45265 | t
 relation   | toys_pkey | AccessShareLock |     | 1/282 | 45263 | t
 relation   | toys      | AccessShareLock |     | 1/282 | 45263 | t
 virtualxid |           | ExclusiveLock   |     | 1/282 | 45263 | t
(6 rows)

Each active lock is stored in pg_catalogs.pg_lock view. To make sure we only see locks on the sandbox database we join pg_catalog.pg_database and filter bydatname=’sandbox’ OR datname IS NULL`.

To not see locks from our own query we also filter out entries from our own backend_pid. NOT pid = pg_backend_pid();

The relation column is typecast into regclass to make it human readable.

Let’s read the fifth row:

1
2
3

  locktype  | relation  |      mode       | tid | vtid  |  pid  | granted
------------+-----------+-----------------+-----+-------+-------+---------
 relation   | toys      | AccessShareLock |     | 1/282 | 45263 | t

The relation toys is locked with AccessShareLock by virtual transaction 1/282 from pid 45263 and the lock is granted. That is the transaction got the lock. Everything is fine Bob and Alice are happy as they can both concurrently see what toys are available.

Note that each transaction also hold an `ExclusiveLock on its own virtualxid that is their virtual transaction ID.

Now Alice decides to grab the car:

1
2

[Alice] sandbox=# UPDATE toys SET usage = usage+1 WHERE id = 1;
UPDATE 1

As you can see she got it successfully. Let’s take a look at what happens to the locks:

transactionid

1
2
3
4
5
6
7
8
9
10
11
12

   locktype    | relation  |       mode       |   tid    | vtid  |  pid  | granted
---------------+-----------+------------------+----------+-------+-------+---------
 relation      | toys_pkey | AccessShareLock  |          | 6/268 | 45265 | t
 relation      | toys      | AccessShareLock  |          | 6/268 | 45265 | t
 virtualxid    |           | ExclusiveLock    |          | 6/268 | 45265 | t
 relation      | toys_pkey | AccessShareLock  |          | 1/282 | 45263 | t
 relation      | toys_pkey | RowExclusiveLock |          | 1/282 | 45263 | t
 relation      | toys      | AccessShareLock  |          | 1/282 | 45263 | t
 relation      | toys      | RowExclusiveLock |          | 1/282 | 45263 | t
 virtualxid    |           | ExclusiveLock    |          | 1/282 | 45263 | t
 transactionid |           | ExclusiveLock    | 24273800 | 1/282 | 45263 | t
(9 rows)

Two more locks are added. Alice got a RowExclusiveLock on toys that is the car is now to here. There is also a real transactionid added for Alice on which Alice holds `ExclusiveLock. For every transaction that potentially changes the state of the database a transactionid is added.

MVCC

But as the transaction is not committed yet Bob still sees the old data.

1
2
3
4
5
6
7

[Bob] sandbox=# SELECT * FROM toys;
 id |  name  | usage
----+--------+-------
  1 | car    |     0
  2 | digger |     0
  3 | shovel |     0
(3 rows)

As it is not clear whether Alice will commit or rollback her transaction Bob sees the state of the table as it was before Alice started her transaction. Postgres uses MVCC - Multi Version Concurrency Control to make sure that each transaction always sees a consistent state of the database.

Blocking Queries

Let’s say Bob also wants the car (a pattern that you can see every day in sandboxes):

1

[Bob] sandbox=# UPDATE toys SET usage = usage + 1 WHERE id = 1;

You realize nothing really happens because Bob has to wait until Alice finishes her transaction.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

   locktype    | relation  |       mode       |   tid    | vtid  |  pid  | granted
---------------+-----------+------------------+----------+-------+-------+---------
 relation      | toys_pkey | AccessShareLock  |          | 6/268 | 45265 | t
 relation      | toys_pkey | RowExclusiveLock |          | 6/268 | 45265 | t
 relation      | toys      | AccessShareLock  |          | 6/268 | 45265 | t
 relation      | toys      | RowExclusiveLock |          | 6/268 | 45265 | t
 virtualxid    |           | ExclusiveLock    |          | 6/268 | 45265 | t
 relation      | toys_pkey | AccessShareLock  |          | 1/282 | 45263 | t
 relation      | toys_pkey | RowExclusiveLock |          | 1/282 | 45263 | t
 relation      | toys      | AccessShareLock  |          | 1/282 | 45263 | t
 relation      | toys      | RowExclusiveLock |          | 1/282 | 45263 | t
 virtualxid    |           | ExclusiveLock    |          | 1/282 | 45263 | t
 transactionid |           | ExclusiveLock    | 24273800 | 1/282 | 45263 | t
 tuple         | toys      | ExclusiveLock    |          | 6/268 | 45265 | t
 transactionid |           | ExclusiveLock    | 24273801 | 6/268 | 45265 | t
 transactionid |           | ShareLock        | 24273800 | 6/268 | 45265 | f
(14 rows)

Now a transactionid for Bob is added and Bob is asking to get a ShareLock on Alice’s transactionid. The typical “mom I wan’t to play with the car too” pattern. As both locks conflict with each other Bob’s request is not granted and he needs to wait until Alice releases her ExclusiveLockon her transactionid by finishing her transaction.

pg_stats_activity

There is another interesting pg_catalog view called pg_stat_activity which shows you what kind of queries are going on:

1
2
3
4
5
6
7

[eve] sandbox=# SELECT query,state,waiting,pid FROM pg_stat_activity
WHERE datname='sandbox' AND NOT (state='idle' OR pid=pg_backend_pid());
                     query                     |        state        | waiting |  pid
-----------------------------------------------+---------------------+---------+-----
 UPDATE toys SET usage = usage+1 WHERE id = 1; | idle in transaction | f       | 45263
 UPDATE toys SET usage = usage+1 WHERE id = 1; | active              | t       | 45265
(2 rows)

Here we see that Alice’s query is waiting for the transaction to commit while Bob’s query is active and waiting.

Now let’s combine the two to see which query is waiting for whom.

1
2
3
4
5
6
7
8
9
10
11
12
13

[eve] sandbox=# SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='sandbox';

 blocked_pid |                 blocked_query                 | blocking_pid |                blocking_query
-------------+-----------------------------------------------+--------------+-----------------------------------------------
       45265 | UPDATE toys SET usage = usage+1 WHERE id = 1; |        45263 | UPDATE toys SET usage = usage+1 WHERE id = 1;
(1 row)

Here we can clearly see which query is blocked by which statement.

Now if Alice decided to rollback (or commit) her transaction her ExclusiveLock would release and Bob would get the ShareLock on her transactionid. If Bob now commits his transaction, the row would get UPDATEd depending on whether Alice commited or rolled back.

1
2

[Alice] sandbox=# ROLLBACK;
ROLLBACK

1
2
3
4
5
6
7
8
9

[Bob] sandbox=# COMMIT;
COMMIT
[Bob] sandbox=# SELECT * FROM toys;
 id |  name  | usage
----+--------+-------
  2 | digger |     0
  3 | shovel |     0
  1 | car    |     1
(3 rows)

Of course if Bob and Alice would have decided to take different toys no such blocking situation would have appeared.

Explicit Locking

Another typical sandbox pattern is that some kid wants to take it all without really needing it.

Let’s say Alice decides to take the access exclusive.

1
2
3
4

[Alice] sandbox=# BEGIN;
BEGIN
[Alice] sandbox=# LOCK TABLE toys IN ACCESS EXCLUSIVE MODE;
LOCK TABLE

Now Bob has to wait although Alice doesn’t really grab any toy.

1
2

[Bob] sandbox=# BEGIN; UPDATE toys SET usage = usage+1 WHERE id = 2;
BEGIN

And our lock table would look like this.

1
2
3
4
5
6
7
8
9
10
11
12

[eve] sandbox=# SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();
  locktype  | relation |        mode         | tid | vtid  |  pid  | granted
------------+----------+---------------------+-----+-------+-------+---------
 virtualxid |          | ExclusiveLock       |     | 6/284 | 45265 | t
 virtualxid |          | ExclusiveLock       |     | 1/294 | 45263 | t
 relation   | toys     | RowExclusiveLock    |     | 6/284 | 45265 | f
 relation   | toys     | AccessExclusiveLock |     | 1/294 | 45263 | t
(4 rows)

As Alice is holding AccessExclusiveLock without (yet) changing the database state she has no transactionid and as Bob didn’t get the RowExclusiveLock on the toys table he also doesn’t have any real transactionid. Thus our above query to see blocking statements won’t help (as we join on `transactionid).

1
2
3
4
5
6
7
8
9
10
11

[eve] sandbox=# SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='sandbox';
 blocked_pid | blocked_query | blocking_pid | blocking_query
-------------+---------------+--------------+----------------
(0 rows)

So for Eve it would look like everything is fine. While

1
2
3
4
5
6

[eve] sandbox=# SELECT pid, query, now() - query_start  AS waiting_duration
FROM pg_catalog.pg_stat_activity WHERE datname='sandbox' AND waiting;
  pid  |                     query                     | waiting_duration
-------+-----------------------------------------------+------------------
 45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | 00:03:16.503233
(1 row)

clearly shows that there is a query waiting. Note we added the difference between now()and query_start to see for how long the query is waiting.

So let’s take another look:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[eve] sandbox=# SELECT blockingl.relation::regclass,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.relation=blockedl.relation
  AND blockingl.locktype=blockedl.locktype AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='sandbox';
 relation | blocked_pid |                 blocked_query                 |   blocked_mode   | blocking_pid |              blocking_query               |    blocking_mode
----------+-------------+-----------------------------------------------+------------------+--------------+-------------------------------------------+---------------------
 toys     |       45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | RowExclusiveLock |        45263 | LOCK TABLE toys IN ACCESS EXCLUSIVE MODE; | AccessExclusiveLock
(1 row)

Here we can see again whats really going on. By joining the relation on which the lock is hold.

Now Alice has been told that it’s not nice to take an explicit lock without really needing it. So she commits her transaction without doing anything and Bob can get a toy.

1
2

[Alice] sandbox=# COMMIT;
COMMIT

1
2

UPDATE 1
[Bob] sandbox=#

Note Bob’s transaction is still open. If we take a look at the locks table we see:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();
   locktype    | relation  |       mode       |   tid    | vtid  |  pid  | granted
---------------+-----------+------------------+----------+-------+-------+---------
 relation      | toys_pkey | RowExclusiveLock |          | 6/284 | 45265 | t
 virtualxid    |           | ExclusiveLock    |          | 6/284 | 45265 | t
 relation      | toys      | RowExclusiveLock |          | 6/284 | 45265 | t
 transactionid |           | ExclusiveLock    | 24273819 | 6/284 | 45265 | t
(4 rows)

So only after Bob got the RowExclusiveLock a real transactionid for his transaction was added. So Bob is fine and commits.

1
2

[Bob] sandbox=# COMMIT;
COMMIT

RowExclusiveLock

As Alice is undecided which toy to take and not allowed to take explicit locks she takes another approach.

1
2
3
4
5
6
7
8

[Alice] sandbox=# BEGIN; SELECT * FROM toys FOR UPDATE;
BEGIN
 id |  name  | usage
----+--------+-------
  2 | digger |     0
  3 | shovel |     0
  1 | car    |     1
(3 rows)

That is “I want to see all toys and maybe I’ll take one but as I’m undecided I don’t want any other to take one until I made my decision.” (Another typical sandbox pattern)

Bob who has decided to take the shovel won’t get it and needs to wait:

1

[Bob] sandbox=# UPDATE toys SET usage = usage+1 WHERE id = 2;

While Eve sees the following situation:

1
2
3
4
5
6
7
8
9
10
11
12
13

   locktype    | relation  |       mode       |   tid    | vtid  |  pid  | granted
---------------+-----------+------------------+----------+-------+-------+---------
 relation      | toys_pkey | RowExclusiveLock |          | 6/277 | 45265 | t
 relation      | toys      | RowExclusiveLock |          | 6/277 | 45265 | t
 virtualxid    |           | ExclusiveLock    |          | 6/277 | 45265 | t
 relation      | toys_pkey | AccessShareLock  |          | 1/288 | 45263 | t
 relation      | toys      | RowShareLock     |          | 1/288 | 45263 | t
 virtualxid    |           | ExclusiveLock    |          | 1/288 | 45263 | t
 tuple         | toys      | ExclusiveLock    |          | 6/277 | 45265 | t
 transactionid |           | ExclusiveLock    | 24273809 | 1/288 | 45263 | t
 transactionid |           | ShareLock        | 24273809 | 6/277 | 45265 | f
 transactionid |           | ExclusiveLock    | 24273810 | 6/277 | 45265 | t
(10 rows)

Bob who clearly wants to change the database state got an transactionid 24273810 and again has to wait for Alice to get ShareLock on her transactionid 24273809.

Join Locks and Activities

So this time we need our first approach to join locks and activities.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

[eve] sandbox=# SELECT blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockinga.pid AS blocking_pid,
  blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  blockingl.transactionid = blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
AND blockinga.datname='sandbox';
 blocked_pid |                 blocked_query                 | blocking_pid |         blocking_query
-------------+-----------------------------------------------+--------------+--------------------------------
       45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; |        45263 | SELECT * FROM toys FOR UPDATE;
(1 row)

So let’s combine both to be 100% sure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[eve] sandbox=# SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid,
  blockinga.query as blocking_query, blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted
AND blockinga.datname='sandbox';
  locked_item  | blocked_pid |                 blocked_query                 | blocked_mode | blocking_pid |         blocking_query         | blocking_mode
---------------+-------------+-----------------------------------------------+--------------+--------------+--------------------------------+---------------
 transactionid |       45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | ShareLock    |        45263 | SELECT * FROM toys FOR UPDATE; | ExclusiveLock
(1 row)

As Eve can’t remember this long query she decides to put that into a view;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

[eve] sandbox=# CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

Note we added the waiting_duration to see for how long the query was blocked, and added the current_database() function to automatically filter by the database the view belongs to.

Now Eve can simply fire:

1
2
3
4
5

[eve] sandbox=# SELECT * from lock_monitor;
  locked_item  | waiting_duration | blocked_pid |                 blocked_query                 | blocked_mode | blocking_pid |         blocking_query         | blocking_mode
---------------+------------------+-------------+-----------------------------------------------+--------------+--------------+--------------------------------+---------------
 transactionid | 00:01:02.143922  |       45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | ShareLock    |        45263 | SELECT * FROM toys FOR UPDATE; | ExclusiveLock
(1 row)

and is always able to see what the kids are up to. Now Eve decides to get a Latte Macchiato and read the postgres manual on explicit locking to get an idea of the different lock types and their conflict modes.

Comments