CYBERTEC Logo

Row locks in PostgreSQL

PostgreSQL comic rower complains about row locks
© Laurenz Albe 2023

 

The PostgreSQL documentation has some information about row locks. But typically, you cannot see them in pg_locks, and not everybody knows how they work and how to track and debug row locks. This article intends to give some insight into how PostgreSQL row locks work “under the hood”.

Why are there four kinds of row locks?

In the old days, there were only FOR UPDATE and FOR SHARE. PostgreSQL used the latter for foreign key constraints: if you inserted a row in a table with a foreign key, PostgreSQL locked the referenced row with FOR SHARE to prevent concurrent deletes. Unfortunately, that also prevented concurrent updates, which was bad for concurrency.

PostgreSQL 9.4 introduced FOR KEY SHARE and FOR NO KEY UPDATE. Now the former lock is taken on the row referenced by a newly inserted row, and the latter is taken for updates that do not modify a primary or unique key column. Since FOR KEY SHARE and FOR NO KEY UPDATE are compatible, you can now update a row even if a concurrent transaction inserts a row referencing the row you are updating.

Which row lock should I take with SELECT ... FOR ...?

Most people use SELECT ... FOR UPDATE to explicitly lock a row against concurrent updates. But most of the time, that is excessive. You should only take a FOR UPDATE lock if you intend to delete the row or modify a primary or unique key column. Otherwise, it is better to take FOR NO KEY UPDATE locks, so that you do not block inserts into tables referencing the table you are updating. I have seen cases where this simple change was enough to dissolve massive locking problems into thin air.

I have never seen a need to explicitly take a share lock on a row. PostgreSQL multiversioning allows you to see the unmodified version of a row even if it is concurrently updated or deleted.

How does PostgreSQL store row locks?

PostgreSQL creates the shared memory lock table when the server starts, and it has a fixed size. Therefore, PostgreSQL does not keep row locks in the lock table. While the number of tables (and table locks) in a database cluster is (normally) moderate, the number of rows can be very large. That would engender the danger of overflow if we kept row locks in the lock table. There is the concept of lock escalation to promote many row locks to a page lock or a table lock, and some other databases do that. But apart from the extra work, lock escalation can lead to surprising and unpredictable deadlocks.

Instead, PostgreSQL stores row locks on the row itself. For that end, it repurposes the xmax system column that is otherwise unemployed when a transaction is in progress (read my article for details).

