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> set global transaction_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
2、锁等待时间设置
mysql> set global innodb_lock_wait_timeout=5;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
| 5 |
+----------------------------+
3、附注
更改设置后，后续开启的连接 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 changed mysql> 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: 0 mysql> 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 transaction mysql> insert into tt value(95, 'ab', 13); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(105, 'bc', 18); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(120, 'cd', 23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(140, 'de', 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> 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 transaction mysql> update tt set age = 16 where name = 'bb'; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```操作均被 block。 |
### 2、范围条件
使用唯一索引：
| | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Session 1 | Session 2 |
| 开启事务，查询 id 为 108 的记录用以更新```
mysql> select * from tt where id >= 108 and id < 109 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | +-----+------+------+ 1 row in set (0.01 sec)
``` | |
| | 开启事务，间隙插入记录：```
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(120, 'cd', 23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tt set age = 26 where id = 130; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```操作被 block。加锁说明：\>= 108 条件：主键索引加锁为 Record lock 记录 108< 109 条件：因为不存在 109 记录，所以继续向右遍历至 130 不满足，加锁 (108, 130] |
| 重新开启事务，右侧条件改为开区间 130：```
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where id >= 108 and id < 130 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | +-----+------+------+ 1 row in set (0.01 sec)
``` | |
| | 开启事务，操作 130 后间隙插入记录及更新 id 为 130 记录：```
mysql> insert into tt value(135, 'ce', 32); Query OK, 1 row affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update tt set age = 26 where id = 130; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```插入操作成功，更新操作被 block。加锁说明：< 130 条件：存在 130 记录，加锁 (108, 130] |
| 重新开启事务，右侧条件改为闭区间 130```
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where id >= 108 and id <= 130 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | | 130 | dd | 25 | +-----+------+------+
``` | |
| | 开启事务，操作 130 后间隙插入记录及更新 id 为 150 记录：```
mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(140, 'de', 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tt set age = 35 where id = 150; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```插入操作成功，更新操作被 block。加锁说明：<= 130 条件：存在 130 记录，加锁 (108, 130]，继续向右查询到不满足条件记录 150，加锁 (130, 150] |
### 2、使用非唯一索引
| | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Session 1 | Session 2 |
| 开启事务，查询 age 范围记录用以更新：```
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where age >= 20 and age < 21 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | +-----+------+------+
``` | |
| | 开启事务，间隙插入记录：```
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(105, 'bc', 18); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(120, 'cd', 23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(140, 'de', 28); Query OK, 1 row affected (0.00 sec) mysql> update tt set name = 'test' where age = 25; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```加锁说明：\>= 20 条件：非唯一索引，加锁为 (15, 20]< 21 条件：因为不存在 21 记录，所以继续向右遍历至 25 不满足，加锁 (20, 25] |
| 重新开启事务，右侧条件改为开区间 25```
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where age >= 20 and age < 25 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | +-----+------+------+
``` | |
| | 开启事务，操作 25 后间隙插入记录及更新 id 为 25 记录：```
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(140, 'de', 28); Query OK, 1 row affected (0.00 sec) mysql> update tt set name = 'test' where age = 25; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```插入操作成功，更新操作被 block。加锁说明：< 25 条件：存在 25 记录，加锁 (20, 25] |
| 重新开启事务，右侧条件改为闭区间 25```
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt where age >= 20 and age <= 25 for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | | 130 | dd | 25 | +-----+------+------+
``` | |
| | 开启事务，操作 25 后间隙插入记录及更新 age 为 30 记录：```
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(140, 'de', 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tt set name = 'test' where age = 30; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```插入操作成功，更新操作被 block。加锁说明：<= 25 条件：存在 25 记录，加锁 (20, 25]，继续向右查询到不满足条件记录 30，加锁 (25, 30] |
### 3、 不使用索引
| | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Session 1 | Session 2 |
| 开启事务，查询 name 范围记录用以更新：```
mysql> select * from tt where name >= 'cc' and name < 'dd' for update; +-----+------+------+ | id | name | age | +-----+------+------+ | 108 | cc | 20 | +-----+------+------+
``` | |
| | 开启事务，间隙插入记录：```
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tt value(85, 'pa', 5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(95, 'ab', 13); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(105, 'bc', 18); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(120, 'cd', 23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(140, 'de', 28); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tt value(160, 'et', 50); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```加锁说明：间隙全部加锁 |
## 七、附加订阅
