CODEWITHSUNDEEP
postgresqllocking
Reef Loretto
Reef Loretto

Reputation: 61

What's the exact type of lock PostgreSQL acquires when performing an `UPDATE`?

I'm aware that programmers have the option to acquire explicit locks when performing database operations, but I'd like more clarity into what type of lock Postgres acquires (automatically) when performing common operations, such as UPDATE.

Upvotes: 0

Views: 226

Answers (1)

jmelesky
jmelesky

Reputation: 4000

Well, let's look and see.

First, open two connections. In the first one (fill in the table, etc, with values appropriate to your DB):

=# BEGIN;
=# UPDATE table SET field = x WHERE id = y;

Now, switch to the other connection. Use pg_stat_activity to find the pid for the other connection:

=# SELECT pid FROM pg_stat_activity WHERE query = 'UPDATE table SET field = x WHERE id = y;';

 pid
------
 1684
(1 row)

Obviously, the pid (process id) will vary.

Alright, now use that pid to find out the locks that are in play:

=# SELECT * FROM pg_locks WHERE pid = 1684;
   locktype    | database | relation |  page  | tuple  | virtualxid | transactionid | classid | objid  | objsubid | virtualtransaction | pid |       mode       | granted | fastpath 
---------------+----------+----------+--------+--------+------------+---------------+---------+--------+----------+--------------------+-----+------------------+---------+----------
 relation      |    16384 |    16385 | [null] | [null] | [null]     |        [null] |  [null] | [null] |   [null] | 4/10730            | 743 | RowExclusiveLock | t       | t
 virtualxid    |   [null] |   [null] | [null] | [null] | 4/10730    |        [null] |  [null] | [null] |   [null] | 4/10730            | 743 | ExclusiveLock    | t       | t
 transactionid |   [null] |   [null] | [null] | [null] | [null]     |          1440 |  [null] | [null] |   [null] | 4/10730            | 743 | ExclusiveLock    | t       | f
(3 rows)

Now, both ExclusiveLocks are on the transaction and virtual transaction. We can ignore those -- they'll be around for pretty much every transaction, regardless of what it's doing.

But the RowExclusiveLock has a database and relation associated with it. The relation is the table you're updating.

So the proper answer to your question is a RowExclusiveLock.

Now you can ROLLBACK or COMMIT your update transaction and clean up. If you want to know the lock types used when you SELECT FOR UPDATE, you can follow the same procedure.

Note that this is the simplest case: updating one row of one table. More complicated updates are likely to have more locks associated with them.

Upvotes: 2

Related Questions