• 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> 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

    加锁说明：

    间隙全部加锁
  • 相关阅读:
    Java8新特性-日期时间
    解决有道云笔记导入md文件无法加载文件内的图片方式
    Mac安装Navicat Premium 12 永久破解
    MacBook Pro安装和配置Tomcat
    MySQL不支持DELETE使用表别名?
    JAVA设计模式之模板方法
    Lombok中的@Builder注解
    JAVA设计模式之策略模式
    Stream中的Peek操作
    MySql插入一条数据不提交事务主键仍自增的理解
  • 原文地址：https://www.cnblogs.com/niejunlei/p/15874597.html
Copyright © 2020-2023  润新知