MySQL事务的隔离级别及锁操作的一点点演示

MySQL 版本：5.7

安装环境：MAC OS

一、测试数据

测试数据库：test；测试表：tt

CREATE TABLE `tt` (  `id` int(11) DEFAULT NULL,  `name` varchar(100) DEFAULT NULL,  KEY `name_idx` (`name`),  KEY `id_idx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 

插入测试数据：

insert into tt value(1, "a”);insert into tt value(1, "b”);insert into tt value(2, “b");

二、事务隔离级别设置

1、查询当前事务隔离级别

mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-UNCOMMITTED        |+-------------------------+

2、设置当前事务隔离级别

set global transaction_isolation = 'read-uncommitted';

设置隔离级别后，后续开启的连接 Session 才会生效。

三、读未提交（READ-UNCOMMITTED）

开启两个连接 Session：

Session 1	Session 2
开启事务，更新 id 为 2 的记录 name 为 “ss" ，保持事务未提交： Query OK, 0 rows affected (0.00 sec)mysql> select * from tt;+------+------+| id | name |+------+------+| 1 | a || 1 | b || 2 | b |+------+------+3 rows in set (0.00 sec)mysql> update tt set name = 'ss' where id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
	开启事务，查询 id 为 2 的记录 name 值： mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | ss |+------+———+  事务 2 可以查询到事务 1 未提交的数据变更。对于事务 2 来说，这条数据是脏数据。

四、读已提交（READ-COMMITTED）

解决 READ-UNCOMMITTED 隔离级别下产生的脏读现象。

设置事务隔离级别：

mysql> set global transaction_isolation = 'read-committed';Query OK, 0 rows affected (0.00 sec)

重新开启测试 Session，查询事务隔离级别：

mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-COMMITTED          |+-------------------------+

 

Session 1	Session 2
开启事务，更新 id 为 2 的记录 name 为 “ssr”： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> update tt set name = 'ssr' where id = 2;Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | ssr |+------+------+1 row in set (0.01 sec)
	查询数据，无法查询到 事务 1 未提交的数据： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | b |+------+------+1 row in set (0.00 sec)
提交事务： mysql> commit ;Query OK, 0 rows affected (0.01 sec)
	查询数据，得到的是事务 1 中已提交的数据变更： mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | ssr |+------+------+1 row in set (0.00 sec)   对于事务 2 来说，在事务 1 提交前后，获取到的数据是不一样的，即不可重复读问题。

五、可重复读（REPEATABLE-READ）

解决 READ-COMMITTED 隔离级别下产生的不可重复读现象。

Session 1中 设置事务隔离级别： 

mysql> set global transaction_isolation = 'repeatable-read';Query OK, 0 rows affected (0.01 sec)

重新开启事务，查询隔离级别：

mysql> select @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ         |+-------------------------+1 row in set (0.00 sec)

 

Session 1	Session 2
	Session 2 开启事务，查询数据： mysql> begin;Query OK, 0 rows affected (0.00 sec)Database changedmysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | b |+------+------+1 row in set (0.00 sec)
更新 id 为 2 的记录 name 为 “ssrr”， 并提交事务： mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> update tt set name = 'ssrr' where id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.01 sec)mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | ssrr |+------+------+1 row in set (0.00 sec)
	Session 2 重新查询数据： mysql> select * from tt where id = 2;+------+------+| id | name |+------+------+| 2 | b |+------+------+ 当前数据未变。 但是问题是，事务 1 已经进行了数据变更，并且提交，事务 2 无法获取所查记录最新变更信息。

为什么事务 2 前后两次相同查询所得的数据是一样的？

一致性读（consistent read）查询模式：基于【某一时刻】的【数据快照】提供读查询结果。无论查询的数据是否被其它事务所改变。这个【某一时刻】在 repeatable-read 隔离级别下为事务中第一次执行查询操作的时间点，read-committed 隔离级别下，数据快照会在每一次执行一致性读操作时进行重置。

幻读

如何避免：加X锁

Next-key lock：Record lock + Gap lock

六、关于 Next-key lock 加锁

调整表 tt 索引及数据：

mysql> show create table tt;+-------+-------------------------------------------------------+| Table | Create Table                                                                                                                                                                                      |+-------+-------------------------------------------------------+| tt    | CREATE TABLE `tt` (  `id` int(11) NOT NULL,  `name` varchar(100) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `idx_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-------------------------------------------------------+1 row in set (0.00 sec)mysql> select * from tt;+-----+------+------+| id  | name | age  |+-----+------+------+|  90 | aa   |   10 || 102 | bb   |   15 || 108 | cc   |   20 || 130 | dd   |   25 || 150 | ee   |   30 |+-----+------+------+

1、等值条件

对于使用唯一性索引：加的锁为 Record lock

Session 1	Session 2
开启事务，查询 id 为 108 记录加 X lock： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where id = 108 for update;+-----+------+------+| id | name | age |+-----+------+------+| 108 | cc | 20 |+-----+------+------+1 row in set (0.01 sec)
	开启事务，记录前后紧邻 gap 插入记录： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(106, 'bc', 16);Query OK, 1 row affected (0.00 sec) mysql> insert into tt value(110, 'cd', 22);Query OK, 1 row affected (0.00 sec)记录均可成功插入

对于使用非唯一性索引：加的锁为 Record lock + Gap lock 前后紧邻 gap

：首先加锁 (15, 20]，因为是非唯一索引，继续向后查找到第一个不满足条件的元素 25 加 gap lock (20, 25)

Session 1	Session 2
开启事务，查询 age 为 20 记录加 X lock： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where age = 20 for update;+-----+------+------+| id | name | age |+-----+------+------+| 108 | cc | 20 |+-----+------+------+1 row in set (0.00 sec)
	开启事务，记录紧邻前后 gap 插入记录： mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into tt value(106, 'bc', 18); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(110, 'cd', 22); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 操作均被 block。 紧邻 gap 以外插入记录： mysql> insert into tt value(100, 'ab', 12);Query OK, 1 row affected (0.00 sec) mysql> insert into tt value(140, 'de', 27);Query OK, 1 row affected (0.00 sec) 记录均可成功插入

对于不使用索引的：加锁为全部记录及gap 

Session1	Session2
开启事务，查询 name 为 ‘cc’ 记录加 X lock： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where name = 'cc' for update;+-----+------+------+| id | name | age |+-----+------+------+| 108 | cc | 20 |+-----+------+------+
	开启事务，各个间隙尝试插入记录： mysql> begin;Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(80, 'pa', 5);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tt value(95, 'ab', 13);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tt value(105, 'bc', 18);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tt value(120, 'cd', 23);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tt value(140, 'de', 28);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> insert into tt value(160, 'en', 35);ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 操作均被 block。 更新记录： mysql> update tt set age = 21 where name = 'cc';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> update tt set age = 16 where name = 'bb';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 操作均被 block。