On Wednesday, October 28, 2015 1:28 PM, Pavel Suderevsky <psuderevsky@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
