Re: Predicate Locks for writes?

On Sat, Oct 7, 2017 at 2:26 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> SERIALIZABLE looks for chains of rw cases.
>
> When we perform UPDATEs and DELETEs we search for rows and then modify
> them. The current implementation views that as a read followed by a
> write because we issue PredicateLockTuple() during the index fetch.
>
> Is it correct that a statement that only changes data will add
> predicate locks for the rows that it modifies?
>

I'm not very aware of how this SI code exactly works. But it seems that
once we update row, read SIReadLock on that tuple is released, because
we're already holding stronger lock. I made simple experiment.

# begin;
BEGIN
Time: 0,456 ms
smagen(at)postgres=*# select * from test where id = 1;
id │ val
────┼─────
1 │ xyz
(1 row)

*# select locktype, database, relation, page, tuple, mode, granted from
pg_locks where pid = pg_backend_pid();
locktype │ database │ relation │ page │ tuple │ mode │ granted
────────────┼──────────┼──────────┼──────┼───────┼─────────────────┼─────────
relation │ 12558 │ 11577 │ NULL │ NULL │ AccessShareLock │ t
relation │ 12558 │ 65545 │ NULL │ NULL │ AccessShareLock │ t
relation │ 12558 │ 65538 │ NULL │ NULL │ AccessShareLock │ t
virtualxid │ NULL │ NULL │ NULL │ NULL │ ExclusiveLock │ t
page │ 12558 │ 65545 │ 1 │ NULL │ SIReadLock │ t
tuple │ 12558 │ 65538 │ 0 │ 3 │ SIReadLock │ t
(6 rows)

*# update test set val = 'xyz' where id = 1;

*# select locktype, database, relation, page, tuple, mode, granted from
pg_locks where pid = pg_backend_pid();
locktype │ database │ relation │ page │ tuple │ mode │
granted
───────────────┼──────────┼──────────┼──────┼───────┼──────────────────┼─────────
relation │ 12558 │ 11577 │ NULL │ NULL │ AccessShareLock │ t
relation │ 12558 │ 65545 │ NULL │ NULL │ AccessShareLock │ t
relation │ 12558 │ 65545 │ NULL │ NULL │ RowExclusiveLock │ t
relation │ 12558 │ 65538 │ NULL │ NULL │ AccessShareLock │ t
relation │ 12558 │ 65538 │ NULL │ NULL │ RowExclusiveLock │ t
virtualxid │ NULL │ NULL │ NULL │ NULL │ ExclusiveLock │ t
transactionid │ NULL │ NULL │ NULL │ NULL │ ExclusiveLock │ t
page │ 12558 │ 65545 │ 1 │ NULL │ SIReadLock │ t
(8 rows)

Once we update the same tuple that we read before, SIReadLock on that tuple
disappears.

PredicateLockTuple() specifically avoids adding an SIRead lock if the
> tuple already has a write lock on it, so surely it must also be
> correct to skip the SIRead lock if we are just about to update the
> row?
>
> I am suggesting that we ignore PredicateLockTuple() for cases where we
> are about to update or delete the found tuple.
>

If my thoughts above are correct, than it would be a reasonable
optimization. We would skip cycle of getting SIReadLock on tuple and then
releasing it, without any change of behavior.

> ISTM that a Before Row Trigger would need to add an SIRead lock since
> that is clearly a read.
>

Yes, because depending on result of "Before Row Trigger" update might not
really happen. That SIReadLock on tuple is necessary.
However, ISTM that we could place SIReadLock on tuple after Before Row
Trigger and only in the case when trigger has cancelled an update.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company