/ db

mysql-lock

参考文章:

首先,多用户并发修改同一条记录时,肯定是后提交的用户将覆盖掉前者提交的结果了。

高并发的应用场景。

先来就库存超卖问题作描述:一般电子商务网站都会遇到如团购、秒杀、特价之类的活动,而这样的活动有一个共同的特点就是访问量激增、上千甚至上万人抢购一个商品。然而,作为活动商品,库存肯定是很有限的,如何控制库存不让出现超买,以防止造成不必要的损失是众多电子商务网站程序员头疼的问题,这同时也是最基本的问题。

从技术方面剖析,很多人肯定会想到事务,但是事务是控制库存超卖的必要条件,但不是充分必要条件。

举例:

  • 总库存:4个商品
  • 请求人:a、1个商品 b、2个商品 c、3个商品

程序伪代码如下:

beginTranse(开启事务)

try{
    $result = $dbca->query('select amount from s_store where postID = 12345');

    if($result->amount > 0){
        //quantity为请求减掉的库存数量
        $dbca->query('update s_store set amount = amount - quantity where postID = 12345');
    }
}catch($e Exception){
    rollBack(回滚)
}

commit(提交事务)

以上代码就是我们平时控制库存写的代码了,大多数人都会这么写,看似问题不大,其实隐藏着巨大的漏洞。数据库的访问其实就是对磁盘文件的访问,数据库中的表其实就是保存在磁盘上的一个个文件,甚至一个文件包含了多张表。例如由于高并发,当前有三个用户a、b、c三个用户进入到了这个事务中,这个时候会产生一个共享锁,所以在select的时候,这三个用户查到的库存数量都是4个,同时还要注意,mysql innodb查到的结果是有版本控制的,在其他用户更新没有commit之前(也就是没有产生新版本之前),当前用户查到的结果依然是旧版本;

然后是update,假如这三个用户同时到达update这里,这个时候服务器会把把并发update语句串行化,也就是给同时到达这里的是三个用户排个序,一个一个执行,并生成排他锁,在当前这个update语句commit之前,其他用户等待执行,commit后,生成新的版本;这样执行完后,库存肯定为负数了。

根据上述分析,我们可以在 update 之前检查库存情况,即 update s_store set amount = amount - quantity where amount>=quantity and postID = 12345,即:

beginTranse(开启事务)

try{
    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){
    rollBack(回滚)
}

commit(提交事务)

这里已经解决了超卖的必要条件。下面还要解决超卖的充分条件:如何抗住瞬间高并发流量。

  1. 在秒杀的情况下,肯定不能如此高频率的去读写数据库,会严重造成性能问题的。必须使用缓存,将需要秒杀的商品放入缓存中,并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下,先将商品数量递减(加锁/解锁)后再进行其他方面的处理,处理失败在将数据递增1(加锁/解锁),否则表示交易成功。当商品数量递减到0时,表示商品秒杀完毕,拒绝其他用户的请求。

  2. 这个肯定不能直接操作数据库的,会挂的。直接读库写库对数据库压力太大,要用缓存。把你要卖出的商品比如10个商品放到缓存中;然后在memcache里设置一个计数器来记录请求数,这个请求数你可以以你要秒杀卖出的商品数为基数,这里我们用库存的10倍,也就是只允许100个请求进来。那当计数器达到100的时候,后面进来的就显示秒杀结束,这样可以减轻你的服务器的压力。然后根据这100个请求,先付款的先得后付款的提示商品以秒杀完。

  3. 首先,多用户并发修改同一条记录时,肯定是后提交的用户将覆盖掉前者提交的结果了。这个直接可以使用加锁机制去解决,乐观锁或者悲观锁。

  4. 不建议在数据库层面加锁,建议通过服务端的内存锁(锁主键)。当某个用户要修改某个id的数据时,把要修改的id存入memcache,若其他用户触发修改此id的数据时,读到memcache有这个id的值时,就阻止那个用户修改。

  5. 实际应用中,并不是让mysql去直面大并发读写,会借助“外力”,比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。

锁的种类

锁的种类一般分为乐观锁悲观锁两种,InnoDB 存储引擎中使用的就是悲观锁,而按照锁的粒度划分,也可以分成行锁表锁

  • 乐观锁,就是在数据库设计一个版本号的字段,每次修改都使其+1,这样在提交时比对提交前的版本号就知道是不是并发提交了,但是有个缺点就是只能是应用中控制,如果有跨应用修改同一条数据乐观锁就没办法了,这个时候可以考虑悲观锁。
  • 悲观锁,就是直接在数据库层面将数据锁死,SELECT FOR UPDATE,这样其他线程将无法提交数据。

乐观锁是一种防止此冲突的方法:一行数据 同时被多个用户更新。例如,同一时间内,用户 A 和用户 B 都在编辑 相同的 wiki 文章。用户 A 保存他的编辑后,用户 B 也点击“保存”按钮来 保存他的编辑。实际上,用户 B 正在处理的是过时版本的文章, 因此最好是,想办法阻止他保存文章并向他提示一些信息。

乐观锁通过使用一个字段来记录每行的版本号来解决上述问题。 当使用过时的版本号保存一行数据时,抛出异常。这就需要添加一个列,以存储每行的版本号。这个列应当是长整型(在 MySQL 中是 BIGINT DEFAULT 0)。

锁的粒度

MySQL 的锁的粒度分为::

  • 表级锁:开销小,加锁块;不会出现死锁,锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发性也最高。
  • 页面锁:开销和加锁界于表锁和行锁之间,会出现死锁;锁定粒度界与表锁和行锁之间,并发一般。

死锁的发生

既然 InnoDB 中实现的锁是悲观的,那么不同事务之间就可能会互相等待对方释放锁造成死锁,最终导致事务发生错误;想要在 MySQL 中制造死锁的问题其实非常容易:

MyISAM

MyISAM 加表锁

MyISAM在执行查询前,会自动执行表的加锁、解锁操作,一般情况下不需要用户手动加、解锁,但是有的时候也需要显示加锁。
比如:检索某一个时刻t1,t2表中数据数量。常用代码如下:

SELECT COUNT(t1.id1) AS 'sum'
FROM t1;

SELECT COUNT(t2.id1) AS 'sum'
FROM t2;

其实这是不正确的,很有可能当你在检索t1的那个时间点,t2的数据已经发生了变化,也就是说你检查出的t1和t2数据结果不是在同一个时间点上。

正确的做法是


MyISAM 并发插入问题

MyISAM 存储引擎有一个系统变量,concurrent_insert,专门用来控制并发插入行为:

  • concurrent_insert 为 0 时候,不允许插入;
  • concurrent_insert 为 1 时候,如果 mysql 没有空洞(中间没有被删除的行),myISAM运行一个进程读表的时候,另一个进程从表尾插入记录,这也是mysql默认设置。
  • concurrent_insert 为 2 时候,无论 MyISAM 表中有没有空洞,都允许在表尾并行的插入。

MyISAM 锁调度问题

更改数据的语句(INSERT、UPDTE 和 DELETE),会比查询语句(SELECT)具有更高的优先级。添加数据的人应该比读取数据的人更重要,因为考虑到插入数据可能占用较长时间,在这期间客户端做不了其他事情。而相反,查询数据的人一般都愿意等待。就像在购物网站中,下单的用户会比浏览产品的用户,具有更高的优先级。

当服务器为客户端执行一个 INSERT 语句的时候,它会锁住相关的表,以排斥其他客户端访问,直到它执行完毕。不过 InnoDB 倒不是这样:它只锁住行,而不是整个表。对于一个有大量并发数据请求的繁忙服务器来说,锁表会导致其他用户延迟,尤其在有人使用多行语法来插入大量数据的时候。

