postgresql

Why does postgres truncate lock a table while waiting for a lock?

Truncate isn't waiting for the release of a select lock before placing an exclusive lock on the target, so no other selects can execute concurrently. Is there any documentation on this behavior?

You need 3 sessions to see this behavior.

tab 1 (manual transactions):

begin;
select * from tbl;

tab 2:

truncate tbl; --stalls (expected)

tab 3:

select * from tbl; --stalls (unexpected)
Solution

  • The lock queues are documented in detail in src/backend/storage/lmgr/README:

    Lock acquisition (routines LockAcquire and ProcSleep) follows these rules:

1. A lock request is granted immediately if it does not conflict with
any existing or waiting lock request, or if the process already holds an
instance of the same lock type (eg, there's no penalty to acquire a read
lock twice).  Note that a process never conflicts with itself, eg one
can obtain read lock when one already holds exclusive lock.

2. Otherwise the process joins the lock's wait queue.  Normally it will
be added to the end of the queue, but there is an exception: if the
process already holds locks on this same lockable object that conflict
with the request of any pending waiter, then the process will be
inserted in the wait queue just ahead of the first such waiter.  (If we
did not make this check, the deadlock detection code would adjust the
queue order to resolve the conflict, but it's relatively cheap to make
the check in ProcSleep and avoid a deadlock timeout delay in this case.)
Note special case when inserting before the end of the queue: if the
process's request does not conflict with any existing lock nor any
waiting request before its insertion point, then go ahead and grant the
lock without waiting.

    Which table lock block which other is documented here.

    That TRUNCATE requires an ACCESS EXCLUSIVE lock is documented here.

    Some tips how to avoid problems with these locks: