死锁 MySQL

死锁 - 了解事务与隔离级别

死锁 - 了解事务与隔离级别

Posted by WangZiTao on Wednesday, July 25, 2018

这个月线上环境突然经常报警，排查后发现了下面这样的异常，一天出现三四次的样子。看异常信息就知道是发生了数据库死锁，由于这块对业务影响不是很大，自己以前也没接触过,决定花点时间,从网上找查找资料,将 MySQL 数据库的一些基础知识，包括事务、隔离级别、索引和锁等重新看一遍，也顺利解决了线上的死锁问题。

异常信息如下：

### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

根据日志,知道是发生了死锁,由于死锁发生的场景比较多,再加上自己刚接手这个模块,准备一边了解业务场景,一边学习死锁知识.

  1. 了解事务与隔离级别
  2. 了解常见锁类型
  3. 掌握常见 SQL 语句的加锁分析
  4. 死锁问题的分析和解决

说起死锁，则不得不说事务，当两个或两个以上的事务相互持有和请求锁的时候，如果形成一个循环的依赖关系，就会产生死锁。所以我们先了解事务.

事务四要素：ACID

对于事务，我之前的理解是很粗糙的，不就是为了保证操作的原子性么？一般订单系统或者财务系统跟钱打交道的都会用到对吧？事务可以保证所有的操作一次性提交，要么全部成功，要么全部失败，譬如 A 转账给 B 100 元，先从 A 的账户里扣除 100 元，再在 B 的账户上加上 100 元，这两个操作必须同时成功，如果 A 的钱减了之后程序异常了，事务会将 A 的钱回滚回去，就是这么简单啊。

确实如此，原子性可以说是事务四要素中最直白的一个，也是最容易理解的一个。但是我们往往忽略了事务的另外三个要素：一致性、隔离性和持久性。这四个要素合起来就是 ACID 。

  • 原子性（Atomicity）：要么全部完成，要么全部不完成；
  • 一致性（Consistency）：一个事务单元需要提交之后才会被其他事务可见；
  • 隔离性（Isolation）：并发事务之间不会互相影响，设立了不同程度的隔离级别，通过适度的破坏一致性，得以提高性能；
  • 持久性（Durability）：事务提交后即持久化到磁盘不会丢失。

对于死锁，我们还是应该把注意力放在 隔离性（Isolation） 上面，因为不同的隔离级别，可能对应的加锁过程也不一样，而正是由于引入了各种各样的隔离级别，才让锁问题变得格外复杂。解决和分析死锁问题，首先得知道当前数据库的隔离级别。那么隔离级别是个什么东西呢？其实从上面也看的出来，隔离性和一致性是有冲突的，有时候为了提高性能，会适度的破坏一致性，可能会出现一个事务还没提交，它的修改就被其他事务看见了，这其实就是传说中的 脏读，它属于最低级的隔离级别 读未提交

事务并发存在的问题

在细讲事务的隔离级别之前，让我们先来看一下下面这张表，以及几个对这个表的操作，瞧瞧在多事务并发处理数据的时候可能会发生哪些意想不到的情况。

表很简单，就三个字段，主键 id、姓名 name 和余额 balance，其中 name 为二级索引，如下： ![]/img/post/18/06/1.jpg)

脏读（dirty read）

假设有两个事务，一个在进行转账操作，将 A 账户的 100 元转到 B 账户，同时有另一个事务在对 A 和 B 的账户余额进行求和统计，如下图所示：

我们不考虑任何加锁机制，仅仅从程序运行的角度来看，事务 1 执行成功之后，A 成功转了 100 元到 B 账户，这时 A 余额还剩 900 元，B 余额剩 1100，总和为 2000；但是事务 2 的统计求和算出来的结果却是 A + B = 1900。这个就是上面说的，没有提交的事务被其他事务读取到了，这叫做 脏读 。 ![]/img/post/18/06/2.png)

解决办法： 把数据库的事务隔离级别调整到 读未提交 以上，可以解决脏读。

不可重复读（unrepeatable read）

第二种场景叫 不可重复读，如下图所示，事务 2 第一次获取 A 的账户余额为 1000 元，这个时候事务 1 对 A 的账户余额进行操作减去了 100 元，事务 2 再去查询 A 的账户余额发现变成了 900 元，这样就出现了同一个事务对同一条记录读取两遍，两次读出来的结果竟然不一样。

不可重复读和脏读的区别在于，脏读是读取了另一个事务未提交的修改，而不可重复读是读取了另一个事务提交之后的修改，本质上都是其他事务的修改影响了本事务的读取。那么不可重复读有什么问题呢？假设上面的事务 2 存在着两个子例程，第一个例程是读取所有账户的余额计算总和，可以得到所有人的余额总和为 3000，第二个例程是再次读取所有账户的余额计算平均值，3 个人总和 3000 按理应该是平均每人 1000 才对，却计算出了 2900/3 = 966，这就导致了数据不一致。

![]/img/post/18/06/3.png)

解决办法： 通过在读的时候加锁，可以解决不可重复读。

幻读（phantom read）

幻读就是在一个事务中一次查询之后,有另一个事务进行了插入或删除操作,插入或删除的内容是满足上述查询条件的,如果这个事务中海油同样查询条件的二次查询,会导致第二次查询的结果与第一次不一致.

比如:管理员A查询id大于等于2的数据发现有2,3两条，但是另外一个线程就在这个时候插入（注意是插入或者删除，不是修改））了一条新的id=4的记录，当管理员A再次查询时,发现多了一条记录，就好像发生了幻觉一样。这就叫幻读。 解决办法：

![]/img/post/18/06/4.png)

解决办法： 通过串行化，可以解决幻读。

丢失更新（lost update）

上面说的三种情况，都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了不该读的数据；那么如果两个事务都是写，又会发生什么呢？

假设两个事务同时对 A 的余额进行修改，他们都查出 A 的当前余额为 1000，然后事务 2 修改 A 的余额，将 A 的余额加 100 变成 1100 并提交，这个时候 A 的余额应该是 1100，但是这个时候事务 1 并不知道 A 的余额已经变动，而是继续在 1000 的基础上进行减 100 的操作并提交事务，就这样事务 2 的提交被覆盖掉了，事务 1 提交之后 A 的余额变成了 900 元。这就是说事务 1 的提交覆盖了事务 2 的提交，事务 2 的 UPDATE 操作完全丢失了，整个过程如下图所示：

![]/img/post/18/06/5.png)

这就是经典的 丢失更新 问题，由于最后一步是提交操作，所以又叫做 提交覆盖，有时候又叫 Read-Modify-Write 问题。一个典型的场景是并发对某个变量进行自增或自减，譬如商品表的库存字段，每次下单之后库存值需要减 1，大概的流程如下：

  1. SELECT name, stock FROM product WHERE id = 100;
  2. 判断 stock 值是否足够，如果足够，则下单：if (stock > n) process order;
  3. 更新 stock 值，减去下单的商品数量：new_stock = stock - n;
  4. UPDATE product SET stock = new_stock WHERE id = 100;

如果两个线程同时下单，很可能就会出现下面这样的情况：

  1. 线程 A 获取库存值为 10；
  2. 线程 B 获取库存值为 10；
  3. 线程 A 需要买 5 个商品，校验通过，并下单；
  4. 线程 B 需要买 5 个商品，校验通过，并下单；
  5. 线程 A 下单完成，更新库存值为 10 - 5 = 5；
  6. 线程 B 下单完成，更新库存值为 10 - 5 = 5；

两个线程下单结束后，商品的库存还剩 5 个，而实际上 10 个商品都已经卖光了。和提交覆盖相对的，还有另一个 丢失更新 问题，叫做 回滚覆盖，如下图所示： ![]/img/post/18/06/6.png) 操作和提交覆盖情景基本上一样，只是最后一步事务 1 的提交变成了回滚，这样 A 的余额恢复成原始值 1000，事务 2 的 UPDATE 操作也完全没有生效，想一想这真的是非常可怕，一个事务的回滚操作竟然影响了另一个正常提交的事务。回滚覆盖问题可以说是程序 bug 了，因此几乎所有的数据库都不允许回滚覆盖。

有时候我们把回滚覆盖称之为 第一类丢失更新 问题，提交覆盖称为 第二类丢失更新 问题。

隔离级别

上面说了这么多并发场景下数据操作可能遇到的问题，那么要如何解决呢？我们能想到的最简单的方法就是对操作的数据进行加锁，写的时候不允许其他事务读，读的时候不允许其他事务写，这样是不是就完美解决了？确实如此。这其实就是四大隔离级别里的 序列化，在序列化隔离级别下，可以保证事务的安全执行，数据库的一致性得以保障，但是它大大降低了事务的并发能力，性能最低。

