PostgreSQL为什么中止此可序列化时间表 [英] 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.

现在，以下事务T1和T2的并发执行是可序列化的，因为它等效于串行执行 T1然后T2

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)

但是，在PostgreSQL 10.4中尝试时，像这样：

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

此事务尝试提交时，数据库中止T1。为什么？

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

推荐答案

PostgreSQL使用启发式方法来确定是否中止可序列化的事务，因为这很难精确。 。因此，即使有等效的串行执行（误报），事务也可能中止。

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).

但是我怀疑在这种情况下还有其他原因。如果查看执行计划，则可能会看到顺序扫描。现在，顺序扫描读取了所有行，因此T2在更新期间已读取 y 。

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!

这篇关于PostgreSQL为什么中止此可序列化时间表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！