我们从一个简单的表开始：
CREATE TABLE test (id INT PRIMARY KEY);
和几行：
INSERT INTO TEST VALUES (1); INSERT INTO TEST VALUES (2);
使用我最喜欢的JDBC查询工具(ExecuteQuery),我将两个会话窗口连接到此表所在的数据库.它们都是事务性的(即auto-commit = false).我们称他们为S1和S2.
每个代码都相同：
1:DELETE FROM test WHERE id=1; 2:INSERT INTO test VALUES (1); 3:COMMIT;
现在,以慢动作运行,在Windows中一次执行一个.
S1-1 runs (1 row deleted) S2-1 runs (but is blocked since S1 has a write lock) S1-2 runs (1 row inserted) S1-3 runs,releasing the write lock S2-1 runs,now that it can get the lock. But reports 0 rows deleted. HUH??? S2-2 runs,reports a unique key constraint violation
现在,这在sqlServer中运行良好.当S2执行删除时,它会报告删除1行.然后S2的插入工作正常.
我怀疑Postgresql在该行存在的表中锁定索引,而sqlServer锁定实际的键值.
我对吗？这可以使用吗？
这里重要的一点是,在sql标准下,在READ COMMITTED事务隔离级别运行的事务中,限制是未提交事务的工作必须不可见.当已提交事务的工作变得可见时,依赖于实现.你指出的是两种产品如何选择实现它的区别.这两种实现都没有违反标准的要求.
这是Postgresql中发生的事情,详细说明：
S1-1 runs (1 row deleted)
旧行保留在原位,因为S1可能仍然会回滚,但是S1现在对该行保持锁定,以便尝试修改该行的任何其他会话将等待S1提交或回滚.对表的任何读取仍然可以看到旧行,除非它们尝试使用SELECT FOR UPDATE或SELECT FOR SHARE锁定它.
S2-1 runs (but is blocked since S1 has a write lock)
S2现在必须等待看到S1的结果.如果S1回滚而不是提交,S2将删除该行.请注意,如果S1在回滚之前插入新版本,则从任何其他事务的角度来看,新版本将永远不会存在,也不会从任何其他事务的角度删除旧版本.
S1-2 runs (1 row inserted)
该行独立于旧行.如果对id = 1的行进行了更新,则旧版本和新版本将相关,并且S2可以在取消阻止时删除该行的更新版本.新行恰好具有与过去存在的某行相同的值,并不会使其与该行的更新版本相同.
S1-3 runs,releasing the write lock
所以S1的变化是持久的.一排消失了.已添加一行.
S2-1 runs,now that it can get the lock. But reports 0 rows deleted. HUH???
内部发生的事情是,如果更新了行的一个版本到同一行的下一个版本,则会有一个指针.如果删除该行,则没有下一个版本.当一个READ COMMITTED事务从写入冲突的块中唤醒时,它会跟随该更新链到最后;如果该行尚未删除,并且仍然符合查询的选择条件,则将对其进行处理.此行已被删除,因此S2的查询继续进行.
S2在扫描表格期间可能会或可能不会到达新行.如果是这样,它将看到在S2的DELETE语句启动后创建了新行,因此它不是可见的行集的一部分.
如果Postgresql从一开始就使用新快照重新启动S2的整个DELETE语句,它的行为与sql Server相同.由于性能原因,Postgresql社区没有选择这样做.在这个简单的例子中,你永远不会注意到性能上的差异,但是当你被阻止时,如果你有一千万行进入DELETE,你肯定会.这里有权衡Postgresql选择性能,因为速度更快的版本仍然符合标准的要求.
S2-2 runs,reports a unique key constraint violation
当然,该行已经存在.这是图片中最不令人惊讶的部分.
虽然这里有一些令人惊讶的行为,但一切都符合sql标准,并且根据标准在“特定于实现”的范围内.如果您假设某些其他实现的行为将出现在所有实现中,那肯定会令人惊讶,但Postgresql非常努力避免READ COMMITTED隔离级别中的序列化失败,并允许一些与其他产品不同的行为以实现那.
现在,我个人并不是任何产品实现中READ COMMITTED事务隔离级别的忠实粉丝.从交易的角度来看,它们都允许竞争条件创造出令人惊讶的行为.一旦某人习惯了一种产品允许的奇怪行为,他们往往会认为“正常”和另一种产品所选择的权衡是奇怪的.但是,对于任何实际上没有实现SERIALIZABLE的模式,每个产品都必须进行某种权衡. Postgresql开发人员选择在READ COMMITTED中绘制线条的地方是最小化阻塞(读取不阻止写入和写入不阻止读取)并最小化序列化失败的可能性.
该标准要求SERIALIZABLE事务是默认值,但大多数产品不会这样做,因为它会导致性能受到更严格的事务隔离级别的影响.当选择SERIALIZABLE时,有些产品甚至不能提供真正可序列化的事务 – 最值得注意的是Oracle和9.1之前的Postgresql版本.但是使用真正的SERIALIZABLE交易是避免竞争条件产生惊人效果的唯一方法,SERIALIZABLE交易总是必须阻止以避免竞争条件或回滚某些交易以避免发展中的竞争条件. SERIALIZABLE事务的最常见实现是严格的两阶段锁定(S2PL),它具有阻塞和序列化失败(以死锁的形式).
完全披露：我与麻省理工学院的Dan Ports合作,使用名为Serializable Snapshot Isolation的新技术为Postgresql版本9.1添加真正的可序列化事务.
