MySQL的锁机制和加锁原理深入分析

一、MySQL/InnoDB中的行锁和表锁问题

首先我们知道InnoDB默认支持的是行锁，但这并不代表InnoDB不支持表锁。必须明白这一点在InnoDB中并不是在数据行上加锁，而是在对应的索引上加锁，这一点和oracle并不同，后者是在数据行上加锁的。这种实现的特点是：只有通过索引条件检索数据的时候加的是行锁，否则加表锁！假如检索条件没有用到索引，也是加表锁！

1.通过非索引项检索数据，加表锁

price属性并没有加索引，因此这时候添加的锁为表级锁！
窗口1：

mysql>set autocommit=0;
mysql> select * from product where price=88 for update;或者 update product set price=99 where price=88
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  2 | 蒙牛 |    88 |   1 |
+----+------+-------+-----+

窗口2：
mysql> update product set price=price-100 where id=6;
这里会等待，直到窗口1 commit后显示下面结果！
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

2、InnoDB锁的特性

在不通过索引条件查询的时候，InnoDB使用的确实是表锁！
由于 MySQL 的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行 的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论 是使用主键索引、唯一索引或普通索引,InnoDB 都会使用行锁来对数据加锁。
即便在条件中使用了索引字段,但是否使用索引来检索数据是由 MySQL 通过判断不同 执行计划的代价来决定的,如果 MySQL 认为全表扫 效率更高,比如对一些很小的表,它 就不会使用索引,这种情况下 InnoDB 将使用表锁,而不是行锁。因此,在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

3、使用相同索引值但是不同行引发的冲突，这个的主要原因还是由于Gap Lock

这里的num属性 加上了普通索引，price属性并没有索引
窗口1：
mysql> set autocommit=0;
Query OK, 0 rows affected

mysql> select * from product where num=1 and price=68 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  1 | 伊利 |    68 |   1 |
+----+------+-------+-----+

窗口2：
mysql> update product set price=price+100 where num=1 and price=88;
这里会发生等待，直到窗口1 commit 显示下面结果
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from product;
+----+----------+-------+-----+
| id | name     | price | num |
+----+----------+-------+-----+
|  1 | 伊利     |    68 |   1 |
|  2 | 蒙牛     |   188 |   1 |
+----+----------+-------+-----+
 

4、当使用索引检索数据时不同事务可以操作不同行数据

锁一行数据，DML操作其他行并没有影响
窗口1：
mysql> select * from user where id=1 for update;
+----+-------+
| id | price |
+----+-------+
|  1 |   400 |
+----+-------+

窗口2：
mysql> update user set price=price+100 where id=2;
无需等待窗口1 commit
Database changed
Rows matched: 1  Changed: 1  Warnings: 0

5.查看执行计划

mysql> explain select * from product where price=88 for update;
+----+-------------+------+
| id | select_type | type | 
+----+-------------+------+
|  1 | SIMPLE      | ALL  |
+----+-------------+------+
type是ALL表明使用的是表锁！

 

6.Gap Lock

​    间隙锁，是在索引的间隙之间加上锁，这是为什么Repeatable Read隔离级别下能防止幻读的主要原因。

​    幻读：事务A执行了一次读操作，此时事务B在事务A的影响区间内更新了一条数据，此时事务A在执行一次读操作时，会发现出现了不合理的数据。有关幻读的详细解释：https://blog.csdn.net/qq_38238296/article/details/88363017

6.2.​1 什么叫间隙锁

​    直接通过例子来说明：

mysql> select * from product_copy;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
|  1 | 伊利   |    68 |   1 |
|  2 | 蒙牛   |    88 |   1 |
|  6 | tom    |  2788 |   3 |
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+
其中id为主键 num为普通索引
窗口A：
mysql> select * from product_copy where num=3 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+
1 row in set

窗口B：
mysql> insert into product_copy values(5,'kris',1888,2);
这里会等待  直到窗口A commit才会显示下面结果
Query OK, 1 row affected

但是下面是不需要等待的
mysql> update product_copy set price=price+100 where num=1;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0
mysql> insert into product_copy values(5,'kris',1888,5);
Query OK, 1 row affected

通过上面的例子可以看出Gap 锁的作用是在1,3的间隙之间加上了锁。而且并不是锁住了表，我更新num=1，5的数据是可以的.可以看出锁住的范围是（1,3]U[3,4)。

