事务隔离级别（5）：Mysql 与Oracle事务隔离级别对比分析2

vlambda
2022-02-09

事务隔离级别（5）：Mysql 与Oracle事务隔离级别对比分析2

上一篇概括的比较了Oracle与Mysql的各个事务隔离级别，本篇将通过示例来加深理解。

1.Mysql各事务隔离级别

a)同时打开4个连接，分别设置隔离级别为读未提交（sess1_ru），读提交（sess2_rc），可重复读（sess3_rr）与串行化（sess4_serial），并开始事务（begin;）

 -- 连接1sess1_ru > select @@transaction_isolation; +-------------------------+| @@transaction_isolation |+-------------------------+| READ-UNCOMMITTED |+-------------------------+1 row in set (0.00 sec)sess1_ru > begin;Query OK, 0 rows affected (0.00 sec)
-- 连接2sess2_rc > select @@transaction_isolation; +-------------------------+| @@transaction_isolation |+-------------------------+| READ-COMMITTED |+-------------------------+1 row in set (0.00 sec)sess2_rc > begin;Query OK, 0 rows affected (0.00 sec)
-- 连接3sess3_rr > select @@transaction_isolation; +-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+1 row in set (0.00 sec)sess3_rr > begin;Query OK, 0 rows affected (0.00 sec)sess3_rr > 
-- 连接4sess4_serial > select @@transaction_isolation; +-------------------------+| @@transaction_isolation |+-------------------------+| SERIALIZABLE |+-------------------------+1 row in set (0.00 sec)sess4_serial > begin;Query OK, 0 rows affected (0.00 sec)

b)打开第五个连接，在缺省可重复读（RR）隔离级别下进行更新操作

dept表有两条记录，将dept=10的记录loc改为'SZ'，但暂不提交

 -- 连接5sess5_rr > select @@transaction_isolation; +-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ |+-------------------------+1 row in set (0.00 sec)
sess5_rr > begin;Query OK, 0 rows affected (0.00 sec)
sess5_rr > select * from dept;+--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SH |+--------+----------+------+2 rows in set (0.00 sec)
--更新sess5_rr > update dept set loc='SZ' where deptno=10; Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

c)观察前4个连接

连接1“读未提交”可以看到未提交的数据'SZ'；

连接2“读提交”及连接3“可重复读”还是看到之前提交的'SH';

连接4“串行”，在此隔离级别下，读数据前要加读锁，被连接5的更新语句所持有的锁阻塞，timeout后报错。

-- ru看到未提交数据sess1_ru > select * from dept;  +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (0.00 sec)
-- rc看不到未提交数据sess2_rc > select * from dept; +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SH |+--------+----------+------+2 rows in set (0.00 sec)
-- rr看不到未提交数据sess3_rr > select * from dept; +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SH |+--------+----------+------+2 rows in set (0.00 sec)
-- serilizable被连接5阻塞sess4_serial > select * from dept;  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionsess4_serial >

d)连接5提交后，前4个连接再进行查询

连接2“读提交”可以看到更改后的数据'SZ'了；

连接3“可重复读”的快照读看到的仍是事务开始后第一个read操作时的快照点'SH',而紧接着进行当前读'select * from dept for update'被连接4阻塞（阻塞的原因后续文章再做分析），而连接4回滚后，连接3的当前读返回deptno=10的loc为'SZ'，再做一次一致性读，返回结果为'SH'，验证了mysql在RR隔离级别，同一条记录快照读和当前读可能返回不同的结果。

--连接5提交sess5_rr > commit;    Query OK, 0 rows affected (0.00 sec)
sess1_ru > select * from dept;+--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (0.00 sec)
-- rc此时看到已提交的'SZ'sess2_rc > select * from dept;   +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (0.00 sec)
-- rr快照读仍然看到'SH',可重复读sess3_rr > select * from dept;  +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SH |+--------+----------+------+2 rows in set (0.01 sec)
-- rr当前读被连接4阻塞，session4回滚 sess3_rr > select * from dept for update;  -- 阻塞sess4_serial > rollback; -- session4回滚 Query OK, 0 rows affected (0.00 sec)
-- rr当前读看到'SZ'sess3_rr > select * from dept for update;  +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (33.10 sec)
--rr快照读与当前读不一致sess3_rr > select * from dept;   +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SH |+--------+----------+------+2 rows in set (0.00 sec)
-- 事务结束后看到连接5的修改sess3_rr > rollback;Query OK, 0 rows affected (0.00 sec) sess3_rr > select * from dept;   +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (0.00 sec)

