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 | tt | relation | | 12/999940 | 30865 | RowExclusiveLock | tt | relation | | 16/1356096 | 32079 | AccessShareLock | tt | relation | | 11/1411878 | 30869 | RowExclusiveLock | tt | relation | | 12/999940 | 30865 | SIReadLock | tt | 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.
