Re: Locks acquired by "update" statement within serializable transaction.

On Wednesday, October 28, 2015 1:28 PM, Pavel Suderevsky <psuderevsky(at)gmail(dot)com> wrote:

> I would ask for clarification about logic of locks acquired by
> update statements within serializable transactions.

> [table has primary key on id column]

> testdb=# begin transaction isolation level serializable;
> BEGIN
> testdb=# update rollover set n = 5 where id = 2;
> UPDATE 1
>
> And this is what I didn't expect:

> [no SIReadLock is taken]

> Why? How is it possible? I was expecting the similar SSI
> behaviour of this two similar stories.

This is explained in the source code with this comment:

* (6) When a write lock for a top level transaction is found to cover
* an existing SIREAD lock for the same transaction, the SIREAD lock
* can be deleted.

The logic is this: what an SIReadLock on the one tuple read would
allow us to detect is a read-write conflict should an overlapping
transaction update or delete the row which was read. That, in
combination with other dependencies, might lead to one of the
transactions being rolled back. But if we already have a write
lock on the tuple (through the xmax column), then an update or
delete of the row by another transaction would cause a write
conflict and one of the transactions will surely be rolled back.
An SIReadLock thus adds no value, so we omit it.

Basically, the snapshot isolation implementation of repeatable read
transactions bring us very close to serializable behavior; SSI
monitors for those cases where snapshot isolation fails to protect
against serialization anomalies, and this is not one of the cases.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company