CODEWITHSUNDEEP
postgresqlpostgresql-9.6
NotGaeL
NotGaeL

Reputation: 8484

Can page level locks cause a deadlock between 2 transactions trying to lock 2 different rows on the same page?

According to postgres docs section 13.3.2

In addition to table and row locks, page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers normally need not be concerned with page-level locks, but they are mentioned here for completeness.

My understanding is I need not to be concerned whether my transactions are large enough that they may lock enough rows long enough that a situation happens where T1 has a lock on R1 on P1 and wants to lock R2 on P2 but cannot do that because T2 has a lock on R3 on P2 and it is not releasing it until it gets a lock on R4 on P1.

T -> transaction
P -> page
R -> row

Is this assumption correct or should I make my transactions short enough that this kind of lock is less likely to happen?

Upvotes: 0

Views: 189

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

Page level locks are always held for a short time, no matter how long your transaction takes. Different from other locks, they are released before commit, immediately when they are no longer required.

Moreover, page locks are always taken in a way that they cannot participate in deadlocks (unless PostgreSQL has a bug).

Details:

Page locks are taken using the LockPage function in src/backend/storage/lmgr/lmgr.c. Currently, they are only used with GIN indexes during index cleanup, when the pending list is integrated into the main index (function ginInsertCleanup in src/backend/access/gin/ginfast.c): the metapage is locked to prevent concurrent execution of the function.

Upvotes: 3

Related Questions