2.Mysql RR隔离级别下快照读与Oracle只读事务快照点的区别

之前提到过，Mysql RR隔离级别下快照读的快照点是第一个read操作的时刻，而Oracle只读事务快照点是事务开始时刻。

下面的例子显示，Mysql连接3（可重复读）开始一个事务，而连接5（sess5_rr）将deptno=10的loc改为'GZ'并提交，之后连接3进行快照读，作为事务第一个read操作可以看到事务开始后提交的修改'GZ'；而连接5紧接着将deptno=10的loc改为'BJ'并提交，而连接3重复读到的结果仍然是'GZ'，连接3回滚结束事务后可看到'BJ'。

而Oracle只读事务开始后，另一连接将deptno=50的loc改为'LA'并提交，但只读事务第一个read看到的仍然是事务开始前的值'SF'。

-- Mysql 快照点为事务开始后第一个read-- 连接3开始新事务sess3_rr > begin;   Query OK, 0 rows affected (0.00 sec)
--连接5进行修改并提交sess5_rr > select * from dept; +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | SZ |+--------+----------+------+2 rows in set (0.00 sec)sess5_rr > update dept set loc='GZ' where deptno=10; Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0sess5_rr > commit; Query OK, 0 rows affected (0.00 sec)
-- 作为第一个read操作，rr可看到刚刚修改的 'GZ'sess3_rr > select * from dept;  +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | GZ |+--------+----------+------+2 rows in set (0.00 sec)------------ 连接5再次做修改并提交 sess5_rr > update dept set loc='BJ' where deptno=10;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0sess5_rr > commit;   Query OK, 0 rows affected (0.00 sec)sess5_rr > select * from dept;+--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | BJ |+--------+----------+------+2 rows in set (0.00 sec)
----- 连接3再进行读，重复读结果不变仍为'GZ'sess3_rr > select * from dept;   +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | GZ |+--------+----------+------+2 rows in set (0.00 sec)
-- 回滚结束事务后看到数据变化sess3_rr > rollback;Query OK, 0 rows affected (0.00 sec)sess3_rr > select * from dept;  +--------+----------+------+| deptno | deptname | loc |+--------+----------+------+| 5 | Finance | BJ || 10 | RD | BJ |+--------+----------+------+2 rows in set (0.00 sec)
------- Oracle-- 连接1开始只读事务sess1 > set transaction read only;  Transaction set. ------ 另一连接进行修改并提交SQL> select * from scott.dept;  DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 HR SFSQL> update scott.dept set loc='LA' where deptno=50;1 row updated.SQL> commit; ------ 即使是只读事务开始后的第一个read操作，仍看不到修改sess1 > select * from scott.dept; DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 HR SF
 -- 事务结束后看到更新的数据sess1 > rollback; Rollback complete.sess1 > select * from scott.dept; DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON50 HR LA

3. Oracle serializable 隔离级别的写冲突检测

Oracle的serializable进行的是快照读，连接1开启serializable隔离级别后，看不到另一事务进行的修改，但当写冲突（修改同一数据）时，报'can't serialize access for this transaction'错误。

-- 连接1 开启serializable隔离级别sess1 > set transaction isolation level serializable;
Transaction set.
sess1 > select * from dept;
 DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 HR LA
-- 另一连接修改数据并提交SQL> select * from dept;
 DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 HR LA
SQL> update dept set loc='SF' where deptno=50;  
1 row updated.
SQL> commit; 
Commit complete.
-- 连接1看不到修改，但当修改发生冲突时报错
sess1 > select * from dept;
 DEPTNO DNAME LOC---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 HR LA
sess1 > update dept set loc='NY' where deptno=50;update dept set loc='NY' where deptno=50 *ERROR at line 1:ORA-08177: can't serialize access for this transaction

由以上示例可以看出：

对于Mysql：

“读未提交”可以读到脏数据；
“读提交”可能发生“不可重复读”及“幻读”现象；
“可重复读”的快照读基于事务开始后第一个read操作时刻，而当前读可能返回与快照读不同的结果；
“串行化”的读操作都是当前读并加锁；

对于Oracle：

“只读事务”快照点是事务开始时刻；
“串行化”隔离级别基于快照读，并检测写冲突。

vlambda博客
学习文章列表