Ungranted access exclusive lock blocks access share lock

user_7635030 0
Viewed 514

From tests we made in PostgreSQL, it seems an ungranted access exclusive lock to a table is blocking an access share lock. Below describes the test that we executed.

Session 1:

begin;
select * from users where id = 1;

Note: Transaction is intentionally left open for the test

Session 2:

alter table users add column foo boolean;

Note: This statement is blocked by the statement in Session 1. Access exclusive lock is not yet granted.

Session 3:

select * from users where id = 2;

The last statement in Session 3 is blocked.

How is it blocked when the access exclusive lock from the alter statement has not yet been granted (since it's blocked by Session 1)? Is there something that we're missing?

locking postgresql sql database
1 Answers

The documentation describes that:

One server process blocks another if it either holds a lock that conflicts with the blocked process's lock request (hard block), or is waiting for a lock that would conflict with the blocked process's lock request and is ahead of it in the wait queue (soft block).

So locks that are not granted are queued, and you cannot jump the queue ahead of a process with a conflicting lock request. This is so that no process can be blocked forever (unless blocking transactions take forever).

user_64643080
Related