为了调和事务的安全性和性能之间的冲突，适当的降低隔离级别，可以有效的提高数据库的并发性能。于是便有了四种不同的隔离级别：

  • 读未提交（Read Uncommitted）：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。会出现脏读，幻读，不可重复读，所有并发问题都可能遇到；

  • 读已提交（Read Committed）：本事务读取到的是最新的数据（其他事务提交后的）。不会出现脏读现象,但是会出现幻读;不可重复读;（大多数数据库的默认隔离级别都是 RC，但是 MySQL InnoDb 默认是 RR）

  • 可重复读（Repeatable Read）：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。MySQL InnoDb 默认的隔离级别,解决了不可重复读问题,但是任然存在幻读问题;（MySQL 的实现有差异，后面介绍）

  • 序列化（Serializable）：读操作会隐式获取共享锁，可以保证不同事务间的互斥。最高隔离级别，啥并发问题都没有。

针对这四种隔离级别，应该根据具体的业务来取舍，如果某个系统的业务里根本就不会出现重复读的场景，完全可以将数据库的隔离级别设置为 RC，这样可以最大程度的提高数据库的并发性。不同的隔离级别和可能发生的并发现象如下表：

![]/img/post/18/06/7.png)

其中，在 可重复读（RR） 隔离级别下，是否可能出现第二类丢失更新问题（提交覆盖）比较有争议，网上对此有很多不一致的说法，其实也是因为 MySQL 的实现和 ANSI-SQL 标准之间的差异，在标准的传统实现中，RR 隔离级别是使用持续的 X 锁和持续的 S 锁来实现的（参看下面的 “隔离级别的实现” 一节），由于是持续的 S 锁，所以避免了其他事务有写操作，也就不存在提交覆盖问题。但是 MySQL 在 RR 隔离级别下，普通的 SELECT 语句只是快照读，没有任何的加锁，和标准的 RR 是不一样的。如果要让 MySQL 在 RR 隔离级别下不发生提交覆盖，可以使用 SELECT … LOCK IN SHARE MODE 或者 SELECT … FOR UPDATE 。

隔离级别的实现

上面所说的事务和隔离级别的概念，其实都是 SQL 标准中通用的概念，但是不同的数据库产品对标准的实现也会有很大的不同。譬如在 SQL 标准中，RR 隔离级别解决了不可重复读问题，但是依然存在幻读现象；而在 MySQL 的 RR 隔离级别下，通过多版本快照读和间隙锁技术解决了幻读问题。关于这一点也有人心存质疑，譬如 Innodb 中 RR 隔离级别能否防止幻读？ 和 MySQL的InnoDB的幻读问题

可重复读和提交读是矛盾的。在同一个事务里，如果保证了可重复读，就会看不到其他事务的提交，违背了提交读；如果保证了提交读，就会导致前后两次读到的结果不一致，违背了可重复读。

个人认为RR 隔离级别下存不存在幻读现象，取决于“幻读”的这个“读”字在 MySQL 里的歧义，这个“读”到底指的是快照读，还是当前读？（快照读与当前读区别和innodb当前读与快照读）如果是快照读，MySQL 通过版本号来保证同一个事务里每次查询得到的结果集都是一致的；如果是当前读，MySQL 通过 Next-key locks 保证其他事务无法插入新的数据，从而避免幻读问题。

传统的隔离级别

隔离级别和锁本身就是两个东西，SQL 规范中定义的四种隔离级别，分别是为了解决事务并发时可能遇到的四种问题，至于如何解决，实现方式是什么，规范中并没有严格定义。

锁作为最简单最显而易见的实现方式，可能被广为人知，所以大家在讨论某个隔离级别的时候，往往会说这个隔离级别的加锁方式是什么样的。其实，锁只是实现隔离级别的几种方式之一，除了锁，实现并发问题的方式还有时间戳，多版本控制等等，这些也可以称为无锁的并发控制。

传统的隔离级别是基于锁实现的，这种方式叫做 基于锁的并发控制（Lock-Based Concurrent Control，简写 LBCC）。通过对读写操作加不同的锁，以及对释放锁的时机进行不同的控制，就可以实现四种隔离级别。

传统的锁有两种：读操作通常加 共享锁（Share locks，S锁，又叫读锁），写操作加排它锁（Exclusive locks，X锁，又叫写锁）；加了共享锁的记录，其他事务也可以读，但不能写；加了排它锁的记录，其他事务既不能读，也不能写。另外，对于锁的粒度，又分为行锁和表锁，行锁只锁某行记录，对其他行的操作不受影响，表锁会锁住整张表，所有对这个表的操作都受影响。

