mysql 锁和事务模型

发表于 | 分类于

一、Locking 锁

1、锁分类

1.1、共享锁和独占锁

InnoDB实现了两种标准行级锁，一种是共享锁 (shared locks，S锁)，另一种是独占锁，或者叫排它锁 (exclusive locks，X锁)。

共享锁：允许 事务 持有 read 一行 的锁；

独占锁：允许 事务 持有 update 或 delete 一行的锁；

S锁

如果事务T1持有了行r上的S锁，则其他事务可以同时持有行r的S锁，但是不能对行r加X锁。

X锁

如果事务T1持有了行r上的X锁，则其他任何事务不能持有行r的X锁，必须等待T1在行r上的X锁释放。

如果事务T1在行r上保持S锁，则另一个事务T2对行r的锁的请求按如下方式处理：

  • T2可以同时持有S锁
  • T2如果想在行r上获取X锁，必须等待其他事务对该行添加的S锁或X锁的释放。

1.2、Intention Locks 意向锁

​ InnoDB 支持 多颗粒度加锁，允许 行锁 和 表锁 共存。例如， LOCK TABLES ... WRITE 就是在指定表上加独占锁。为了支持多颗粒度加锁，InnoDB设计了 意向锁。意向锁 是 一种 表级锁，用来 指示 一个事务对表中的一行数据需要加 哪种类型（shared 或 exclusive）的锁。

意向锁有两种：

  • intention shared lock (IS)意向共享锁：表示 一个事务 倾向于 对一行数据 使用共享锁；在 transaction 获取一个 共享锁（S） 之前 必须先获得一个 IS 锁；

  • intention exclusive lock (IX)意向独占锁：表示 一个事务 倾向于 对一行数据 使用独占锁；在 transaction 获取一个 独占锁（X） 之前 必须先获得一个 IX 锁；

例如：SELECT ... LOCK IN SHARE MODE 使用的就是 IS 锁， SELECT ... FOR UPDATE 使用的是 IX 锁；


1.3、锁的兼容性

锁的兼容矩阵如下：

— 排它锁(X) 意向排它锁(IX) 共享锁(S) 意向共享锁(IS)
排它锁(X) N N N N
意向排它锁(IX) N OK N OK
共享锁(S) N N OK OK
意向共享锁(IS) N OK OK OK

按照上面的兼容性，如果不同事务之间的锁兼容，则当前加锁事务可以持有锁，如果有冲突则会等待其他事务的锁释放。

如果一个事务请求锁时，请求的锁与已经持有的锁冲突而无法获取时，互相等待就可能会产生死锁。

意向锁不会阻止除了全表锁定请求之外的任何锁请求。
意向锁的主要目的是显示事务正在锁定某行或者正意图锁定某行。


2、InnoDB 中的锁

2.1、准备工作

示例的基础是一个只有两列的数据库表。

1
2
3
4
5
6
7
8
9
10
11
mysql> create database test;
mysql> use test;

