SQL串行化事务是如何工作的？（SELECT/UPDATE与INSERT冲突）

Question

事务#1在符合第一个事务的条件（WHERE从句）的数据集上运行，第2个事务执行一个INSERT，并将其插入到该数据集中。如果第2个事务首先提交，那么第1个事务不应该失败吗？

我有一个如下所示的表格（在PostgreSQL 9.5 db中）

CREATE TABLE public.foo (id serial PRIMARY KEY, mynum integer);

以及以下数据

 id | mynum
----+-------
  1 |    10
  2 |    10
  3 |    10
  4 |    10
(4 rows)

我同时在两个psql控制台中运行2个串行事务：

-- both transactions
mydb=# begin;
BEGIN
mydb=# set transaction isolation level serializable;
SET

-- tx1
mydb=# select * from foo where mynum < 100;
id | mynum
----+-------
  1 |    10
  2 |    10
  3 |    10
  4 |    10
(4 rows)
--tx1: Shouldn't freeze data visible for tx1 select?

    --tx2
    mydb=# insert into foo (mynum) values (10);
    INSERT 0 1 
    -- tx2 will insert next row with id 5 in foo table
    -- Shouldn't insert of tx2 broke data snapshot visible for tx1?

--tx1
mydb=# update foo set mynum = 20 where id < 100;
UPDATE 4
-- Shouldn't here appear serialization fail or at least on tx1 commit?

    --tx2 
    mydb=# commit;
    COMMIT

--tx1 
mydb=# commit;
COMMIT
-- tx1 Commit is OK - no any error

-- implicit tx
mydb=# select * from foo;
id | mynum
----+-------
  1 |    20
  2 |    20
  3 |    20
  4 |    20
  5 |    10
(5 rows)

我想知道为什么它会这样表现，考虑到PostgreSQL文档中的说明。

引用： "为了保证真正的串行化，PostgreSQL使用谓词锁定，这意味着它保留锁定以允许确定写操作何时对与并发事务先前读取的结果产生影响，如果它先运行，则该锁定可以防止这种情况。"

链接：http://www.postgresql.org/docs/current/static/transaction-iso.html

- ALZ

Answer 1

保证并发事务有一定的串行执行顺序，可以产生相同的结果，但这不一定与提交顺序相同。在您的情况下，按顺序运行tx1，然后是tx2会给您相同的结果，因此没有理由出现串行化失败。

两个重叠的事务永远不会允许看到彼此的数据（MVCC系统确保新记录被/语句忽略）。当它们应该看到彼此的数据时，即当任一串行顺序导致第二个事务看到第一个事务写入的记录时，才会出现串行化故障。关键点在于，只有两个事务都读取并写入数据时，才会发生这种情况；否则，总是可以通过将只读事务放在前面或只写事务放在最后来对它们进行串行化。因此，您的单独插入将永远不会与任何内容冲突（但如果tx2还包含，那么您将收到错误提示）。

当涉及更多的交易时，情况就不那么简单了。这里有一长串例子在这里，可以更深入地了解序列化异常发生的时间和地点。

- Nick Barnes

---

我+1并接受这个答案，因为它是唯一的，而且似乎是正确的（特别是因为“不一定与提交顺序相同”，rw-conflict和PG WIKI链接）。我在PG社区上提出了同样的问题-答案类似。以下是链接： http://www.postgresql.org/message-id/flat/CAL93h0HEpaLHrYEr=p8dUnBnSR3k96eP+gQ4vS=[e-mail]#CAL93h0HEpaLHrYEr=p8dUnBnSR3k96eP+gQ4vS=[e-mail] - ALZ