While that allows PostgreSQL to take as many row locks as it needs, it also comes at a price: if you modify a table row, the buffer that contains the row becomes dirty (if it isn't already) and PostgreSQL has to write it out to disk during the next checkpoint. So taking row locks causes extra write operations in PostgreSQL.

What happens when PostgreSQL acquires row locks?

Check if somebody else holds a lock on the row

First, we check the hint bits and the xmax of the row to see if somebody holds a lock on the row. That will become important later on.

Take a short-lived tuple lock on the row

Then we take an exclusive “tuple” lock on the row in question. This is a regular “heavyweight” lock in the shared memory lock table and serves two purposes:

  • If more than one session wants to lock the same row, they have to queue. This queue is maintained by the tuple lock.
  • The lock protects the row from concurrent modifications until we have written our lock to the row itself.

Wait for the locking transaction if necessary

If the initial check showed that other transactions are holding a lock on the row, we hang on to the tuple lock and go to sleep waiting for the transaction ID of one of the locking transactions. As soon as all blocking transactions have terminated, we can proceed. But first, we check if there were concurrent modifications of the row while we were waiting. If yes, the behavior depends on the current transaction isolation level:

  • With READ COMMITTED, we fetch the latest committed version of the row (and check if is still satisfies the WHERE condition). This is good for concurrency, but can lead to interesting anomalies.
  • With higher isolation levels, PostgreSQL throws a serialization error.

Write the row lock to the row

As soon as all blocking row locks are gone and we have the most recent version of the row, we can proceed. We modify xmax and some hint bits on the row and release the tuple lock.

The beauty of that algorithm is that no session ever needs more than two locks in the lock table, so there is no danger of running out of memory.

A clarifying example of row locks

Since the above was a bit complicated, let's look at an example where three database transactions try to lock the same row at the same time.

  • The first transaction that gets there will grab a tuple lock on the row. Then it writes the row lock to the row and releases the tuple lock immediately.
  • The second transaction that comes along sees that the first transaction has locked the row. It grabs a tuple lock on the row and goes to sleep waiting for the first transaction to finish
  • The third (and any following) transaction also sees that the row is locked. It blocks waiting for the tuple lock on the row.

If you see a tuple lock in pg_locks, that means that somebody is waiting for a row lock, no matter if that tuple lock is granted or not.

Debugging row locks with the pgrowlocks extension

pg_locks is the catalog view that allows you to examine the lock table. Unless there are conflicts on row locks, you will never see a row lock in pg_locks. So we cannot use pg_locks to see who holds which row lock. But we can use the extension pgrowlocks:

locker” is the value stored in xmax. We see that two rows in the first table block are locked:

  • The sessions 13644 and 13911 hold a FOR KEY SHARE lock on the row with ctid (0,2). Since more than one transaction holds a lock on the row, PostgreSQL created the multixact with ID 2 as a placeholder.
  • The session 13913 holds a FOR NO KEY UPDATE lock on the row with ctid (0,3).

Note that pgrowlocks() performs a sequential scan on the table, so this can take a while and will put load on your disk.

The upshot

Row locks are different, because PostgreSQL does not permanently store them in the shared memory lock table. Because of that, we normally cannot see them in pg_locks. However, there is the pgrowlocks extension that can help with debugging lock problems. Next time you need to explicitly lock a row, use SELECT ... FOR NO KEY UPDATE!

To learn more about SQL, find out about using the MERGE command in PostgreSQL v15 and higher.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

5 2 votes
Article Rating
Subscribe
Notify of
guest
9 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Fabrice Chapuis
Fabrice Chapuis
1 year ago

if the lock is stored in the row itself what the shared memory lock table is for?

jian he
jian he
1 year ago

You should only take a
lock if you intend to delete the row or modify a key column.

key column, you mean primary key? can this also apply to unique constraint column?

laurenz
laurenz
1 year ago
Reply to  jian he

A primary or unique key column. Anything that could be referenced by a foreign key. I thought that was clear from the preceding sentences, but I have edited the text to make it clearer.

Eusnong Lee
Eusnong Lee
11 days ago
  • The first transaction that gets there will grab a tuple lock on the row. Then it writes the row lock to the row and releases the tuple lock immediately.

Is tuple lock always obtained even if the hint bits and the xmax of the row show that the row is not locked by another transaction? (xmax field is empty)

Eunsong Lee
Eunsong Lee
10 days ago
Reply to  Laurenz Albe

Thanks for your reply.

There's one more question about tuple lock.
I looked through the postgres github,and I found that LockTuple() function is used when tuple lock is acquired.
But,I couldn't find LockTuple() function when a transaction proceed in case the xmax of the tuple is empty(Row is not locked)
(I used gdb to check this out)

Is there any other function for acquiring tuple lock in case the xmax of the tuple is empty?

Eunsong Lee
Eunsong Lee
9 days ago
Reply to  Laurenz Albe

Thank you again.

But..when I tested it, I found results that I couldn't understand.

I created a new table which had not been used before.
The result of checking the tuple header information was below.(using pageinspect)

ctid |xmin|xmax |lock_only|is_multi|hot_updated|keys_updated|keyshr_lock|shr_lock|t_ctid|
-----+----+-----+---------+--------+-----------+------------+-----------+--------+------+
(0,1)|1734|0 (a)|     |    |      |      |      |    |(0,1) |

And then,test scenario of mine was below.

1.TX1 :
begin;
update relation_test set c2 = 'update1' where c1=1; 
2.TX2:
begin;
update relation_test set c2 = 'update1' where c1=1; 

For the test, we checked the source using gdb.
I set breakpoints as follow.
(gdb) info b
Num   Type      Disp Enb       What
1    breakpoint   keep y  in heap_lock_tuple at heapam.c:4152
2    breakpoint   keep y  in heap_acquire_tuplock at heapam.c:4847
3    breakpoint   keep y  in heap_update at heapam.c:2994
4    breakpoint   keep y in HeapTupleSatisfiesUpdate at heapam_visibility.c:463

[TX1]
When TX1 proceeded, I could find some functions were called in order.
: heap_update() -> HeapTupleSatisfiesUpdate()

Also found that 'TM_OK' was returned as the result of calling HeapTupleSatisfiesUpdate().
But, I couldn't find heap_acquire_tuplock and heap_lock_tuple.
(I guess require_sleep is an internal variable inside the heap_lock_tuple(), but I couldn't find heap_acquie_tuplelock, heap_lock_tuple at all)

When TX2 proceeded, I could find heap_acquire_tuplock,heap_lock_tuple as you said.
:heap_update() -> HeapTupleSatisfiesUpdate() -> heap_acquire_tuplock()

I found 'TM_BeingModified' was returned as the result of calling HeapTupleSatisfiesUpdate().
And then, I could find that it calls heap_acquire_tuplock().

Here, My question is why couldn't I find heap_acquire_tuplock() in TX1 even though I find that clearly in TX2.
Could you explain me why?

Again, Thanks a lot for your reply.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    9
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram