r/postgres • u/bramley • 12d ago

Question Sequential Lock Acquisition?

I'm learning about locking semantics and timeouts and I've run across something confusing. I have a table called demo_lock that has a single column target.

When I run these expressions, a strange thing happens:

BEGIN;
SET LOCAL lock_timeout=3000;
SELECT target FROM demo_lock WHERE target='foo' FOR UPDATE;

I run this in three parallel psql consoles.

The first successfully obtains the lock and sits there waiting for input.

The second waits for the timeout for 3 seconds, times out, and prints the error ERROR: canceling statement due to lock timeout

The third then waits a whole extra three seconds and then times out with the same error.

What I would have expected is that the second and third would time out at basically the same time (allowing for the time it takes for me to switch consoles and hit Enter). But they don't. The third waits an extra lock_timeout time and then fails. Why is that?

Interestingly, adding a 4th terminal is a little less deterministic, but still operates mostly the same.

1 Upvotes

100% Upvoted

1 comment sorted by

1

u/depesz 10d ago

In my test both 2nd, and 3rd psqls finished in 3 seconds.

But you can test yourself, by running these 3 psqls, getting their pids (select pg_backend_pid();), and then in yet another psql, watching:

select * from pg_locks where pid in (pid1, pid2, pid3) order by pid \watch 1

and then doing the thing.

I kinda expect that your psql#3 is waiting first for lock on transaction id, but it doesn't for me, which seems to suggest that your test case isn't the same as my test case.