Postgresql SSI: read/write dependencies

From: Pavel Suderevsky <psuderevsky(at)gmail(dot)com>
To: pgsql-general(at)PostgreSQL(dot)org
Subject: Postgresql SSI: read/write dependencies
Date: 2015-10-28 18:46:06
Message-ID: CAEBTBzt4qcWDgityGKgFmzbWE85B4g2eVZ87NV4u-pm20yB32w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I am working on better insight of postgresql serialization mechanism.
The main question is - how to determine serialization behaviour at system
level and predict exception:

> ERROR: could not serialize access due to read/write dependencies among
> transactions
> DETAIL: Reason code: Canceled on identification as a pivot, during commit
> attempt.
> HINT: The transaction might succeed if retried.

I was doing some tests with simple select/update/insert operations and I
have the following assumtion. One of two concurrent transactions will get
failed with mentioned exception after the first commit if:
1. Both transactions have acquired SIReadLock and RowExclusiveLock.
2. Both transactions have cross on the the same rows in their snapshots and
xid of any of these rows has been changed in result of first commit.

Whether these two statements are correct?
What else aspects should be mentioned when investigating this? How these
dependencies can be found?

I am not really sure if some particular examples are needed but as example
both transactions have been successfully commited with:

relname | locktype | page | virtualtransaction | pid | mode
>> | granted
>
>
>> ---------+----------+------+--------------------+-------+------------------+---------
>
> t | relation | | 12/999940 | 30865 | AccessShareLock
>> | t
>
> t | relation | | 12/999940 | 30865 | RowExclusiveLock
>> | t
>
> t | relation | | 16/1356096 | 32079 | AccessShareLock
>> | t
>
> t | relation | | 11/1411878 | 30869 | RowExclusiveLock
>> | t
>
> t | relation | | 12/999940 | 30865 | SIReadLock
>> | t
>
> t | relation | | 16/1356096 | 32079 | SIReadLock
>> | t
>
>
And second transaction got failed when:

relname | locktype | page | virtualtransaction | pid | mode
> | granted
>
> ---------+----------+------+--------------------+-------+------------------+---------
> t | relation | | 16/1356112 | 32079 | AccessShareLock
> | t
> t | relation | | 16/1356112 | 32079 | RowExclusiveLock
> | t
> t | relation | | 11/1411894 | 30869 | RowExclusiveLock
> | t
> t | relation | | 11/1411894 | 30869 | SIReadLock
> | t
> t | relation | | 16/1356112 | 32079 | SIReadLock
> | t

Table:

> Table "public.t"
> Column | Type | Modifiers |
> Storage | Stats target | Description
>
> --------+---------+-----------------------------------------------+---------+--------------+-------------
> s | integer | not null default nextval('t_s_seq'::regclass) | plain
> | |
> i | integer | | plain
> | |
> Indexes:
> "t_pkey" PRIMARY KEY, btree (s)
> Has OIDs: no

In common way I got following locks behaviour in serialization level
transactions:
select: SIReadLock + AccessShareLock
update: SIReadLock + RowExclusiveLock
insert: RowExclusiveLock

Queries are the simplest:
update t set i=867 where s=1;
insert into t (i) values (999);
select * from t;

Also I have two extra questions related to this case after reading and
trying https://wiki.postgresql.org/wiki/SSI.
1. Primary Colors - "the predicate locking used as part of conflict
detection works based on pages and tuples actually accessed, and there is a
random factor used in inserting index entries which have equal keys, in
order to minimize contention". Really random? There are no way to determine
possible fail at system level?
2. Deposit Report (not even a question but perplexity) - it is strange that
T2 doesn't gurantee that no new receipts will be added with obsolete bench.
Well, yes, operations are serialized actually, but it turns out that if I
want no new receipts to be added with obsolete bench I need to add extra
select in T1. =)

I will be very thankfull for your explanation of these patterns.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2015-10-28 18:50:00 Re: Locks acquired by "update" statement within serializable transaction.
Previous Message Jim Nasby 2015-10-28 18:21:20 Re: Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5