6.2.2 为什么说gap锁是RR隔离级别下防止幻读的主要原因。

快照读：简单的select操作，没有lock in share mode或for update

当前读：官方文档的术语叫locking read，也就是insert，update，delete,select…in share mode和select…for update，当前读会在所有扫描到的索引记录上加锁，不管它后面的where条件到底有没有命中对应的行记录。

​    首先了解到InnoDB索引的数据结构是B+树，其索引是有序性的，（具体原理可以看这篇文章：https://blog.csdn.net/qq_38238296/article/details/88362635 ）如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。注意是当前读。

​    根据索引的有序性，我们可以从上面的例子推断出满足where条件的数据，只能插入在num=（1,3]U[3,4)两个区间里面，只要我们将这两个区间锁住，那么就不会发生幻读。

 

7.2.3. 主键索引/唯一索引+当前读会机上Gap锁吗？

窗口A：
mysql> select * from product_copy where id=6 for update;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  6 | tom  |  2788 |   3 |
+----+------+-------+-----+

窗口B：并不会发生等待
mysql> insert into product_copy values(5,'kris',1888,3);
Query OK, 1 row affected

例子说明的其实就是行锁的原因，我只将id=6的行数据锁住了，用Gap锁的原理来解释的话：因为主键索引和唯一索引的值只有一个，所以满足检索条件的只有一行，故并不会出现幻读，所以并不会加上Gap锁。

7.2.4通过范围查询是否会加上Gap锁

​ 前面的例子都是通过等值查询，下面测试一下范围查询。

窗口A：
mysql> select * from product_copy where num>3 for update;
+----+--------+-------+-----+
| id | name   | price | num |
+----+--------+-------+-----+
| 10 | 优衣库 |   488 |   4 |
+----+--------+-------+-----+

窗口B：会等待
mysql> insert into product_copy values(11,'kris',1888,5);
Query OK, 1 row affected
不会等待
mysql> insert into product_copy values(3,'kris',1888,2);
Query OK, 1 row affected

7.2.5 检索条件并不存在的当前读会加上Gap吗？

1.等值查询

窗口A：
mysql> select * from product_copy where num=5 for update;
Empty set

窗口B：6 和 4都会等待
mysql> insert into product_copy values(11,'kris',1888,6);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

原因一样会锁住（4,5]U[5,n）的区间

2.范围查询

这里就会有点不一样

窗口A：
mysql> select * from product_copy where num>6 for update;
Empty set
窗口B：8 和 4 都会锁住
mysql> insert into product_copy values(11,'kris',1888,4);
Query OK, 1 row affected

mysql> insert into product_copy values(11,'kris',1888,8);
Query OK, 1 row affected

上面的2例子看出当你查询并不存在的数据的时候，mysql会将有可能出现区间全部锁住。

8.死锁的原理及分析

8.1. MVCC

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

8.2. 2PL：Two-Phase Locking

​    传统RDBMS（关系数据库管理系统）加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现

transaction         mysql
begin;                 加锁阶段
insert into           加insert对应的锁
update table      加update对应的锁
delete from        加delete对应的锁
commit              解锁阶段
将insert、update、delete的锁全部解开

上面的例子可以看出2PL就是将加锁、解锁分为两个阶段，并且互相不干扰。加锁阶段只加锁，解锁阶段只解锁。

​    MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。（不过现在一般都是InnoDB引擎，关于MyISAM不做考虑）

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

​    当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。
 

1.两个session的两条语句

​ 这种情况很好理解，首先session1获得 id=1的锁 session2获得id=5的锁，然后session想要获取id=5的锁 等待，session2想要获取id=1的锁 ，也等待！

2.两个session的一条语句

 

​    这种情况需要我们了解数据的索引的检索顺序原理简单说下：普通索引上面保存了主键索引，当我们使用普通索引检索数据时，如果所需的信息不够，那么会继续遍历主键索引。

​    假设默认情况是RR隔离级别，针对session 1 从name索引出发，检索到的是（hdc,1）（hdc,6）不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10] 这个顺序是因为B+树结构的有序性。而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

避免死锁，这里只介绍常见的三种

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
这篇文章关于mysql锁写的很有深度：http://hedengcheng.com/?p=771