归纳起来，四种隔离级别的加锁策略如下：

  • 读未提交（Read Uncommitted）：事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读；通过对写操作加 “持续X锁”，对读操作不加锁 实现；

  • 读已提交（Read Committed）：事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “临时S锁” 实现；不会出现脏读；

  • 可重复读（Repeatable Read）：事务读会阻塞其他事务事务写但不阻塞读，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “持续S锁” 实现；

  • 序列化（Serializable）：为了解决幻读问题，行级锁做不到，需使用表级锁。

结合上面介绍的每种隔离级别分别是用来解决事务并发中的什么问题，再来看看它的加锁策略其实都挺有意思的。其中 读未提交 网上有很多人认为不需要加任何锁，这其实是错误的，我们上面讲过，有一种并发问题在任何隔离级别下都不允许存在，那就是第一类丢失更新（回滚覆盖），如果不对写操作加 X 锁，当两个事务同时去写某条记录时，可能会出现丢失更新问题，这里 有一个例子可以看到写操作不加 X 锁发生了回滚覆盖。再看 读已提交，它是为了解决脏读问题，只能读取已提交的记录，要怎么做才可以保证事务中的读操作读到的记录都是已提交的呢？很简单，对读操作加上 S 锁，这样如果其他事务有正在写的操作，必须等待写操作提交之后才能读，因为 S 和 X 互斥，如果在读的过程中其他事务想写，也必须等事务读完之后才可以。这里的 S 锁是一个临时 S 锁，表示事务读完之后立即释放该锁，可以让其他事务继续写，如果事务再读的话，就可能读到不一样的记录，这就是 不可重复读 了。为了让事务可以重复读，加在读操作的 S 锁变成了持续 S 锁，也就是直到事务结束时才释放该锁，这可以保证整个事务过程中，其他事务无法进行写操作，所以每次读出来的记录是一样的。最后，序列化 隔离级别下单纯的使用行锁已经实现不了，因为行锁不能阻止其他事务的插入操作，这就会导致幻读问题，这种情况下，我们可以把锁加到表上（也可以通过范围锁来实现，但是表锁就相当于把表的整个范围锁住，也算是特殊的范围锁吧）。

从上面的描述可以看出，通过对锁的类型（读锁还是写锁），锁的粒度（行锁还是表锁），持有锁的时间（临时锁还是持续锁）合理的进行组合，就可以实现四种不同的隔离级别。这四种不同的加锁策略实际上又称为 封锁协议（Locking Protocol），所谓协议，就是说不论加锁还是释放锁都得按照特定的规则来。读未提交 的加锁策略又称为 一级封锁协议，后面的分别是二级，三级，序列化 的加锁策略又称为 四级封锁协议。

其中三级封锁协议在事务的过程中为写操作加持续 X 锁，为读操作加持续 S 锁，并且在事务结束时才对锁进行释放，像这种加锁和解锁明确的分成两个阶段我们把它称作 两段锁协议（2-phase locking，简称 2PL）。在两段锁协议中规定，加锁阶段只允许加锁，不允许解锁；而解锁阶段只允许解锁，不允许加锁。这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化的（关于串行化是一个非常重要的概念，尤其是在数据恢复和备份的时候）。在两段锁协议中，还有一种特殊的形式，叫 一次封锁，意思是指在事务开始的时候，将事务可能遇到的数据全部一次锁住，再在事务结束时全部一次释放，这种方式可以有效的避免死锁发生。但是这在数据库系统中并不适用，因为事务开始时并不知道这个事务要用到哪些数据，一般在应用程序中使用的比较多。

MySQL 的隔离级别

虽然数据库的四种隔离级别通过 LBCC 技术都可以实现，但是它最大的问题是它只实现了并发的读读，对于并发的读写还是冲突的，写时不能读，读时不能写，当读写操作都很频繁时，数据库的并发性将大大降低，针对这种场景，MVCC 技术应运而生。MVCC 的全称叫做 Multi-Version Concurrent Control（多版本并发控制），InnoDb 会为每一行记录增加几个隐含的“辅助字段”，（实际上是 3 个字段：一个隐式的 ID 字段，一个事务 ID，还有一个回滚指针），事务在写一条记录时会将其拷贝一份生成这条记录的一个原始拷贝，写操作同样还是会对原记录加锁，但是读操作会读取未加锁的新记录，这就保证了读写并行。要注意的是，生成的新版本其实就是 undo log，它也是实现事务回滚的关键技术。关于 InnoDb 的 MVCC 机制的实现原理推荐看下这篇文章：MySQL数据库事务各隔离级别加锁情况–read committed && MVCC。

