1

I play around with Postgres serializable mode, and found some commit order that fail a transaction, but wont fail a transaction in case of different commit ordering.

For below code, I run from each transaction concurrently, but do operation on each transaction step-by-step, so I start tx 1 and complete all of its ops, then tx 2 and all of its ops, then tx 3. But after each succession I do not commit it yet. After all ops on all tx completes then I start commiting.

Table Declarations:

drop table if exists si_test;
create table si_test (name text unique, num integer);

insert into si_test values('a',10);
insert into si_test values('b',20);
insert into si_test values('c',30);

The transactions:

T1: begin transaction isolation level serializable;
T1: update si_test set num=45 where name='a';

T2: begin transaction isolation level serializable;
T2: select * from si_test where name='a'; -- -> this shows value 10
T2: update si_test set num=47 where name='b';

T3: begin transaction isolation level serializable;
T3: select * from si_test where name='b'; -- -> this shows value 20

And this is my commit order and result:

T1 -> T3 -> T2 : T2 serialization error
T1 -> T2 -> T3 : T2 serialization error
T2 -> T1 -> T3 : all committed
T2 -> T3 -> T1 : all committed
T3 -> T1 -> T2 : all committed
T3 -> T2 -> T1 : all committed

From those queries, I can say that there is a serializable schedule T3 -> T2 -> T1. I read at postgres SSI docs that they check for 2 "dangerous structure / rw-dependency" to conclude if some concurrent transactions will violate a serializable schedule, which from my tests, should not be.

I have checked the EXPLAIN ANALYZE, and it shows that my instance is using index scan to satisfy the where clause

My question is: Can someone explain what actually is going on? Where is the catch in my test?

Notes: Test run on Postgres 11.2 on Windows 10 Pro

Notes 2: Those 3 tx run on 3 different connection at once

aaron
  • 257
  • 6
  • 15
  • A new transaction doesn't start until the previous one has committed or rolled back, so what you think is several transactions is really one. You'll get a warning if you try to start a transaction when there is already an active transaction. You'd have to exanine the actual order of the statements of all statements in concurrent transactions relative to each other to figure out why a serialization error is thrown. Use simpler examples. – Laurenz Albe Jul 11 '19 at 03:18
  • I run it on 3 connection at once though, so they all run at once – aaron Jul 11 '19 at 04:20
  • I have edited the question to better show my intention – aaron Jul 11 '19 at 04:58
  • Still not enough information. Which statements are running at what time in which transaction? – Laurenz Albe Jul 11 '19 at 05:11
  • I have specified the order in my question. Basically, I run transaction in order (on 3 different connections) from start to finish, but do not commit anything (so the transactions are still dangling). Only after all 3 transaction finish their ops, I start to committing them one by one following the commit order I specify above – aaron Jul 11 '19 at 05:15
  • If you want to understand why exactly you get a serialization error, you have to know **at what exact time** each statement in each of the concurrent transactions is executed. – Laurenz Albe Jul 11 '19 at 05:53
  • can you elaborate where to check this exact time? As I know, postgres use first-committer wins (so it wont block unless a write to same data), and all my operations are not blocked whatsoever when I do this test – aaron Jul 11 '19 at 06:10
  • In PostgreSQL, *all* committers have to win. Set `log_min_duration_statement` to 0 to log all statements. – Laurenz Albe Jul 11 '19 at 06:38
  • thanks for the info, gonna test it again later – aaron Jul 11 '19 at 07:22
  • is the log at folder 'log'? logs there are only the exact queries I am using – aaron Jul 11 '19 at 08:52

0 Answers0