4

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?

mljrg
  • 4,430
  • 2
  • 36
  • 49

1 Answers1

5

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!

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    I am going to check that Laurenz. Oh my, that's terrible, because in that case not only we need to analyse the SQL code, but also how it is executed to infer of possible concurrency issues! This becomes even more complex do manage concurrency and performance tuning ... – mljrg Jun 12 '18 at 09:36
  • Humm ... and can the execution plan vary in runtime, even when the SQL is not changed, and so, have the database change its commit / abort decisions? – mljrg Jun 12 '18 at 09:43
  • OK, confirmed that T2's update on `x` does a seq scan as reported by `explain update ... where name = 'x'`. I even created an index on `name`, and still a seq scan. I learned that PostgreSQL's query plan varies at runtime depending on table statistics also (it uses the index only if it is advantageous). Now, does this mean that there is no way to overwrite PostgreSQL behavior to avoid seq scans like this, and thus have less unnecessary aborts? Or is this something that we developers should not care for, because there is nothing we can do to make it perform better? – mljrg Jun 12 '18 at 10:20
  • It seems strange that the internal way PostgreSQL executes its queries can affect the outcome of a transaction. I say this because at application-level T2 does not to care for `y`'s row, so the fact that PostgreSQL internally checks this row should not have influenced the commit / abort decision on T2. I note that as PostgreSQL does, it breaks the serializability of T1 and T2 in this case ... – mljrg Jun 12 '18 at 10:30
  • I think that this example shows that SERIALIZABLE isolation is not always the best option. I even start to doubt that it is ever a good option (well there might be some cases where it is useful). In this particular case, for example, READ COMMITTED works much better. This is problably the reason that SERIALIZABLE is not the default in PostgreSQL. Likewise, this is probably one reason why Oracle does not support true serialization, and probably there are always another solution not requiring it (such as serialization at application level). – mljrg Jun 12 '18 at 10:43
  • Serializable is not the default because it is more expensive. The query plan determines which rows you see, so also which rows will be locked. I don't think that is a big problem - you probably want efficient query plans anyway, serializable or not. – Laurenz Albe Jun 12 '18 at 13:25
  • I have just read that the above problem comes from the implementation of SSI (Serializable Snapshot Isolation) which can have false positives (i.e., unnecessary aborts) because it doesn't do full cycle checks for performance reasons. – mljrg Jun 12 '18 at 13:38
  • Yep. I just clarified a bit [from the sources](http://www.it.usyd.edu.au/~fekete/teaching/serializableSI-Fekete.pdf). – mljrg Jun 12 '18 at 13:48