MyISAM 存储引擎的读锁和写锁是互斥的,读写操作是串行的,如果读写两个进程同时请求同一张表,Mysql将会使写进程先获得锁。不仅仅如此,即使读请求先到达锁等待队列,写锁后到达,写锁也会先执行。因为mysql因为写请求比读请求更加重要。这也正是MyISAM不适合含有大量更新操作和查询操作应用的原因。

MariaDB [(none)]> SHOW STATUS LIKE 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 20    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

InnoDB(默认引擎)

InnoDB 引擎的表只锁定相关的行,而不锁定整个表。

查看InnoDB行锁争用情况:

InnoDB行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的。这意味着:只有通过索引条件检索数据,InnoDB 才使用行级锁,否则 InnoDB将使用表锁,在实际开发中应当注意。

CREATE TABLE `t1` (
	`id` int(5) DEFAULT NULL,
	`status` int(3) UNSIGNED ZEROFILL NOT NULL DEFAULT '000'
) ENGINE = InnoDB CHARSET = utf8;

INSERT INTO t1
VALUES (1, 1),(2, 2);

案例1

会话1:

MariaDB [birdwatchers]> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [birdwatchers]> SELECT * FROM t1 WHERE id1 = 1 FOR UPDATE;
+------+--------+
| id   | status |
+------+--------+
|    1 |   001  |
+------+--------+
1 row in set (0.0 sec)

会话2:

MariaDB [birdwatchers]> SET autocommit = 0;
MariaDB [birdwatchers]> SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

因为这里我们没有给表加索引,所以,在使用 SELECT FOR UPDATE 给行加锁时,实际上是加到了表锁。所以会话2 会超时。会话2 重新执行命令,并在会话1 执行 commit; 释放锁后,会话2 会输出:

MariaDB [birdwatchers]> SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
+------+--------+
| id   | status |
+------+--------+
|    2 |   002  |
+------+--------+
1 row in set (12.37 sec)

这里的 12.37 秒,就是被会话1 加表锁阻塞的时间。

接下来我们给 id1 这一列加上索引,让行锁可以正常工作。会话2 执行 commit; 释放锁后,回到会话1执行:

ALTER TABLE t1 ADD INDEX id1(id1);

这时行锁就正产了,两个会话同时执行 SELECT FOR UPDATE 都不会被阻塞了。

在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

InnoDB行锁是通过给索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说:如果不通过索引条件检索数据,那么InnoDB将对表中所有数据加锁,实际效果跟表锁一样。

行锁分为三种情形:

  1. Record lock:对索引项加锁,即锁定一条记录。
  2. Gap lock:对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁,即锁定一个范围的记录,不包含记录本身。
  3. Next-key Lock:锁定一个范围的记录并包含记录本身(上面两者的结合)。

注意:InnoDB默认级别是repeatable-read级别,所以下面说的都是在RR级别中的。

之前一直搞不懂Gap Lock和Next-key Lock的区别,直到在网上看到一句话豁然开朗,希望对各位有帮助。

Next-Key Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record Lock),再对索引记录两边的间隙加上间隙锁(Gap Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

实例展示:

CREATE TABLE `test` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`age` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `keyname` (`age`)
) ENGINE = InnoDB CHARSET = utf8;

INSERT INTO test (age)
VALUES (3),(6),(9);
MariaDB [birdwatchers]> select * from test;
+----+------+
| id | age  |
+----+------+
|  1 |    3 |
|  2 |    6 |
|  3 |    9 |
+----+------+
3 rows in set (0.00 sec)

这样我们age段的索引就分为

(negative infinity, 3],
(3,6],
(6,9],
(9,positive infinity);

MySql索引算法原理解析(通俗易懂,只讲B-tree)

我们来看一下几种情况:

1、当事务A执行以下语句:

mysql> select * from test where age=6 for update;

不仅使用行锁锁住了相应的数据行,同时也在两边的区间,(5,6](6,9] 都加入了gap锁。