mysql> CREATE TABLE test (
id int(11) NOT NULL,
code int(11) NOT NULL, 
PRIMARY KEY(id), 
KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 

mysql> INSERT INTO test(id,code) values(1,1),(10,10);

数据表test只有两列，id是主键索引，code是普通的索引(注意，一定不要是唯一索引)，并初始化了两条记录，分别是(1,1),(10,10)。
这样，我们验证唯一键索引就可以使用id列，验证普通索引(非唯一键二级索引)时就使用code列。

2.2、查看锁状态的方法

要看到锁的情况，必须手动开启多个事务，其中一些锁的状态的查看则必须使锁处于waiting状态，这样才能在mysql的引擎状态日志中看到。

命令：

1
mysql> show engine innodb status;

这条命令能显示最近几个事务的状态、查询和写入情况等信息。当出现死锁时，命令能给出最近的死锁明细。

2.3、Record Locks 记录锁

record lock 是 对 index record 加锁的一种锁。例如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止其他事务 插入、更新、删除 t.c1=10 的行；

record lock 总是对 index 加锁，即使表没有定义任何索引。在这种情况下 InnoDB 会添加一个隐藏主键；

查看记录锁

开启第一个事务，不提交，测试完之后回滚。

1
2
3
4
5
6
7
> mysql> start transaction;
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> update test set id=2 where id=1;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
>

事务加锁情况

1
2
3
4
5
6
7
8
9
10
> mysql> show engine innodb status\G;
> ... 
> ------------
> TRANSACTIONS
> ------------
> ---TRANSACTION 366811, ACTIVE 690 sec
> 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
> MySQL thread id 785, OS thread handle 123145432457216, query id 729076 localhost 127.0.0.1 root
> ...
>

可以看到有一行被加了锁。由之前对锁的描述可以推测出，update语句给id=1这一行上加了一个X锁。

注意：X锁广义上是一种抽象意义的排它锁，即锁一般分为X模式和S模式，狭义上指row或者index上的锁，而Record锁是索引上的锁。
为了不修改数据，可以用select ... for update语句，加锁行为和update、delete是一样的，insert加锁机制较为复杂，后面的章节会提到。

第一个事务保持原状，不要提交或者回滚，现在开启第二个事务。

1
2
3
4
5
> mysql> start transaction;
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> update test set id=3 where id=1;
>

执行update时，sql语句的执行被阻塞了。查看下事务状态:

1
2
3
4
5
6
7
8
9
10
11
12
> mysql> show engine innodb status\G;
> ...
> ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
> RECORD LOCKS space id 62 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 366820 lock_mode X locks rec but not gap waiting
> Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
>  0: len 8; hex 0000000000000001; asc         ;;
>  1: len 6; hex 0000000598e3; asc       ;;
>  2: len 7; hex 7e000001a80896; asc ~      ;;
> 
> ------------------
> ...
>

喜闻乐见，我们看到了这个锁的状态。状态标题是’事务正在等待获取锁’，描述中的lock_mode X locks rec but not gap就是本章节中的record记录锁，直译一下’X锁模式锁住了记录’。后面还有一句but not gap意思是只对record本身加锁，并不对间隙加锁，间隙锁的叙述见下一个章节。


2.4、Gap Locks 间隙锁

​ gap lock 作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;这条语句阻止其他事务插入10和20之间的数字，无论这个数字是否存在。gap lock 是性能和并发权衡的产物，只存在于部分事务隔离级别。

SELECT * FROM child WHERE id = 100;

  • 如果id本身是唯一索引，那么 gap lock 是不生效的；
  • 如果 id 和 其他的列 组成的联合索引是 唯一索引， 那么 gap lock 是生效的，锁定了 child[100][x] ~ child[100][y] 之间的记录；
  • 如果 id 上 没有加索引 或者 不是唯一索引，gap lock 生效；

简单来说就是 gap lock 锁定的是一个范围（0~N条记录），而不是单条记录；

​ InnoDB中的Gap锁是“纯粹的抑制性锁”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务所采取的间隙锁并不会阻止另一个事务对同一间隙采取间隙锁。共享锁和独占锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

禁用gap lock：

  • 将 隔离级别 设为 READ COMMITTED ；
  • 启用 innodb_locks_unsafe_for_binlog

查看间隙锁

按照官方文档，where子句查询条件是唯一键且指定了值时，只有record锁，没有gap锁。
如果where语句指定了范围，gap锁是存在的。
这里只测试验证一下当指定非唯一键索引的时候，gap锁的位置，按照文档的说法，会锁定当前索引及索引之前的间隙。(指定了非唯一键索引,例如code=10，间隙锁仍然存在\)

开启第一个事务，锁定一条非唯一的普通索引记录

1
2
3
4
5
6
7
8
9
10
11
>mysql> start transaction;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> select * from test where code = 10 for update;
>+----+------+
>| id | code |
>+----+------+
>| 10 |   10 |
>+----+------+
>1 row in set (0.00 sec)
>

由于预存了两条数据，row(1,1)和row(10,10)，此时这个间隙应该是`1。我们先插入row(2,2)来验证下gap锁的存在，再插入row(0,0)来验证gap的边界。

按照间隙锁的官方文档定义，select * from test where code = 10 for update;会锁定code=10这个索引，并且会锁定code<10的间隙。

开启第二个事务，在code=10之前的间隙中插入一条数据，看下这条数据是否能够插入。

1
2
3
4
5
>mysql> start transaction;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> insert into test values(2,2);
>

插入的时候，执行被阻塞，查看引擎状态：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
>mysql> show engine innodb status\G;
>...
>---TRANSACTION 366864, ACTIVE 5 sec inserting
>mysql tables in use 1, locked 1
>LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
>MySQL thread id 793, OS thread handle 123145434963968, query id 730065 localhost 127.0.0.1 root update
>insert into test values(2,2)
>------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
>RECORD LOCKS space id 63 page no 4 n bits 72 index code of table `test`.`test` trx id 366864 lock_mode X locks gap before rec insert intention waiting
>Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
> 0: len 8; hex 800000000000000a; asc         ;;
> 1: len 8; hex 000000000000000a; asc         ;;
>
>------------------
>...
>

插入语句被阻塞了，lock_mode X locks gap before rec，由于第一个事务锁住了1到10之间的gap，需要等待获取锁之后才能插入。

如果再开启一个事务，插入(0,0)

1
2
3
4
>mysql> start transaction;
>mysql> insert into test values(0,0);
>Query OK, 1 row affected (0.00 sec)
>

可以看到：指定的非唯一建索引的gap锁的边界是当前索引到上一个索引之间的gap\。

最后给出锁定区间的示例,首先插入一条记录(5,5)

1
2
3
>mysql> insert into test values(5,5);
>Query OK, 1 row affected (0.00 sec)
>

开启第一个事务:

1
2
3
4
5
6
7
8
9
10
11
12
13
>mysql> start transaction;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> select * from test where code between 1 and 10 for update;
>+----+------+
>| id | code |
>+----+------+
>|  1 |    1 |
>|  5 |    5 |
>| 10 |   10 |
>+----+------+
>3 rows in set (0.00 sec)
>

第二个事务，试图去更新code=5的行：

1
2
3
4
5
>mysql> begin;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> update test set code=4 where code=5;
>

执行到这里，如果第一个事务不提交或者回滚的话，第二个事务一直等待直至mysql中设定的超时时间。


2.5、Next-Key Locks

Next-key 锁实际上是Record锁和gap锁的组合。Next-key锁是在 下一个索引记录本身 和 索引之前的gap 加上S锁或是X锁(如果是读就加上S锁，如果是写就加X锁)。

​ 默认情况下，InnoDB的事务隔离级别为RR，系统参数 innodb_locks_unsafe_for_binlog 的值为false。InnoDB使用next-key锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了幻读的发生。

幻读是指在同一事务下，连续执行两次同样的SQL语句，第二次的SQL语句可能会返回之前不存在的行。

​ 当查询的索引是唯一索引时，Next-key lock会进行优化，降级为Record Lock，此时Next-key lock仅仅作用在索引本身，而不会作用于gap和下一个索引上。


查看Next-key锁

Next-key锁的作用范围

如上述例子，数据表test初始化了row(1,1),row(10,10)，然后插入了row(5,5)。数据表如下：

1
2
3
4
5
6
7
8
9
10
> mysql> select * from test;
> +----+------+
> | id | code |
> +----+------+
> |  1 |    1 |
> |  5 |    5 |
> | 10 |   10 |
> +----+------+
> 3 rows in set (0.00 sec)
>

由于id是主键、唯一索引，mysql会做优化，因此使用code这个非唯一键的二级索引来举例说明。

对于code，可能的next-key锁的范围是：

1
2
3
4
5
> (-∞,1]
> (1,5]
> (5,10]
> (10,+∞)
>

开启第一个事务，在code=5的索引上请求更新:

1
2
3
4
5
6
7
8
9
10
11
> mysql> start transaction;
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> select * from test where code=5 for update;
> +----+------+
> | id | code |
> +----+------+
> |  5 |    5 |
> +----+------+
> 1 row in set (8.81 sec)
>

之前在gap锁的章节中介绍了，code=5 for update会在code=5的索引上加一个record锁，还会在1<gap<5的间隙上加gap锁。现在不再验证，直接插入一条(8,8)：

1
2
3
4
> mysql> start transaction;
> Query OK, 0 rows affected (0.00 sec)
> mysql> insert into test values(8,8);
>

insert处于等待执行的状态，这就是next-key锁生效而导致的结果。第一个事务，锁定了区间(1,5]，由于RR的隔离级别下next-key锁处于开启生效状态，又锁定了(5,10]区间。所以插入SQL语句的执行被阻塞。

解释：在这种情况下，被锁定的区域是code=5前一个索引到它的间隙，以及next-key的区域。code=5 for update对索引的锁定用区间表示，gap锁锁定了(1,5)，record锁锁定了{5}索引记录，next-key锁锁住了(5,10]，也就是说整个(1,10]的区间被锁定了。由于是for update，所以这里的锁都是X锁，因此阻止了其他事务中带有冲突锁定的操作执行。

如果我们在第一个事务中，执行了code > 8 for update，在扫描过程中，找到了code=10，此时就会锁住10之前的间隙(5到10之间的gap)，10本身(record)，和10之后的间隙(next-key)。此时另一个事务插入(6,6),(9,9)和(11,11)都是不被允许的，只有在前一个索引5及5之前的索引和间隙才能执行插入(更新和删除也会被阻塞)。


2.6、Insert Intention Locks

插入意向锁在行插入之前由INSERT设置一种间隙锁，是意向排它锁的一种。
在多事务同时写入不同数据至同一索引间隙的时，不会发生锁等待，事务之间互相不影响其他事务的完成，这和间隙锁的定义是一致的。

假设一个记录索引包含4和7，其他不同的事务分别插入5和6，此时只要行不冲突，插入意向锁不会互相等待，可以直接获取。参照锁兼容/冲突矩阵。
插入意向锁的例子不再列举，可以查看gap锁的第一个例子。


2.7、AUTO-INC Locks

​ auto-inc lock 是一种特殊的表级锁。如果一个事务正在向表中插入值，那么任何其他事务都必须等待，以便由第一个事务插入的行接收连续的主键值。

​ 我们一般把主键设置为AUTO_INCREMENT的列，默认情况下这个字段的值为0，InnoDB会在AUTO_INCREMENT修饰下的数据列所关联的索引末尾设置独占锁。在访问自增计数器时，InnoDB使用自增锁，但是锁定仅仅持续到当前SQL语句的末尾，而不是整个事务的结束，毕竟自增锁是表级别的锁，如果长期锁定会大大降低数据库的性能。由于是表锁，在使用期间，其他会话无法插入表中。


二、不同sql语句设置的锁

​ 如果一个SQL语句要对二级索引(非主键索引)设置X模式的Record锁，InnoDB还会检索出相应的聚簇索引(主键索引)并对它们设置锁定。

1、 SELECT … FROM…不加锁

SELECT ... FROM是快照读取，除了SERIALIZABLE的事务隔离级别，该SQL语句执行时不会加任何锁。

SERIALIZABLE级别下，SELECT语句的执行会在遇到的索引记录上设置S模式的next-key锁。但是对于唯一索引，只锁定索引记录，而不会锁定gap。


2、 UPDATE系列

S锁读取(SELECT ... LOCK IN SHARE MODE)，X锁读取(SELECT ... FOR UPDATE)、更新UPDATE和删除DELETE这四类语句，采用的锁取决于搜索条件中使用的索引类型。

  • 如果使用唯一索引，InnoDB仅锁定索引记录本身，不锁定间隙。
  • 如果使用非唯一索引，或者未命中索引，InnoDB使用间隙锁或者next-key锁来锁定索引范围，这样就可以阻止其他事务插入锁定范围。

2.1、 UPDATE语句

UPDATE ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁，如果是唯一索引只锁定记录。
当UPDATE修改聚簇索引时，将对受影响的二级索引采用隐式锁，隐式锁是在索引中对二级索引的记录逻辑加锁，实际上不产生锁对象，不占用内存空间。

​ 例如 update test set code=100 where id=10; 执行的时候 code=10 的索引(code是二级索引，见文中给出的建表语句)会被加隐式锁，只有隐式锁产生冲突时才会变成显式锁(如S锁、X锁)。即此时另一个事务也去更新 id=10 这条记录，隐式锁就会升级为显示锁。
​ 这样做的好处是降低了锁的开销。

UPDATE可能会导致新的普通索引的插入。当新的索引插入之前，会首先执行一次重复索引检查。在重复检查和插入时，更新操作会对受影响的二级索引记录采用共享锁定(S锁)。


2.2、 DELETE语句

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置一个独占的next-key锁,如果是唯一索引只锁定记录。


3、 INSERT

INSERT区别于UPDATE系列单独列出，是因为它的处理方式较为特别。

插入行之前，会设置一种插入意向锁，插入意向锁表示插入的意图。如果其它事务在 要插入的位置 上设置了X锁，则无法获取插入意向锁，插入操作也因此阻塞。

INSERT在插入的行上设置X锁。该锁是一个Record锁，并不是next-key锁，即只锁定记录本身，不锁定间隙，因此不会阻止其他 session 在这行记录前的间隙中插入新的记录。


三、Deadlocks 死锁

​ 死锁是指由于每个事务都持有对方需要的锁而无法进行其他事务的情况。因为这两个事务都在等待资源变得可用，所以都不会释放它持有的锁。

1、死锁示例

该示例涉及两个客户端A和B。

首先，客户端A创建一个包含一行的表，然后开始事务。在事务中，A通过S在共享模式下选择该行来获得对该行的 锁定：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

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

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

接下来，客户端B开始事务并尝试从表中删除该行：

1
2
3
4
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

删除操作需要一个X锁。无法授予该S锁，因为它与客户端A持有的锁不兼容 ，因此该请求进入针对行和客户端B块的锁请求队列中。

最后，客户端A还尝试从表中删除该行：

1
2
3
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

此处发生死锁是因为客户端A需要 X锁才能删除该行。但是，不能授予该锁定请求，因为客户端B已经有一个X锁定请求，并且正在等待客户端A释放其S锁定。由于B事先要求锁，所以SA持有的锁也不能 升级 X为X锁。结果， InnoDB为其中一个客户端生成错误并释放其锁。客户端返回此错误：

1
2
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

届时，可以授予对另一个客户端的锁定请求，并从表中删除该行。


2、死锁检测和回滚

​ InnoDB自动检测事务 死锁并回滚一个或多个事务以打破死锁。 InnoDB尝试选择要回滚的小事务，其中事务的大小由插入，更新或删除的行数确定。

​ 如果死锁无法检测，通过设置 innodb_lock_wait_timeout 来解决；

​ 如果 一个事务被完整的会滚，那么它所持有的所有锁都会被释放；但如果由于出错仅仅一条sql被会滚，那么某些锁可能不会被释放；这是因为 InnoDB存储行锁的格式 无法确定后续的锁会被哪些sql持有；

  • 要查看InnoDB用户事务中的最后一个死锁，请使用SHOW ENGINE INNODB STATUS命令。

  • 如果频繁出现死锁，则说明事务结构或应用程序错误处理存在问题，请在innodb_print_all_deadlocks 启用该设置的情况下运行，以 将有关所有死锁的信息打印到 mysqld错误日志中


3、如何最小化并处理死锁

InnoDB使用自动行级锁定。即使在仅插入或 删除 单行的事务中，也可能会遇到死锁。这是因为这些操作并不是真正的“ 原子 ”操作。它们会自动对插入或删除的行的（可能是多个）索引记录设置锁定。

您可以使用以下技术来处理死锁并减少发生死锁的可能性：

  • 在任何时候，发出 SHOW ENGINE INNODB STATUS命令以确定最近死锁的原因。这可以帮助您调整应用程序以避免死锁。

  • 如果频繁出现死锁警告引起关注，请通过启用innodb_print_all_deadlocks 配置选项来收集更广泛的调试信息 。有关每个死锁的信息，而不仅仅是最新的死锁，都记录在MySQL 错误日志中。完成调试后，请禁用此选项。

  • 如果由于死锁而失败，请始终准备重新发出事务。死锁并不危险。请再试一次。

  • 保持交易小巧且持续时间短，以使交易不易发生冲突。

  • 进行一系列相关更改后立即提交事务，以减少冲突的发生。特别是，不要长时间未提交事务而使交互式 mysql会话保持打开状态。

  • 如果您使用锁定读取（SELECT ... FOR UPDATE或 SELECT ... LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED。

  • 修改事务中的多个表或同一表中的不同行集时，每次都要以一致的顺序执行这些操作。然后，事务形成定义明确的队列，并且不会死锁。例如，组织数据库操作到功能在应用程序中，或调用存储程序，而不是编码的多个相似序列 INSERT，UPDATE以及 DELETE在不同的地方语句。

  • 将选择好的索引添加到表中。然后，您的查询需要扫描更少的索引记录，并因此设置更少的锁。使用EXPLAIN SELECT以确定哪些索引MySQL认为最适合您的查询。

  • 使用较少的锁定。如果你能负担得起，以允许 SELECT从一个旧的快照返回数据，不要添加条款FOR UPDATE或LOCK IN SHARE MODE给它。在READ COMMITTED这里使用隔离级别是件好事，因为同一事务中的每个一致性读取均从其自己的新快照读取。

  • 如果没有其他帮助，请使用表级锁序列化事务。LOCK TABLES与事务表（例如InnoDB 表）一起使用的正确方法 是，以SET autocommit = 0（not START TRANSACTION）后跟来开始事务，直到明确提交事务后才LOCK TABLES调用 UNLOCK TABLES。例如，如果您需要写表 t1和从表中读取数据 t2，则可以执行以下操作：

    1
    2
    3
    4
    SET autocommit=0;
    LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
    COMMIT;
    UNLOCK TABLES;

    表级锁可防止对表的并发更新，从而避免死锁，但代价是对繁忙系统的响应速度较慢。

  • 序列化事务的另一种方法是创建一个仅包含一行的辅助“ 信号量 ”表。在访问其他表之前，让每个事务更新该行。这样，所有事务都以串行方式发生。请注意，InnoDB 在这种情况下，即时死锁检测算法也适用，因为序列化锁是行级锁。对于MySQL表级锁，必须使用超时方法来解决死锁。


四、Transaction Model 事务模型

​ InnoDB 的事务模型 的目标是 将 multi-versioning 和two-phase locking 的最佳属性结合起来。默认情况下 InnoDB 的查询以 “非锁定一致性读” 和 行级锁 的方式运行。

1、autocommit, commit, rollback

​ 在 InnoDB 中 所有的 用户操作都包裹在事务中；如果 启用了 autocommit，那么每条语句都是一个事务；默认情况下，每创建一个session，autocommit 都是启用的；如果SQL语句正确执行，那么就会自动条，否则会滚之前的操作，然后报错；

在 autocommit 情况下 如果 想把多条语句包裹在一个事务里，则需要如下格式：

1
2
3
4
5
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
# 不想提交，则执行 ROLLBACK;

禁用 autocommit：

1
SET autocommit=0;

2、存在的问题

​ 平常开发过程中免不了对数据库的操作，并且还会有多个线程同时开启事务后对数据库进行访问，那此时不可避免就会出现多个线程之间交叉访问而导致数据的不一致，通过对数据库的隔离级别进行设置可以保证各线程数据获取的准确性。

在介绍隔离级别之前先要弄清楚数据库在并发事务下会出现的一些状态：

1)、脏读

脏读就是一个事务读取了另外一个事务未提交的数据。

事务2读取了事务1未提交的数据。

2)、不可重复读

在同一事务中，两次读取同一数据，得到内容不同

例如事务1读取了某个数据，然后事务2更新了这个数据并提交，然后事务1又来读取了一次，那这两次读取的结果就会不一样。

3)、幻读

在一个事务的两次查询中数据记录数不一致，例如有一个事务1查询了几列数据，而事务2在此时插入了新的几列数据，事务1在接下来的查询中，就会发现有几列数据是它先前所没有的。

不可重复读是针对于多次读取同一条数据出现不同结果，幻读是多次读取而产生的记录数不一样


3、隔离级别（Isolation）

​ Isolation 是 ACID 中的 I；Isolation 是在多个事务同时进行更改和执行查询时，对性能与可靠性、一致性和结果再现性之间的平衡进行微调的设定。

​ InnoDB 提供了 4 种隔离级别：READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, 和 SERIALIZABLE。默认的 隔离级别是 REPEATABLE READ。

​ InnoDB 使用不同的锁策略 来实现隔离级别。REPEATABLE READ 用来操作重要的数据，保证 ACID；如果使用 READ UNCOMMITTED 或READ COMMITTED 可以降低锁的开销；SERIALIZABLE是一种比 SERIALIZABLE更严格的规则，一般用在专门的场景，比如 XA事务 或者 解决并发问题和死锁。

1)、read uncommitted

可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。

2)、read committed

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

3)、repeatable read(默认)

可以重复读取，但有幻读。读写观点：读取的数据行不可写，但是可以往表中新增数据。在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

4)、serializable

可读，不可写。像java中的锁，写数据必须等待另一个事务结束。


4、查看隔离级别

1
2
3
4
-- 1.查看当前会话隔离级别
select @@tx_isolation;
-- 查看系统当前隔离级别
select @@global.tx_isolation;

设置隔离级别

1
2
3
4
5
6
7
8
-- 设置当前会话隔离级别
set session transaction isolatin level repeatable read;
-- 设置系统当前隔离级别
set global transaction isolation level repeatable read;
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';

五、隔离级别实现原理

1、MVCC

参考： https://www.cnblogs.com/cjsblog/p/8365921.html

　　MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。

1.1、说明

​ 网上看到大量的文章讲到MVCC都是说给每一行增加两个隐藏的字段分别表示行的创建时间以及过期时间，它们存储的并不是时间，而是事务版本号。

​ 事实上，这种说法并不准确，严格的来讲，InnoDB会给数据库中的每一行增加三个字段，它们分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

​ 但是，为了理解的方便，我们可以这样去理解，索引接下来的讲解中也还是用这两个字段的方式去理解。

1.2、增删查改

​ 在InnoDB中，给每行增加两个隐藏字段来实现MVCC，一个用来记录数据行的创建时间，另一个用来记录行的过期时间（删除时间）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

于是乎，默认的隔离级别（REPEATABLE READ）下，增删查改变成了这样：

  • SELECT：读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
  • INSERT：将当前事务的版本号保存至行的创建版本号
  • UPDATE：新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号
  • DELETE：将当前事务的版本号保存至行的删除版本号

1.3、快照读和当前读

  • 快照读：读取的是快照版本，也就是历史版本

  • 当前读：读取的是最新版本

​ 普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

2、一致性非锁定读和锁定读

2.1、锁定读

　　在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。SELECT … LOCK IN SHARE MODE 和 SELECT … FOR UPDATE。

  • SELECT … LOCK IN SHARE MODE：给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交；

  • SELECT … FOR UPDATE：给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的；

2.2、一致性非锁定读

一致性读（consistent read）意味着 InnoDB 对一个 query 展示的数据是 多版本中一个时间点的 snapshot。

  • 如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照；

  • 如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。

Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

3、悲观锁和乐观锁

  • 悲观锁，正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

  • 乐观锁，如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

4、锁

有这样三种锁我们需要了解

  • Record Locks（记录锁）：在索引记录上加锁。
  • Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
  • Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于是Record Locks与Gap Locks的一个结合。

假设一个索引包含以下几个值：10,11,13,20。那么这个索引的next-key锁将会覆盖以下区间：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

了解了以上概念之后，接下来具体就简单分析下REPEATABLE READ隔离级别是如何实现的

5、理论分析

​ 之所以说是理论分析，是因为要是实际操作证明的话我也不知道怎么去证明，毕竟作者水平实在有限。但是，这并不意味着我在此胡说八道，有官方文档为证。

​ 这段话的大致意思是，在默认的隔离级别中，普通的SELECT用的是一致性读不加锁。而对于锁定读、UPDATE和DELETE，则需要加锁，至于加什么锁视情况而定。如果你对一个唯一索引使用了唯一的检索条件，那么只需锁定索引记录即可；如果你没有使用唯一索引作为检索条件，或者用到了索引范围扫描，那么将会使用间隙锁或者next-key锁以此来阻塞其它会话向这个范围内的间隙插入数据。

​ 作者曾经有一个误区，认为按照前面说MVCC下的增删查改的行为就不会出现任何问题，也不会出现不可重复读和幻读。但其实是大错特错。

​ 举个很简单的例子，假设事务A更新表中id=1的记录，而事务B也更新这条记录，并且B先提交，如果按照前面MVVC说的，事务A读取id=1的快照版本，那么它看不到B所提交的修改，此时如果直接更新的话就会覆盖B之前的修改，这就不对了，可能B和A修改的不是一个字段，但是这样一来，B的修改就丢失了，这是不允许的。

​ 所以，在修改的时候一定不是快照读，而是当前读。

​ 而且，前面也讲过只有普通的SELECT才是快照读，其它诸如UPDATE、删除都是当前读。修改的时候加锁这是必然的，同时为了防止幻读的出现还需要加间隙锁。

  • 一致性读保证了可用重复读
  • 间隙锁防止了幻读

回想一下

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。