Locks acquired by "update" statement within serializable transaction.

From: Pavel Suderevsky <psuderevsky(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Locks acquired by "update" statement within serializable transaction.
Date: 2015-10-28 16:13:32
Message-ID: CAEBTBztT-FRK+it6xQpomxZqcjb91HU_n-+qC11w=CDDYSpTUQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-10-28 16:48:59 Re: how to insert stream into table using nodejs?
Previous Message Leonardo 2015-10-28 16:08:05 Re: how to insert stream into table using nodejs?