CODEWITHSUNDEEP
postgresqlconcurrencytransactionsserializable
mljrg
mljrg

Reputation: 4620

Why does PostgreSQL abort this serializable schedule

Theory states that a group of concurrent transactions is serializable if, and only if, their concurrent execution is equivalent to one of their possible serial executions.

Now the following concurrent execution of transactions T1 and T2 is serializable, because it is equivalent to the serial execution "T1 then T2"

T1: r1x   w1y  c1
T2:    w2x   c2

(i.e., T1 reads x, T2 writes x, T1 writes y, T2 commits, and finally, T1 commits)

However, when tried in PostgreSQL 10.4, like this:

T1: begin
T1: set transaction isolation level serializable;
T2: begin
T2: set transaction isolation level serializable;
T2: update variables set value = value + 1 where name = 'x'
T1: update variables set value = value + 1 where name = 'y'
T2: commit
T1: commit

the database aborts T1 when this transaction tries to commit. Why?

Upvotes: 4

Views: 409

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

PostgreSQL uses heuristics to determine whether to abort a serializable transaction or not, because it would be too hard to be exact. So it can happen that transactions are aborted even if there is an equivalent serial execution (false positives).

But I suspect a different reason in this case. If you look at the execution plans, you will probably see sequential scans. Now a sequential scan reads all rows, so T2 has read y during its update.

The behavior of serializable transactions depends on the execution plan chosen!

Upvotes: 5

Related Questions