实验

来源：互联网发布：iphone移动数据打不开编辑：程序博客网时间：2024/04/29 11:05

通过百度搜索：“MySql 事务隔离级别”，“InnoDB 事务隔离级别” 发现很多的文章“特点”如下：

重点在于解释：不可重复读和幻读的区别；
大部分结论是：在repeatable read的隔离级别下，解决了不可重复读的问题，但是存在幻读问题。

正确的分析可以参考：Innodb中的事务隔离级别和锁的关系

关于脏读、幻读、不可重复、丢失更新，可做如下实验。强调仅限MySql环境，各类数据库的结论推测到其他数据库上是不太合适的。

实验证明目的是证明：MySql InnoDB的Repeatable Read级别是解决了不可重复读和幻读的问题，并直观感受脏读、幻读、不可重复问题

准备表和数据

CREATE DATABASE Test;USE Test;CREATE TABLE test(    id INT AUTO_INCREMENT,    name VARCHAR(100),    sex CHAR(1),    PRIMARY KEY (id),    KEY (name,sex))ENGINE = INNODB;INSERT test SELECT 1,'saillen','男';INSERT test SELECT 2,'wenwen','女';INSERT test SELECT 3,'jocker','男';

实验SQL

在各个级别下的实验SQL都是一样的，不一样的是设置事务级别的语句。

#事务1SELECT @@tx_isolation \GSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT @@tx_isolation \GSTART TRANSACTION;#更新操作：脏读、不可重复读UPDATE test SET name = 'sai' WHERE id = 1;#插入操作：幻读INSERT test SELECT 4,'hacker','男';COMMIT;#事务2SELECT @@tx_isolation \GSET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;SELECT @@tx_isolation \GSTART TRANSACTION;#在事务1开始前执行一遍SELECT * FROM test;#在事务1update和insert后执行一遍SELECT * FROM test;#在事务1 commit后执行一遍，出现不可重复和幻读SELECT * FROM test;

Read Uncommitted级别实验

脏读发生在Read UnCommitted级别下，表示读取到了其他并发的未提交事务的结果

实验结果

事务1的结果：

mysql> SELECT @@tx_isolation \G*************************** 1. 行 ***************************@@tx_isolation: REPEATABLE-READ1 行于数据集 (0.01 秒)mysql> set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.02 秒)mysql> select @@tx_isolation ;+------------------+| @@tx_isolation   |+------------------+| READ-UNCOMMITTED |+------------------+1 行于数据集 (0.03 秒)mysql> start transaction;Query OK, 0 rows affected (0.02 秒)mysql> update test set name = 'sai' where id = 1;Query OK, 1 rows affected (0.03 秒)mysql> select * from test where id = 1;+----+------+------+| id | name | sex  |+----+------+------+| 1  | sai  | 男  |+----+------+------+1 行于数据集 (0.02 秒)mysql> rollback;Query OK, 0 rows affected (0.02 秒)mysql> select * from test where id = 1;+----+---------+------+| id | name    | sex  |+----+---------+------+| 1  | saillen | 男  |+----+---------+------+1 行于数据集 (0.03 秒)

事务2的结果：

mysql> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1 行于数据集 (0.03 秒)mysql> set session transaction isolation level read uncommitted;Query OK, 0 rows affected (0.01 秒)mysql> select @@tx_isolation;+------------------+| @@tx_isolation   |+------------------+| READ-UNCOMMITTED |+------------------+1 行于数据集 (0.02 秒)mysql> start transaction;Query OK, 0 rows affected (0.02 秒)mysql> select * from test where id = 1;+----+------+------+| id | name | sex  |+----+------+------+| 1  | sai  | 男  |+----+------+------+1 行于数据集 (0.02 秒)mysql> select * from test where id = 1;+----+---------+------+| id | name    | sex  |+----+---------+------+| 1  | saillen | 男  |+----+---------+------+1 行于数据集 (0.04 秒)mysql> commit;Query OK, 0 rows affected (0.01 秒)

结论

MySql在READ UNCOMMITTED事务隔离级别下会有脏读现象，因为不加任何锁，极端情况下才使用的级别；

Read Committed事务级别

不可重复读是指事务1在事务过程中，事务2修改了事务1读取的行，导致事务1两次读取到的结果不一致，重点区别是事务2 commit后才能感知到这次修改。这种情况某些时候是允许并且欢迎的，比如我们对账户做修改的时候，应该实时感知到余额的变化，但是某些时候是不应该被感知的，比如做某个时间段的销量统计的，库存减少在事务中被感知，一是程序容易计算错误，二是我们计算的是某个时候的，下一时刻的变化不在考虑范围内。

实验结果

事务1的实验结果

mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.02 秒)mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+1 行于数据集 (0.03 秒)mysql> start transaction;Query OK, 0 rows affected (0.02 秒)mysql> update test set name = 'sai' where id = 1;Query OK, 1 rows affected (0.02 秒)mysql> insert test select 4,'hacker','男';Query OK, 1 rows affected (0.01 秒)mysql> commit;Query OK, 0 rows affected (0.01 秒)

事务2实验结果

mysql> set session transaction isolation level read committed;Query OK, 0 rows affected (0.02 秒)mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| READ-COMMITTED |+----------------+1 行于数据集 (0.03 秒)mysql> start transaction;Query OK, 0 rows affected (0.01 秒)mysql> select * from test;+----+---------+------+| id | name    | sex  |+----+---------+------+| 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+3 行于数据集 (0.02 秒)mysql> select * from test;+----+---------+------+| id | name    | sex  |+----+---------+------+| 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+3 行于数据集 (0.03 秒)mysql> select * from test;+----+---------+------+| id | name    | sex  |+----+---------+------+| 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+3 行于数据集 (0.02 秒)mysql> select * from test;+----+--------+------+| id | name   | sex  |+----+--------+------+| 4  | hacker | 男  || 3  | jocker | 男  || 1  | sai    | 男  || 2  | wenwen | 女  |+----+--------+------+4 行于数据集 (0.01 秒)mysql> commit;Query OK, 0 rows affected (0.01 秒)

结论

MySql在Read Committed事务隔离级别下是解决了脏读问题，但是存在不可重复读和幻读问题。在Read Committed级别下，MySql InnoDB引擎采用一致性非锁定读方案去读数据，读不加S锁，但是写加了X锁，所以读发生了问题，如果使用SELECT * FROM test IN SHARE MODE 主动加S锁可以避免不可重复读；

如果主动加S或者X锁，会发下事务1被阻塞在update的地方，因为update要加X锁，row已经被加S锁，所以要阻塞，事务并发性降低。

如果使用SELECT * FROM test FOR UPDATE 主动加X锁也可以避免，但是无法避免幻读。

Repeatable Read级别

Repeatable read 是MySql的默认事务级别，也是网上部分结论出问题的事务级别，该级别在ISO和一些数据库的实现上是用来解决不可重复读问题的，但是解决不了幻读。但是实际上，MySql的可重复读级别是解决了‘幻读’和‘不可重复读’两个问题的！

实验结果

事务1结果

mysql> set session transaction isolation level repeatable read;Query OK, 0 rows affected (0.01 秒)mysql> update test set name = 'sai' where id = 1;Query OK, 1 rows affected (0.02 秒)mysql> insert test select 5,'h','女';Query OK, 1 rows affected (0.02 秒)mysql> commit;Query OK, 0 rows affected (0.01 秒)mysql>

事务2结果

mysql> select @@tx_isolation;+-----------------+| @@tx_isolation  |+-----------------+| REPEATABLE-READ |+-----------------+1 行于数据集 (0.02 秒)mysql> start transaction;Query OK, 0 rows affected (0.01 秒)mysql> select * from test ;+----+---------+------+| id | name    | sex  |+----+---------+------+| 4  | hacker  | 男  || 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+4 行于数据集 (0.02 秒)mysql> select * from test ;+----+---------+------+| id | name    | sex  |+----+---------+------+| 4  | hacker  | 男  || 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+4 行于数据集 (0.05 秒)mysql> select * from test ;+----+---------+------+| id | name    | sex  |+----+---------+------+| 4  | hacker  | 男  || 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+4 行于数据集 (0.03 秒)mysql> mysql> select * from test ;+----+---------+------+| id | name    | sex  |+----+---------+------+| 4  | hacker  | 男  || 3  | jocker  | 男  || 1  | saillen | 男  || 2  | wenwen  | 女  |+----+---------+------+4 行于数据集 (0.02 秒)mysql> commit;Query OK, 0 rows affected (0.01 秒)mysql> select * from test ;+----+--------+------+| id | name   | sex  |+----+--------+------+| 5  | h      | 女  || 4  | hacker | 男  || 3  | jocker | 男  || 1  | sai    | 男  || 2  | wenwen | 女  |+----+--------+------+5 行于数据集 (0.03 秒)

结论

MySql的可重复事务级别解决了不可重复和幻读问题，这个和大部分的数据库以及ISO标准是不一样的。解决方案是采用Next-Key Lock实现，通过锁定范围来实现。

以上述例子分析，select * from test; 这里没有加where条件，肯定走全表扫描，所以加锁的范围就是负无穷到正无穷，如果是加where条件为id > 10 and id < 20，那么加锁范围就是[10,20]会锁定id为10，11，……，20的行，这些行可能不存在，但是在锁定范围内是的数据是不会被update和insert的。MySql下解决幻读的方案存在一定小问题，但是并不严重。

串行化

在一般的扁平事务中，MySql的默认事务隔离级别完全够用了，不需要串行化，串行化是MySql为分布式事务准备的。实际使用很少这里不实验。

阅读全文

0 0