Обсуждение: understand the pg locks in in an simple case

Alex wrote:
> But when I check the pg_locks: session 1.  I can see no tuple lock
> there,  when I check the session 2,   I can see a
> tuple(ExclusiveLock) is granted,  but it is waiting for a
> transactionid. 
> 
> since every tuple has txn information,  so it is not hard to
> implement it this way.  but is there any benefits over the the
> straight way?   with the current implementation, what is the point
> of tuple(ExclusiveLock) for session 2?

From what I can tell the reason is that pg_locks reports the
information from the shared memory locking table, and tuple locks
are *not* maintained there, but in the "xmax" of the row itself.

To see all tuple locks in pg_locks would require a sequential
scan of all tables which have certain locks on them, which is not
going to happen.

Yours,
Laurenz Albe

On 20/08/2019 10:23, Alex wrote:
> I have troubles to understand the pg lock in the following simple 
> situation.
> 
> 
> Session 1:
> 
> 
> begin;   update  tset  a=  1  where  a=  10;
> 
> 
> Session 2:
> 
> 
> begin;  update  tset  a=  2  where  a=  10;
> 
> 
> They update the same row and session 2 is blocked by session 1 without 
> surprise.
> 
> 
> The pretty straight implementation is:
> 
> Session 1 lock the the *tuple (ExclusiveLock)* mode.
> 
> when session 2 lock it in exclusive mode,  it is blocked.
> 
> 
> But when I check the pg_locks: session 1.  I can see *no tuple 
> lock*there,  when I check the session 2,   I can see a 
> *tuple(ExclusiveLock) is granted*,  but it is waiting for a transactionid.
> 
> 
> since every tuple has txn information,  so it is not hard to implement 
> it this way.  but is there any benefits over the the straight way?  
>   with the current implementation, what is the point 
> of tuple(ExclusiveLock) for session 2?

The reason that tuple locking works with XIDs, rather than directly 
acquiring a lock on the tuple, is that the memory allocated for the lock 
manager is limited. One transaction can lock millions of tuples, and if 
it had to hold a normal lock on every tuple, you would run out of memory 
very quickly.

So it may seem that we don't need heavy-weight locks on individual 
tuples at all. But we still them to establish the order that backends 
are waiting. The locking protocol is:

1. Check if a tuple's xmax is set.
2. If it's set, obtain a lock on the tuple's TID.
3. Wait on the transaction to finish, by trying to acquire lock on the XID.
4. Update the tuple, release the lock on the XID, and on the TID.

It gets more complicated if there are multixids, or you update a row you 
have earlier locked in a weaker mode, but that's the gist of it.

We could skip the lock on the tuple's TID, but then if you have multiple 
backends trying to update or lock a row, it would be not be 
deterministic, who gets the lock first. For example:

Session A: BEGIN; UPDATE foo SET col='a' WHERE id = 123;
Session B: UPDATE foo SET col='b' WHERE id = 123; <blocks>
Session C: UPDATE foo SET col='c' WHERE id = 123; <blocks>
Session A: ROLLBACK;

Without the lock on the TID, it would be indeterministic, whether 
session B or C gets to update the tuple, when A rolls back. With the 
above locking protocol, B will go first. B will acquire the lock on the 
TID, and block on the XID lock, while C will block on the TID lock held 
by B. If there were more backends trying to do the same, they would 
queue for the TID lock, too.

- Heikki