通过百度搜索:“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都是一样的,不一样的是设置事务级别的语句。
#事务1
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START TRANSACTION;
#更新操作:脏读、不可重复读
UPDATE test SET name = 'sai' WHERE id = 1;
#插入操作:幻读
INSERT test SELECT 4,'hacker','男';
COMMIT;
#事务2
SELECT @@tx_isolation \G
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@tx_isolation \G
START 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-READ
1 行于数据集 (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为分布式事务准备的。实际使用很少这里不实验。