InnoDb 通过 MVCC 实现了读写并行，但是在不同的隔离级别下，读的方式也是有所区别的。首先要特别指出的是，在 read uncommit 隔离级别下，每次都是读取最新版本的数据行，所以不能用 MVCC 的多版本，而 serializable 隔离级别每次读取操作都会为记录加上读锁，也和 MVCC 不兼容，所以只有 RC 和 RR 这两个隔离级别才有 MVCC。下面我们通过两个例子，来看看这两个隔离级别下 MVCC 有什么区别。

还是使用上面的 account 表，首先将 MySQL 的事务隔离级别设置为 RR，然后开启一个事务，执行下面的 SQL：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

mysql> update account set balance = 2000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  2000   |
+----+----------+---------+
1 row in set (0.00 sec)

开启第二个事务，如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

可以看到事务 2 查询出来的还是 A 原始的信息，这个时候事务 1 使用 commit 提交：

mysql> commit;
Query OK, 0 rows affected (0.05 sec)

并在事务 2 中再查询一次，发现查询出来的结果不变，还是原始值：

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同样的，我们将数据库的隔离级别设置为 RC，重新做一次这个实验，第一步都是一样的，只是第二步当事务 1 commit 之后，事务 2 再查一次发现查询出来的是最新提交的记录了：

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  2000   |
+----+----------+---------+
1 row in set (0.00 sec)

通过这个实验可以知道，尽管 RR 和 RC 隔离级别都实现了 MVCC 来满足读写并行，但是读的实现方式是不一样的：RC 总是读取记录的最新版本，如果该记录被锁住，则读取该记录最新的一次快照，而 RR 是读取该记录事务开始时的那个版本。 虽然这两种读取方式不一样，但是它们读取的都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read），有时候也叫做 非阻塞读（Nonlocking Read），RR 隔离级别下的叫做 一致性非阻塞读（Consistent Nonlocking Read）。

除了 快照读 ，MySQL 还提供了另一种读取方式：当前读（Current Read），有时候又叫做 加锁读（Locking Read） 或者 阻塞读（Blocking Read），这种读操作读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据加锁的不同，又分成两类：

  • SELECT … LOCK IN SHARE MODE：加 S 锁
  • SELECT … FOR UPDATE：加 X 锁
  • INSERT / UPDATE / DELETE：加 X 锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题， 关于记录锁和间隙锁的概念后面再讲，有兴趣的同学可以和上面一样，通过两个例子来感受一下。

首先在 RC 隔离级别下，开启一个事务，执行下面的 SQL 查询所有 id > 3 的记录，使用当前读而不是快照读： 首先在 RC 隔离级别下，开启一个事务，执行下面的 SQL 查询所有 id > 3 的记录，使用当前读而不是快照读：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id > 3 lock in share mode;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  4 |        D |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同时，开启另一个事务，向 account 表中新增一条记录，然后修改 id = 4 的记录：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(name, balance) value('E', 1000);
Query OK, 1 row affected (6.24 sec)

mysql> update account set balance = 2000 where id = 4;

可以看到事务 2 在事务 1 当前读之后，仍然可以新增记录，但是在执行 update 操作的时候被阻塞，这说明了事务 1 在执行当前读的时候在 id = 4 这条记录上加了锁，但是并没有对 id > 3 这个范围加锁。然后我们切换到事务 1 中，再次查询：

mysql> select * from account where id > 3 lock in share mode;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这个时候事务 1 直接死锁了，原因其实很简单，事务 2 在 insert 新纪录的时候（id = 5）会在新纪录上加锁，所以事务 1 再次执行当前读，想获取 id > 3 的记录，就需要在 id = 4 和 id = 5 这两条记录上加锁，但是 id = 5 这条记录已经被事务 2 锁住了，于是事务 1 被事务 2 阻塞，同时事务 2 还在等待 事务 1 释放 id = 4 上的锁，于是便产生了死锁。

接下来把隔离级别设置成 RR，再重复事务 1 刚刚的操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id > 3 lock in share mode;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  4 |        D |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同时，事务 2 向 account 表中新增一条记录：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(name, balance) value('E', 1000);