这样事务B就无法在这个两个区间insert进新数据,但是事务B可以在两个区间外的区间插入数据。

2、当事务A执行

mysql> select * from fenye where age=7 for update;

那么就会给(6,9]这个区间加锁,别的事务无法在此区间插入或更新数据。

3、如果查询的数据不再范围内,

比如事务A执行

mysql> select * from fenye where age=100 for update;

那么加锁区间就是(9,positive infinity)

小结:行锁防止别的事务修改或删除,间隙锁防止别的事务新增,行锁和间隙锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

何时在InnoDB中使用表锁?

nnoDB在绝大部分情况会使用行级锁,因为事务和行锁往往是我们选择InnoDB的原因,但是有些情况我们也考虑使用表级锁。

1、当事务需要更新大部分数据时,表又比较大,如果使用默认的行锁,不仅效率低,而且还容易造成其他事务长时间等待和锁冲突。
2、事务比较复杂,很可能引起死锁导致回滚。

InnoDB的加锁分析

MySQL InnoDB存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注:与MVCC相对的,是基于锁的并发控制,Lock-Based Concurrency Control)。MVCC最大的好处,相信也是耳熟能详:读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,这也是为什么现阶段,几乎所有的RDBMS,都支持了MVCC。

在MVCC并发控制中,读操作可以分成两类:快照读 (snapshot read)与当前读 (current read)。快照读,读取的是记录的可见版本 (有可能是历史版本),不用加锁。当前读,读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中,哪些读操作是快照读?哪些操作又是当前读呢?以MySQL InnoDB为例:

  • 快照读:简单地 SELECT 操作,属于快照读,不加锁。(当然,也有例外,下面会分析)
    • select * from table where ?;
  • 当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert into table values (…);
    • update table set ? where ?;
    • delete from table where ?;
    • 所有以上的语句,都属于当前读,读取记录的最新版本。并且,读取之后,还需要保证其他并发事务不能修改当前记录,对读取记录加锁。其中,除了第一条语句,对读取记录加S锁 (共享锁)外,其他的操作,都加的是X锁 (排它锁)。

为什么将 插入/更新/删除 操作,都归为当前读?

可以看看下面这个 更新 操作,在数据库中的执行流程:

mysql-lock-current-read

从图中,可以看到,一个Update操作的具体流程。当Update SQL被发给MySQL后,MySQL Server会根据where条件,读取第一条满足条件的记录,然后InnoDB引擎会将第一条记录返回,并加锁 (current read)。待MySQL Server收到这条加锁的记录之后,会再发起一个Update请求,更新这条记录。一条记录操作完成,再读取下一条记录,直至没有满足条件的记录为止。因此,Update操作内部,就包含了一个当前读。同理,Delete操作也一样。Insert操作会稍微有些不同,简单来说,就是Insert操作可能会触发Unique Key的冲突检查,也会进行一个当前读。

注:根据上图的交互,针对一条当前读的SQL语句,InnoDB与MySQL Server的交互,是一条一条进行的,因此,加锁也是一条一条进行的。先对一条满足条件的记录加锁,返回给MySQL Server,做一些DML操作;然后在读取下一条加锁,直至读取完毕。

InnoDB的加锁分析背景1:聚簇索引(Cluster Index)

InnoDB存储引擎的数据组织方式,是聚簇索引表:完整的记录,存储在主键索引中,通过主键索引,就可以获取记录所有的列。关于聚簇索引表的组织方式,可以参考MySQL的官方文档:Clustered and Secondary Indexes 。本文假设读者对这个,已经有了一定的认识,就不再做具体的介绍。接下来的部分,主键索引/聚簇索引 两个名称,会有一些混用,望读者知晓。

InnoDB的加锁分析背景2:二阶加锁(Two-Phase Locking)

二阶加锁,说的是锁操作分为两个阶段:加锁阶段与解锁阶段,并且保证加锁阶段与解锁阶段不相交。下面,仍旧以MySQL为例,来简单看看二阶加锁在MySQL中的实现。

2pl

从上图可以看出,2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段:只加锁,不放锁。解锁阶段:只放锁,不加锁。

InnoDB的加锁分析背景3:事务的隔离级别(Isolation Level)

隔离级别:一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。隔离级别从允许的并发副作用(例如,脏读或虚拟读取)的角度进行描述。

MySQL/InnoDB定义的4种隔离级别:

  • Read Uncommited,可以读取未提交记录。最弱的隔离级别,脏读不可重复读以及幻读都可能发生。此隔离级别,不会使用,忽略。
  • Read Committed (RC),快照读忽略。避免了脏读。本文不考虑。
    • 针对当前读,RC隔离级别保证对读取到的记录加锁 (记录锁),存在幻读现象。
  • Repeatable Read (RR)快照读忽略。本文不考虑。
    • 针对当前读,RR隔离级别保证对读取到的记录加锁 (记录锁),同时保证对读取的范围加锁,新的满足查询条件的记录不能够插入 (间隙锁),不存在幻读现象。
    • 即可以放心重复读取,不用担心数据不一致的意思。
  • Serializable,可串行化的。
    • 从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读,所有的读操作均为当前读,读加读锁 (S锁),写加写锁 (X锁)。
    • Serializable隔离级别下,读写冲突,因此并发度急剧下降,在MySQL/InnoDB下不建议使用。

一条简单SQL的加锁实现分析

在介绍完一些背景知识之后,本文接下来将选择几个有代表性的例子,来详细分析MySQL的加锁处理。当然,还是从最简单的例子说起。经常有朋友发给我一个SQL,然后问我,这个SQL加什么锁?就如同下面两条简单的SQL,他们加什么锁?

SQL1:select * from t1 where id = 10;
SQL2:delete from t1 where id = 10;

针对这个问题,该怎么回答?我能想象到的一个答案是:

SQL1:不加锁。因为MySQL是使用多版本并发控制的,读不加锁。
SQL2:对id = 10的记录加写锁 (走主键索引)。

这个答案对吗?说不上来。即可能是正确的,也有可能是错误的,已知条件不足,这个问题没有答案。如果让我来回答这个问题,我必须还要知道以下的一些前提,前提不同,我能给出的答案也就不同。要回答这个问题,还缺少哪些前提条件?

  • 前提一:id列是不是主键?
  • 前提二:当前系统的隔离级别是什么?
  • 前提三:id列如果不是主键,那么id列上有索引吗?
  • 前提四:id列上如果有二级索引,那么这个索引是唯一索引吗?
  • 前提五:两个SQL的执行计划是什么?索引扫描?全表扫描?

没有这些前提,直接就给定一条SQL,然后问这个SQL会加什么锁,都是很业余的表现。而当这些问题有了明确的答案之后,给定的SQL会加什么锁,也就一目了然。下面,我将这些问题的答案进行组合,然后按照从易到难的顺序,逐个分析每种组合下,对应的SQL会加哪些锁?

注:下面的这些组合,我做了一个前提假设,也就是有索引时,执行计划一定会选择使用索引进行过滤 (索引扫描)。但实际情况会复杂很多,真正的执行计划,还是需要根据MySQL输出的为准。

  • 组合一:id列是主键,RC隔离级别
  • 组合二:id列是二级唯一索引,RC隔离级别
  • 组合三:id列是二级非唯一索引,RC隔离级别
  • 组合四:id列上没有索引,RC隔离级别
  • 组合五:id列是主键,RR隔离级别
  • 组合六:id列是二级唯一索引,RR隔离级别
  • 组合七:id列是二级非唯一索引,RR隔离级别
  • 组合八:id列上没有索引,RR隔离级别
  • 组合九:Serializable隔离级别

排列组合还没有列举完全,但是看起来,已经很多了。真的有必要这么复杂吗?事实上,要分析加锁,就是需要这么复杂。但是从另一个角度来说,只要你选定了一种组合,SQL需要加哪些锁,其实也就确定了。接下来,就让我们来逐个分析这9种组合下的SQL加锁策略。

注:在前面八种组合下,也就是RC,RR隔离级别下,SQL1:select操作均不加锁,采用的是快照读,因此在下面的讨论中就忽略了,主要讨论SQL2:delete操作的加锁。如下图所示:

msyql-lock-zuhe1

组合一:id列是主键,RC隔离级别

这个组合,是最简单,最容易分析的组合。id是主键,Read Committed隔离级别,给定SQL:delete from t1 where id = 10; 只需要将主键上,id = 10的记录加上X锁即可。

结论:id是主键时,此SQL只需要在id=10这条记录上加X锁即可。

组合二:id列是二级唯一索引,RC隔离级别

这个组合,id不是主键,而是一个Unique的二级索引键值。那么在RC隔离级别下,delete from t1 where id = 10; 需要加什么锁呢?见下图:

mysql-lock-zuhe2

此组合中,id是unique索引,而主键是name列。此时,加锁的情况由于组合一有所不同。

由于id是unique索引,因此delete语句会选择走id列的索引进行where条件的过滤,在找到id=10的记录后,首先会将unique索引上的id=10索引记录加上X锁,同时,会根据读取到的name列,回主键索引(聚簇索引),然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。

为什么聚簇索引上的记录也要加锁?试想一下,如果并发的一个SQL,是通过主键索引来更新:update t1 set id = 100 where name = ‘d’; 此时,如果delete语句没有将主键索引上的记录加锁,那么并发的update就会感知不到delete语句的存在,违背了同一记录上的更新/删除需要串行执行的约束

结论:若id列是unique列,其上有unique索引。那么SQL需要加两个X锁,一个对应于id unique索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name='d',id=10]的记录。

组合三:id列是二级非唯一索引,RC隔离级别

相对于组合一、二,组合三又发生了变化,隔离级别仍旧是RC不变,但是id列上的约束又降低了,id列不再唯一,只有一个普通的索引。假设delete from t1 where id = 10; 语句,仍旧选择id列上的索引进行过滤where条件,那么此时会持有哪些锁?同样见下图:

mysql-lock-zuhe3

根据此图,可以看到,首先,id列索引上,满足id = 10查询条件的记录,均已加锁。同时,这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于,组合二最多只有一个满足等值查询的记录,而组合三会将所有满足查询条件的记录都加锁。

结论:若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加锁。

组合四:id列上没有索引,RC隔离级别

相对于前面三个组合,这是一个比较特殊的情况。id列上没有索引,where id = 10;这个过滤条件,没法通过索引进行过滤,那么只能走全表扫描做过滤。对应于这个组合,SQL会加什么锁?或者是换句话说,全表扫描时,会加什么锁?这个答案也有很多:有人说会在表上加X锁;有人说会将聚簇索引上,选择出来的id = 10;的记录加上X锁。那么实际情况呢?请看下图:

mysql-lock-zuhe4

由于id列上没有索引,因此只能走聚簇索引,进行全部扫描。从图中可以看到,满足删除条件的记录有两条,但是,聚簇索引上所有的记录,都被加上了X锁。无论记录是否满足条件,全部被加上X锁。既不是加表锁,也不是在满足条件的记录上加行锁。

有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。

注:在实际的实现中,MySQL有一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。

结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。

组合五:id列是主键,RR隔离级别

上面的四个组合,都是在Read Committed隔离级别下的加锁行为,接下来的四个组合,是在Repeatable Read隔离级别下的加锁行为。

组合五,id列是主键列,Repeatable Read隔离级别,针对delete from t1 where id = 10; 这条SQL,加锁与组合一:[id主键,Read Committed]一致。

组合六:id列是二级唯一索引,RR隔离级别

组合六的加锁,与组合二:[id唯一索引,Read Committed]一致。两个X锁,id唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个。

