目录
1、隔离级别的种类介绍
2、验证隔离级别
2.1、两个变量及测试环境介绍
2.2、read-uncommitted验证
2.3、read-committed验证
2.4、repeatable read验证
2.5、serializable验证
1、隔离级别的种类介绍
数据库事务的隔离级别(级别依次增强,并发性依次降低):
a)、READ-UNCOMMITTED
读未提交:事务一所做的修改即使没有提交(COMMIT),正在执行的事务二也能看到(看到的数据就是脏数据,即造成了脏读),此级别会造成幻读。
b)、READ-COMMITTED
读提交:事务一所做的修改在没有提交前,事务二执行中的事务不能看到,需要在事务一做提交后,事务二才能看到,避免了脏读,但会产生幻读。sql server和oracle数据默认采用此事务隔离级别
c)、REPEATABLE-READ
重复读:事务一所做的任务修改,不会影响事务二,即使是事务一已提交,事务二也看不到事务一所做的修改,只有当事务二提交后,才会发现事务一所做的修改,避免了脏读,但会发生幻读,因为在事务二提交前与提交后数据因事务一而发生了改变。mysql默认采用了此事务隔离级别
d)、SERIALIZABLE
序列化:事务一正在修改一表中的数据时,事务二就不可以对同一表中的任何数据进行修改(INSERT、DELETE等)操作,只有当事务一提交后,事务二才可进行,避免了脏读和幻读。
对这四种事务隔离级别的描述网上有一文章比喻得很形象,请参照:http://blog.csdn.net/fg2006/article/details/6937413
借用网上的一张图来说明这4种事务隔离级别逐步解决的问题:
2、验证隔离级别
2.1、两个变量及测试环境介绍
在验证各个隔离级别前来介绍两个变量,一个是与事务自动提交的变量“autocommit”,另一个是用来设定事务隔离级别的“tx_isolation”变量。
mysql> SELECT VERSION(); #查看做测试的mysql版本是5.5.36 +------------+ | VERSION() | +------------+ | 5.5.36-log | +------------+ mysql> SELECT USER(); #查看当前用户 +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ mysql> SHOW VARIABLES LIKE 'autocommit'; #设置事务自动提交的变量,关闭后可提高性能,在使用Innodb存储引擎的场景应该关闭 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ mysql> SET GLOBAL autocommit=0; #关闭事务自动提交功能 mysql> SHOW SESSION VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'tx%'; #调整事务隔离级别变量 +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+
测试时所用到的数据库及表介绍:
mysql> SHOW TABLE STATUS FROM mydb1 LIKE 'students_tb'\G *************************** 1. row *************************** Name: students_tb Engine: InnoDB #表的存储引擎是InnoDB ...略... mysql> DESC students_tb; +-----------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------+----------------+ | StudentID | smallint(5) unsigned | NO | PRI | NULL | auto_increment | | Name | char(30) | NO | | NULL | | | Age | tinyint(3) unsigned | YES | | NULL | | | Gender | enum('M','F') | YES | | NULL | | | ClassID | tinyint(3) unsigned | NO | | NULL | | +-----------+----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 10 | Cora | 25 | F | 6 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+
2.2、read-uncommitted验证
先打开一个mysql会话,在交互式界面中设置好隔离级别,如下:
mysql> SET GLOBAL tx_isolation = 'read-uncommitted'; #先设置隔离级别为读未提交
再打开个一个mysql会话,这样mysql就有两个会话:
mysql> SHOW PROCESSLIST; +----+------+-----------+-------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+-------+------------------+ | 1 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 3 | root | localhost | mydb1 | Sleep | 253 | | NULL | +----+------+-----------+-------+---------+------+-------+------------------+ 2 rows in set (0.01 sec)
在会话一中启动事务:
mysql> START TRANSACTION; mysql> DELETE FROM students_tb WHERE Name='Cora'; #删除一条数据
在会话二中查看students_tb表中的数据:
mysql> SELECT * FROM students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #发现在会话一中删除的数据没有了,但会话一还没有提交事务,这里读到的数据就是脏数据,产生了脏读。
回到会话一中撤销事务:
mysql> ROLLBACK; Query OK, 0 rows affected (0.01 sec)
再回到会话二中查看students_tb表中的数据:
mysql> SELECT * FROM students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 10 | Cora | 25 | F | 6 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #之前被删除“Cora”的数据又回来了
小结:
可见在read-uncommitted这种隔离级别下,随着在会话一中事务对表的操作,导致对在会话二中读取表数据结果产生了影响,这样就发生了脏读和幻读现象。
2.3、read-committed验证
在会话一中设置read-committed事务隔离级别:
mysql> SET GLOBAL tx_isolation = 'read-committed'; mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+
会话二要先关闭,再打开,不然对重新设置的变量不生效:
mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; #在会话二中确认事务隔离级别 +---------------+----------------+ | Variable_name | Value | +---------------+----------------+ | tx_isolation | READ-COMMITTED | +---------------+----------------+
回到会话一中,执行事务:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM mydb1.students_tb WHERE Name='Cora'; #同样删除一条数据 Query OK, 1 row affected (0.00 sec)
在会话二中查询students_tb表中的数据:
mysql> START TRANSACTION; #也启动一个事务 mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 10 | Cora | 25 | F | 6 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Cora这个学生的信息还在,会话一中的操作没有对会话二产生影响,即避免了发生脏读。
回到会话一中把事务提交:
mysql> COMMIT;
再到会话二中查询数据:
mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Croa的那行数据不见了,所以当会话一中的事务提交后,会影响会话二中的事务对表数据的读取,这样就产生了幻读。
小结:
read-committed隔离级别解决了脏读,但幻读问题依然存在。
2.4、repeatable-read验证
在会话一中设置repeatable-read事务隔离级别:
mysql> SET GLOBAL tx_isolation='repeatable-read';
同样先关闭会话二,再开启一个mysql会话
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+
回到会话一中,开启一个事务,同时在会话二中也开启一个事务:
mysql> START TRANSACTION; #会话一中开启事务 mysql> START TRANSACTION; #会话二中开启事务
在会话一中删除一数据:
mysql> DELETE FROM mydb1.students_tb WHERE Name='Tom'; mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Tom的数据被删除
在会话二的事务中查看students_tb表中的数据:
mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Tom这一行数据还在,会话一中的删除操作对会话二中的事务没有影响,即没有产生脏读
再回到会话一中提交事务:
mysql> COMMIT;
回到会话二的事务中再次查询数据:
mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 1 | Tom | 17 | M | 1 | | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Tom这一行依然还在,再一次验证脏读是不会发生的
再把会话二中的事务提交后再查询数据:
mysql> COMMIT; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #Tom这一行的数据没有了,在会话二中事务开始和事务提交后读取students_tb获取的数据发生了改变,产生了幻读
小结:
工作在repeatable-read(可重复读)的隔离级别的事务能避免脏读,但还是不能避免幻读的产生。
2.5、serializable验证
在会话一中修改tx_isolation变量的值为serializable:
mysql> SET GLOBAL tx_isolation='serializable'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | tx_isolation | SERIALIZABLE | +---------------+--------------+
同样先关闭会话二,再打开一个会话:
mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; +---------------+--------------+ | Variable_name | Value | +---------------+--------------+ | tx_isolation | SERIALIZABLE | +---------------+--------------+
在会话一和会话二依次开启一个事务:
mysql> START TRANSACTION; #会话一中开启事务 mysql> START TRANSACTION; #会话二中开启事务
回到会话一中,查询students_tb表的数据:
mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 4 | Jimima | 15 | F | 4 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 58 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+
再回到会话二,修改表中的数据:
mysql> UPDATE mydb1.students_tb SET Age=55 WHERE Name='Echo'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #数据修改成功了
回到会话一中,修改表中的数据:
mysql> mysql> DELETE FROM mydb1.students_tb WHERE Name='Jimima'; #执行此语句后会卡一会儿,然后报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
回到会话二,提交事务:
mysql> COMMIT;
再回到会话一执行上边没有执行成功的语句:
mysql> DELETE FROM mydb1.students_tb WHERE Name='Jimima'; Query OK, 1 row affected (0.00 sec) #执行成功 mysql> COMMIT; Query OK, 0 rows affected (0.04 sec) mysql> SELECT * FROM mydb1.students_tb; +-----------+--------+------+--------+---------+ | StudentID | Name | Age | Gender | ClassID | +-----------+--------+------+--------+---------+ | 2 | Jack | 18 | M | 3 | | 3 | Lucy | 21 | F | 6 | | 5 | Jimmy | 30 | M | 9 | | 6 | Jim | 26 | M | 7 | | 11 | Echo | 55 | F | 7 | | 12 | 张三 | 47 | M | 3 | +-----------+--------+------+--------+---------+ #事务提交后表中的数据也有了相应的修改
小结:
工作在serializable的事务隔离级别,事务一在对一个表中的一行进行修改时,事务二不能对相同的表中进行修改操作,查询是可以的,但查询到的数据也是事务一开始前的原始数据,在事务一中已被修改而没有被提交的数据在事务二中是不可见的,只要事务一对表正在进行修改操作,那就会加上锁,这种锁也是表级锁,只有当事务一提交事务后锁才解除,事务二才能修改此表中的数据。转载于:https://blog.51cto.com/zhaochj/1630233