我们发现，这个时候事务 2 就被阻塞了，很显然事务 1 在执行 select … lock in share mode 的时候，不仅在 id = 4 这条记录上加了锁，而且在 id > 3 这个范围上也加了锁。

关于 MySQL 不同的隔离级别，读操作的差异总结起来如下图所示（其中，读未提交和可序列化都和 MVCC 不兼容，可以暂且认为它们都属于当前读）：

![]/img/post/18/06/8.png)

查看和设置 MySQL 的隔离级别

可以通过查看 MySQL 中的系统变量 tx_isolation 的值来确定当前 MySQL 正在使用什么隔离级别。使用下面的 SQL 查询当前数据库的隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里要注意的是在 MySQL 中变量一般分为两类：用户变量 和 系统变量，用户变量的变量名格式为 @variable，而系统变量的格式为 @@variable，tx_isolation 是系统变量，所以变量名为 @@tx_isolation。其中，系统变量又可以分为 全局变量 和 会话变量，默认情况下使用 select @@variable 查询出来的是会话变量的值，也可以写作 select @@session.variable 或者 select @@local.variable，如果要查询全局变量的值，则使用 select @@global.variable。

所以通常使用下面的 SQL 查询数据库的隔离级别：

mysql> select @@session.tx_isolation, @@global.tx_isolation;
+------------------------+-----------------------+
| @@session.tx_isolation | @@global.tx_isolation |
+------------------------+-----------------------+
| REPEATABLE-READ        | REPEATABLE-READ       |
+------------------------+-----------------------+

另外可以使用 SET TRANSACTION 命令修改 MySQL 的隔离级别，譬如下面的 SQL 修改隔离级别为 RC：

mysql> set transaction isolation level read committed;

同样其他的隔离级别可以使用下面的 SQL 进行修改：

  • set transaction isolation level read uncommitted;

  • set transaction isolation level read committed;

  • set transaction isolation level repeatable read;

  • set transaction isolation level serializable;

这里也有一个要注意的地方，默认的 SET TRANSACTION 命令 只对当前会话的下一个事务有效，当下个事务结束之后，下下个事务又会恢复到当前会话的隔离级别。我在这个地方困惑了好久，因为使用 SET TRANSACTION 命令设置完隔离级别之后，通过 select @@tx_isolation 查询当前会话的隔离级别是不会变化的，但是实际上下一个事务的隔离级别确实已经修改了.

找到了一种可以查看事务隔离级别的方法，那就是查 information_schema.INNODB_TRX 表。这种方式有一个不方便的地方在于，事务里必须要先执行一条 SQL 语句，才会在 INNODB_TRX 表中有记录，如下所示：

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                   trx_id: 282133714024240
                trx_state: RUNNING
              trx_started: 2018-07-25 23:12:58
    trx_requested_lock_id: NULL
         trx_wait_started: NULL
               trx_weight: 0
      trx_mysql_thread_id: 40
                trx_query: NULL
      trx_operation_state: NULL
        trx_tables_in_use: 0
        trx_tables_locked: 0
         trx_lock_structs: 0
    trx_lock_memory_bytes: 1136
          trx_rows_locked: 0
        trx_rows_modified: 0
  trx_concurrency_tickets: 0
      trx_isolation_level: READ COMMITTED
        trx_unique_checks: 1
   trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
         trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

如果要修改当前会话的隔离级别，而不是仅仅下一个事务的隔离级别（这在做实验时挺有用的），需要使用下面命令：

mysql> set session transaction isolation level read committed;

另外，也可以使用修改系统变量的方法来修改隔离级别：

mysql> set @@session.tx_isolation = 'READ-COMMITTED';

如果要修改全局变量的话，则使用命令 set global transaction isolation level … 或者 set @@global.tx_isolation = …，方法与上面类似，不再赘述。不过要注意的是，这个命令不会改变当前会话的隔离级别，而是对新的会话有影响，所以要关闭当前会话，重新开启一个会话才能看到效果。 转载自：aneasystone’s blog

comments powered by Disqus
CATALOG
    FEATURED TAGS
    api-gateway blog docker elk git helm io istio java juc jvm kubernetes microservice mysql nginmesh security service-mesh sql注入 tips travel ubuntu vim 中间件 其他 基础 性能 正则 死锁 端口转发 网络 设计模式 配置
    FRIENDS