VACUUM Locks

I’ve had some difficulty lately in understanding some locking activity going on in my databases, so I decided to run some tests to better understand how things work (using an 8.3 server). Specifically – I want to know how the autovacuum process does or doesn’t affect sessions so I can rule it out from my investigations.

Session 1 – run a VACUUM on a large table (so it takes a while)

mydb=# vacuum big_table;

Session 2 – Update some rows in the table

mydb=# begin;
BEGIN
mydb=# update big_table set cola = 'x' where id = 123456789;
UPDATE 112

Session 3 – Examine pg_locks

This is the VACUUM session:

mydb=# select * from pg_locks where pid=2136;
  locktype  | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |           mode           | granted
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+--------------------------+---------
 relation   | 25693263 | 27681060 |      |       |            |               |         |       |          | 118/92993          | 2136 | RowExclusiveLock         | t
 relation   | 25693263 | 27681056 |      |       |            |               |         |       |          | 118/92993          | 2136 | RowExclusiveLock         | t
 relation   | 25693263 | 27679696 |      |       |            |               |         |       |          | 118/92993          | 2136 | ShareUpdateExclusiveLock | t
 virtualxid |          |          |      |       | 118/92993  |               |         |       |          | 118/92993          | 2136 | ExclusiveLock            | t
 relation   | 25693263 | 27681058 |      |       |            |               |         |       |          | 118/92993          | 2136 | RowExclusiveLock         | t
 relation   | 25693263 | 27681059 |      |       |            |               |         |       |          | 118/92993          | 2136 | RowExclusiveLock         | t
(6 rows)

So the VACUUM has taken a ShareUpdateExclusiveLock on the table and a number of RowExclusiveLock – one for each index.

This is the UPDATE session:

mydb=# select * from pg_locks where pid=20666;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
 relation      | 25693263 | 27681056 |      |       |            |               |         |       |          | 98/115834          | 20666 | RowExclusiveLock | t
 relation      | 25693263 | 27679696 |      |       |            |               |         |       |          | 98/115834          | 20666 | RowExclusiveLock | t
 virtualxid    |          |          |      |       | 98/115834  |               |         |       |          | 98/115834          | 20666 | ExclusiveLock    | t
 relation      | 25693263 | 27681060 |      |       |            |               |         |       |          | 98/115834          | 20666 | RowExclusiveLock | t
 transactionid |          |          |      |       |            |     219901739 |         |       |          | 98/115834          | 20666 | ExclusiveLock    | t
 relation      | 25693263 | 27681059 |      |       |            |               |         |       |          | 98/115834          | 20666 | RowExclusiveLock | t
 relation      | 25693263 | 27681058 |      |       |            |               |         |       |          | 98/115834          | 20666 | RowExclusiveLock | t
(7 rows)

The UPDATE has an Exclusive Lock on its virtual transaction id (which every session does) and since it actually updated some rows, it also has an Exclusive Lock on a real transaction id. It also took Exclusive Row locks on the table and three indexes. Note that none of the locks are waiting, so there are no blocking issues here.

In other words – a VACUUM does not block an UPDATE. This is actually what I expected from looking at this table in the documentation: