Re: understand the pg locks in in an simple case

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