目录
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等)操作,只有当事务一提交后,事务二才可进行,避免了脏读和幻读。
对这四种事务隔离级别的描述网上有一文章比喻得很形象,请参照:借用网上的一张图来说明这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的事务隔离级别,事务一在对一个表中的一行进行修改时,事务二不能对相同的表中进行修改操作,查询是可以的,但查询到的数据也是事务一开始前的原始数据,在事务一中已被修改而没有被提交的数据在事务二中是不可见的,只要事务一对表正在进行修改操作,那就会加上锁,这种锁也是表级锁,只有当事务一提交事务后锁才解除,事务二才能修改此表中的数据。