组合七:id列是二级非唯一索引,RR隔离级别

还记得前面提到的MySQL的四种隔离级别的区别吗?RC隔离级别允许幻读,而RR隔离级别,不允许存在幻读。但是在组合五、组合六中,加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下,如何防止幻读呢?问题的答案,就在这里揭晓。

Repeatable Read隔离级别,id上有一个非唯一索引,执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤,最后的加锁行为,是怎么样的呢?同样看下面这幅图:

mysql-lock-zuhe7

此图,相对于组合三:[id列上非唯一锁,Read Committed]看似相同,其实却有很大的区别。最大的区别在于,这幅图中多了一个GAP锁,而且GAP锁看起来也不是加在记录上的,倒像是加载两条记录之间的位置,GAP锁有何用?

其实这个多出来的GAP锁,就是RR隔离级别相对于RC隔离级别,不会出现幻读的关键。确实,GAP锁锁住的位置,也不是记录本身,而是两条记录之间的GAP。

如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。

如图中所示,有哪些位置可以插入新的满足条件的项 (id = 10),考虑到B+树索引的有序性,满足条件的项一定是连续存放的?

  • 记录[6,c]之前,不会插入id=10的记录;
  • [6,c][10,b]间可以插入[10, aa]
  • [10,b][10,d]间,可以插入新的[10,bb],[10,c]等;
  • [10,d][11,f]间可以插入满足条件的[10,e],[10,z]等;
  • [11,f]之后也不会插入满足条件的记录。

因此,为了保证[6,c][10,b]间,[10,b][10,d]间,[10,d][11,f]不会插入新的满足条件的记录,MySQL选择了用GAP锁,将这三个GAP给锁起来。

Insert操作,如insert [10,aa],首先会定位到[6,c][10,b]间,然后在插入前,会检查这个GAP是否已经被锁上,如果被锁上,则Insert不能插入记录。因此,通过第一遍的当前读,不仅将满足条件的记录锁上 (X锁),与组合三类似。同时还是增加3把GAP锁,将可能插入满足条件记录的3个GAP给锁上,保证后续的Insert不能插入新的id=10的记录,也就杜绝了同一事务的第二次当前读,出现幻象的情况。

有心的朋友看到这儿,可以会问:既然防止幻读,需要靠GAP锁的保护,为什么组合五、组合六,也是RR隔离级别,却不需要加GAP锁呢?

首先,这是一个好问题。其次,回答这个问题,也很简单。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。而组合五,id是主键;组合六,id是unique键,都能够保证唯一性。一个等值查询,最多只能返回一条记录。而且新的相同取值的记录,是无法insert/update进表的,因此也就避免了GAP锁的使用。

结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,对应SQL:delete from t1 where id = 10; 首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。

组合八:id列上没有索引,RR隔离级别

Repeatable Read隔离级别下的最后一种情况,id列上没有索引。此时SQL:delete from t1 where id = 10; 没有其他的路径可以选择,只能进行全表扫描。最终的加锁情况,如下图所示:

mysql-lock-zuhe8

如图,这是一个很恐怖的现象。首先,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。这个示例表,只有6条记录,一共需要6个记录锁,7个GAP锁。试想,如果表上有1000万条记录呢?

在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。

结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。

组合九:Serializable隔离级别

Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。

Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。

结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读就是加锁的,所有的读操作,都是当前读。

死锁的原理与分析

深入理解MySQL如何加锁,有两个比较重要的作用:

  1. 可以根据MySQL的加锁规则,写出不会发生死锁的SQL;
  2. 可以根据MySQL的加锁规则,定位出线上产生死锁的原因;

下面,来看看两个死锁的例子:

