暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL | OracleMysqlPostgreSQL三种数据库的事务差异，谁有点怪?(一)

PostgreSQL | OracleMysqlPostgreSQL三种数据库的事务差异，谁有点怪?(一)

励志成为PostgreSQL大神 2021-03-25
873

前言

Oracle12.2和 PostgreSQL13.1的默认事务隔离级别为READ COMMITTED，而 MySQL8的默认事务隔离级别为REPEATABLE-READ，本文将介绍这三种类型在“READ-COMMITTED”级别下的一些区别。

测试案例

我们将使用以下案例

-Oracle
MySQL
PostgreSQL
时间线session1session2session 1session 2session 1session2
1select * from test;select * from test;select * from test;select * from test;select * from test;select * from test;
2

begin;
begin;
3update test set b = b + 1;
update test set b = b + 1;
update test set b = b + 1;
4


begin;
begin;
5
delete from t1 where b = 3;
delete from t1 where b = 3;
delete from t1 where b = 3;
6commit;
commit;
commit;
7
commit;
commit;
commit;
8select * from test;select * from test;select * from test;select * from test;select * from test;select * from test;

有两个会话，Session1在时间点3执行全表update，并且没有提交。现在的Session2在时间5根据条件进行delete(被会话1阻塞)。

问:此时会话1执行commit后，会话2会按照会话1更新后的值进行delete吗？

答案揭晓

为便于显示，这里使用Excel截图展示结果。Oracle和MySQL在执行结果上是一致的， PostgreSQL是另一种执行结果。为什么会有不一致的结果呢？让我们看看官方文档是怎么说的？

Oracle

请参阅Oracle Concept第9章中的Data Concurrency and Consistency章节中。隔离级别Read Committed Isolation Level。

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/data-concurrency-and-consistency.html#GUID-DB571DA8-864F-4FE9-93B9-3EC2DD0604FE

Conflicting Writes in Read Committed Transactions

In a read committed transaction, a conflicting write occurs when the transaction attempts to change a row updated by an uncommitted concurrent transaction.

The transaction that prevents the row modification is sometimes called a blocking transaction. The read committed transaction waits for the blocking transaction to end and release its row lock.

The options are as follows:

  • If the blocking transaction rolls back, then the waiting transaction proceeds to change the previously locked row as if the other transaction never existed.
  • If the blocking transaction commits and releases its locks, then the waiting transaction proceeds with its intended update to the newly changed row.

在这里，我们可以清楚地看到，如果阻塞事务提交并释放其锁，等待的事务将其预期更新继续到新修改的行。也就是说被阻塞者会再次读取修改后的结果然后执行。

MySQL

参考MySQL 8.0文档第15.7.2.1 Transaction Isolation Levels中READ COMMITTED说明。

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html#isolevel_read-committed

Using READ COMMITTED
has additional effects:

  • For UPDATE
    or DELETE
    statements, InnoDB
    holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE
    condition. This greatly reduces the probability of deadlocks, but they can still happen.
  • For UPDATE
    statements, if a row is already locked, InnoDB
    performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE
    condition of the UPDATE
    . If the row matches (must be updated), MySQL reads the row again and this time InnoDB
    either locks it or waits for a lock on it.

在这里，最后一段明确的说明是，对于UPDATE 语句如果行已锁定，那么 InnoDB将执行"半一致"读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否与UPDATE的WHERE条件匹配。如果行匹配(必须更新)，MySQL 将再次读取该行，而这次InnoDB锁定或等待该行锁定。

MySQL和Oracle效果一致，就是被阻塞者会再次读取修改后的结果然后执行。

PostgreSQL

参考PostgreSQL文档13.2.1. Read Committed Isolation Level中的READ COMMITTED说明。

https://www.postgresql.org/docs/13/transaction-iso.html#XACT-READ-COMMITTED

UPDATE
, DELETE
, SELECT FOR UPDATE
, and SELECT FOR SHARE
commands behave the same as SELECT
in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE
clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row. In the case of SELECT FOR UPDATE
and SELECT FOR SHARE
, this means it is the updated version of the row that is locked and returned to the client.

在PostgreSQL文档中也有清晰的说明。第二个更新程序会在第一个更新程序提交时忽略该行(如果第一个更新程序删除了该行)。

最终结论

在Oracle和 MySQL中，当其他会话更新或删除了选定的行时，当前会话在执行事务之前将重新检查最新的数据。PostgreSQL当选定的行被其他会话更新或删除时，当前会话将忽略这些行。

试想一下，步骤6如果session1全部执行 rollback会怎样？通过测试在执行 rollback之后， Oracle、MySQL、PostgreSQL的执行结果是相同的，最终将删除一条记录。因为等待的事务将继续更改先前锁定的行，就像其他事务从未存在一样。

结尾

PostgrSQL的事务行为有点怪，未完待续...

References

[1]OracleとPostgreSQLのトランザクション差異について整理する:
https://zatoima.github.io/oracle-postgresql-transaction-different.html

[2]Data Concurrency and Consistency: 
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/data-concurrency-and-consistency.html#GUID-432C4EDC-F7F2-467C-B85F-4E82D3D58A2F

[3]Consistent Nonlocking Reads:
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

[4]Transaction Isolation: 
https://www.postgresql.org/docs/13/transaction-iso.html#XACT-READ-COMMITTED

oraclemysqlpostgresql
文章转载自励志成为PostgreSQL大神，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论