Thread: Locks acquired by "update" statement within serializable transaction.
Hi,
I would ask for clarification about logic of locks acquired by update statements within serializable transactions.
Tried postgres 9.3.6 and postgres 9.4.4.
Story 1.
testdb=# \dS+ t
Table "public.t"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
s | integer | not null | plain | |
i | integer | | plain | |
Indexes:
"t_pkey" PRIMARY KEY, btree (s)
Has OIDs: no
testdb=# begin transaction isolation level serializable;
BEGIN
testdb=# update t set i=867 where s=2;
UPDATE 1
And this it what I've expected: SIReadLock + RowExclusiveLock:
testdb=# SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted
from pg_locks l, pg_stat_all_tables t
where l.relation=t.relid and t.relname = 't';
relname | locktype | page | virtualtransaction | pid | mode | granted
---------+----------+------+--------------------+-------+------------------+---------
t | relation | | 12/1000023 | 30865 | RowExclusiveLock | t
t | relation | | 12/1000023 | 30865 | SIReadLock | t
(2 rows)
Story 2.
testdb=# \d+ rollover
Table "public.rollover"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | not null | plain | |
n | integer | | plain | |
Indexes:
"rollover_pkey" PRIMARY KEY, btree (id)
Has OIDs: no
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:
testdb=# SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted
from pg_locks l, pg_stat_all_tables t
where l.relation=t.relid and t.relname = 'rollover';
relname | locktype | page | virtualtransaction | pid | mode | granted
----------+----------+------+--------------------+-------+------------------+---------
rollover | relation | | 12/1000031 | 30865 | RowExclusiveLock | t
(1 row)
Why? How is it possible? I was expecting the similar SSI behaviour of this two similar stories.
On Wednesday, October 28, 2015 1:28 PM, Pavel Suderevsky <psuderevsky@gmail.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
On Wednesday, October 28, 2015 1:52 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > 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. Oh, I see that your other case also had a primary key on the column used for selecting the row to update; you're probably wondering why the optimization didn't kick in for that case. From the locks it appears that in the first story a sequential table scan was used, causing a relation-level SIReadLock. To allow use of the primary key (and thus less overhead in the SSI code), you may need to analyze the table or change cost factors to encourage an index scan rather than a sequential scan. Increasing cpu_tuple_cost and effective_cache_size, and decreasing random_page_cost might nudge things in that direction. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company