mysql-lock-dead1
mysql-lock-dead2

  • 两个 Session 的两条 SQL 产生死锁(22锁);
    • 最常见的死锁,每个事务执行两条SQL,分别持有了一把锁,然后加另一把锁,产生死锁。
  • 两个 Session 的一条 SQL 产生死锁(21锁);
    • 虽然每个Session都只有一条语句,仍旧会产生死锁。
    • 针对Session 1,从name索引出发,读到的[hdc, 1][hdc, 6]均满足条件,不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁,加锁顺序为先[1,hdc,100],后[6,hdc,10]
    • 而Session 2,从pubtime索引出发,[10,6],[100,1]均满足过滤条件,同样也会加聚簇索引上的记录X锁,加锁顺序为[6,hdc,10],后[1,hdc,100]
    • 发现没有,跟Session 1的加锁顺序正好相反,如果两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。

结论:死锁的发生与否,并不在于事务中有多少条SQL语句,死锁的关键在于:两个(或以上)的Session加锁的顺序不一致

而使用本文上面提到的,分析MySQL每条SQL语句的加锁规则,分析出每条语句的加锁顺序,然后检查多个并发SQL间是否存在以相反的顺序加锁的情况,就可以分析出各种潜在的死锁情况,也可以分析出线上死锁发生的原因。

我们说过MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。

在上面的例子中我们可以看到,当两个事务都需要获得对方持有的锁才能够继续完成事务,导致双方都在等待,产生死锁。

发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。

避免死锁:(MyISAM总是一次获得SQL语句所需要的全部锁,而且是表锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。)

有多种方法可以避免死锁,这里只介绍常见的三种:

1、如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会
2、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率
3、对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率

总结

写到这儿,本文也告一段落,做一个简单的总结,要做的完全掌握MySQL/InnoDB的加锁规则,甚至是其他任何数据库的加锁规则,需要具备以下的一些知识点:

  • 了解数据库的一些基本理论知识:
    • 数据的存储格式 (堆组织表 vs 聚簇索引表);
    • 并发控制协议 (MVCC vs Lock-Based CC);
    • Two-Phase Locking;
    • 数据库的隔离级别定义 (Isolation Level);
  • 了解SQL本身的执行计划 (主键扫描 vs 唯一键扫描 vs 范围扫描 vs 全表扫描);
  • 了解数据库本身的一些实现细节 (过滤条件提取;Index Condition Pushdown;Semi-Consistent Read);
  • 了解死锁产生的原因及分析的方法 (加锁顺序不一致;分析每个SQL的加锁顺序);

面试题

请制造一个死锁的场景:

CREATE TABLE `subway` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`line` tinyint(3) NOT NULL,
	`stop` varchar(20) NOT NULL,
	`sequence` tinyint(3) NOT NULL,
	PRIMARY KEY (`id`),
	KEY `line_sotp` (`line`, `stop`)
) ENGINE = InnoDB CHARSET = utf8;

INSERT INTO subway (line, stop, sequence)
VALUES (1, '莘庄', 1),
	(1, '人民广场', 13),
	(5, '莘庄', 1),
	(8, '人民广场', 15),
	(2, '人民广场', 18);
+------+--------------+----------+
| line | stop         | sequence |
+------+--------------+----------+
|    1 | 莘庄         |        1 |
|    1 | 人民广场     |       13 |
|    5 | 莘庄         |        1 |
|    8 | 人民广场     |       15 |
|    2 | 人民广场     |       18 |
+------+--------------+----------+
  1. A 到 B 是否可以直达?

这里的关键点在于,JOIN 自身。

SELECT s1.line, s1.stop AS A, s2.stop AS B
FROM subway s1
	JOIN subway s2 USING (line)
WHERE s1.stop = '莘庄'
	AND s2.stop = '人民广场';
  1. 换乘一次

  2. 制造一个死锁场景

会话1:

BEGIN;
SELECT * FROM subway WHERE id=1 FOR UPDATE;
UPDATE * FROM SET stop='莘庄(xx广场)' WHERE id=5;

会话2:

BEGIN;
DELETE FROM subway WHERE id=5;
DELETE FROM subway WHERE id=1;