前言
昨天介绍了事务写冲突上Oracle、MySQL、PostgreSQL上的不同之处，今天将要介绍的是for update和delete冲突。
测试案例
我们将使用以下案例
| - | Oracle | MySQL | PostgreSQL | |||
|---|---|---|---|---|---|---|
| 时间线 | session1 | session2 | session 1 | session 2 | session 1 | session2 |
| 1 | select * from test; | select * from test; | select * from test; | select * from test; | select * from test; | select * from test; |
| 2 | begin; | begin; | ||||
| 3 | select * from test where id1=(select min(ID1) from test) for update; | select * from test where id1=(select min(ID1) from test) for update; | select * from test where id1=(select min(ID1) from test) for update; | |||
| 4 | begin; | begin; | ||||
| 5 | select * from test where id1=(select min(ID1) from test) for update; | select * from test where id1=(select min(ID1) from test) for update; | select * from test where id1=(select min(ID1) from test) for update; | |||
| 6 | delete from test where id1=1; | delete from test where id1=1; | delete from test where id1=1; | |||
| 7 | commit; | commit; | commit; |
有两个会话，session 1在时间点3执行带子查询的for update语句，根据子查询min函数返回的条件锁住相关行。session 2在时间点5同样也执行带子查询的for update语句，也想查询锁住相关的行。此时在时间点6会话1删除子查询返回结果的这一行。然后观察session 2查询的结果。
问:此时会话1执行commit后，会话2会查到什么数据？
答案揭晓
为便于显示，这里使用Excel截图展示结果。Oracle和MySQL在执行结果上是一致的， PostgreSQL是另一种执行结果。为什么会有不一致的结果呢？让我们再看官方文档是怎么说这种情况的？
Oracle
请参阅Database Development Guide第8章中的SQL Processing for Application Developers章节中对Explicitly Acquiring Row Locks的一段描述。
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/sql-processing-for-application-developers.html#GUID-F91A26B7-2C41-4CC5-BB07-AA7E9ED58CAF
Note:
The return set for a
SELECT
FOR
UPDATE
might change while the query is running; for example, if columns selected by the query are updated or rows are deleted after the query started. When this happens,SELECT
FOR
UPDATE
acquires locks on the rows that did not change, gets a read-consistent snapshot of the table using these locks, and then restarts the query to acquire the remaining locks.If your application uses the
SELECT
FOR
UPDATE
statement and cannot guarantee that a conflicting locking request will not result in user-caused deadlocks—for example, through ensuring that concurrent DML statements on a table never affect the return set of the query of aSELECT
FOR
UPDATE
statement—then code the application always to handle such a deadlock (ORA-00060) in an appropriate manner.
SELECT
FOR
UPDATE
查询运行时的返回集可能会更改；例如，如果查询开始后更新了查询选择的列或删除了行。发生这种情况时，SELECT
FOR
UPDATE
获取未更改的行上的锁，使用这些锁获取表的读取一致快照，然后重新启动查询以获取剩余的锁。
MySQL
目前没有明确的文档说明，测试结果与Oracle结果相同，所以应该采用了和Oracle相同的机制。如果发现锁定记录发生了变化，重新启动查询并重新获得MIN(ID)的值以确保语句级别的读一致性。
PostgreSQL
与前面的写冲突模式相似，“当第一个会话更新时，如果第一个会话删除了该行，则第二个更新过程将忽略该行。”
结论
在写冲突和for update与delete之间冲突进行了测试。测试结果发现，Oracle,MySQL,PostgreSQL在事务隔离级别为READ-COMMITTED下有不同的行为，说实话，从中立角度来看，我更喜欢Oracle和 MySQL的行为。因此我们将Oracle数据库迁移到 PostgreSQL，需要考虑到应用程序关于此类并发性的问题。
