Mysql锁类型和加锁分析及解决方案锁的方法

一、Mysql 锁类型及锁分析1、锁类型介绍：

MySQL 具有三个锁级别：页级、表级和行级。

算法：

所以实际上 Next-=Gap lock + lock

二、死锁的原因和例子1、原因：

所谓死锁：是指两个或多个进程在执行过程中因资源竞争而相互等待的现象。如果没有外力，他们将无法前进。这个时候，据说。系统处于死锁状态或系统发生死锁。这些总是相互等待的进程称为死锁进程。表级锁不会导致死锁。所以解决死锁的方法主要是针对最常用的。

2、生成示例：

案例一

需求：将投资资金分成几份，随机分配给借款人。

一开始，业务流程的想法是这样的：

投资人投资后，将金额随机分成几份，然后从借款人表中随机选择几份，然后通过一个for项更新借款人表中的余额。

比如两个用户同时投资，用户A的金额随机分成2份，分给借款人1、2

用户B的金额随机分成2份，分给借款人2、1

由于锁的顺序不同，死锁当然很快就出现了。

这个问题的改进很简单，直接一次性锁定所有已分配的借款人即可。

Select * from xxx where id in (xx,xx,xx) for update

列表中的mysql in in会自动从小到大排序，锁也是从小到大一一添加

例如（以下会话 ID 是主键）：

：

mysql> select * from t3 where id in (8,9) for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
|  8 | WA     | f    | 2016-03-02 11:36:30 |
|  9 | JX     | f    | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
rows in set (0.04 sec)

：

select * from t3 where id in (10,8,5) for update;

锁等待…

其实这个时候id=10的记录并没有被锁定，但是id=5的记录已经被锁定了。 id=8的等待锁就在这里，不信你看看。

：

mysql> select * from t3 where id=5 for update;

锁等待

：

mysql> select * from t3 where id=10 for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
| 10 | JB     | g    | 2016-03-10 11:45:05 |
+----+--------+------+---------------------+
row in set (0.00 sec)

在其他情况下，id=5 无法锁定，但 id=10 可以锁定。

案例二

在开发中，我们经常会做出这样的判断需求：根据字段值（有索引）查询，如果不存在，则插入；否则，更新。

以id为例，目前还没有id=22的行

：

select * from t3 where id=22 for update;
Empty set (0.00 sec)

：

select * from t3 where id=23  for update;
Empty set (0.00 sec)

：

insert into t3 values(22,'ac','a',now());

锁等待…

：

insert into t3 values(23,'bc','b',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

锁定现有行（主键）时，mysql只有行锁。

当锁定一个不存在的行时（即使条件是主键），mysql也会锁定一个范围（带间隙锁）

锁定范围为：

（无穷小或小于表中 id的最大值，无穷大或大于表中 id的最小值）

例如：如果当前表中有一个id (11, 12）

然后锁定（12，无穷大）

如果当前表中的id是(11, 30）

然后锁定(11, 30）

解决这个死锁的方法是：

insert into t3(xx,xx) on duplicate key update `xx`='XX';

使用mysql特有的语法来解决这个问题。因为语句是针对主键的，所以不管插入的行是否存在，都只会有行锁

案例三

mysql> select * from t3 where id=9 for update;
+----+--------+------+---------------------+
| id | course | name | ctime               |
+----+--------+------+---------------------+
|  9 | JX     | f    | 2016-03-01 11:36:30 |
+----+--------+------+---------------------+
 
row in set (0.00 sec)

：

mysql> select * from t3 where id<20 for update;

锁等待

：

mysql> insert into t3 values(7,'ae','a',now());
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这与案例 1 中的其他情况类似，只是没有按照常识打牌。

等待id=9的锁，持有1到8的锁（注意9到19的范围是没有锁的），最后插入新行的时候还要等待，所以死锁发生。

这种一般不会出现在业务需求中，因为你锁了id=9，但是又想插入id=7的行，有点跳，当然要有解决办法，那就是重组业务需求，避免这样的写作。

案例四

一般情况下，一个 SQL 持有两个锁，然后他们访问彼此的锁定数据导致死锁。

案例 5

两条单sql语句涉及的加锁数据是一样的，只是加锁顺序不同，导致死锁。

死锁场景如下：

表结构：

CREATE TABLE dltask (
    id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘auto id’,
    a varchar(30) NOT NULL COMMENT ‘uniq.a’,
    b varchar(30) NOT NULL COMMENT ‘uniq.b’,
    c varchar(30) NOT NULL COMMENT ‘uniq.c’,
    x varchar(30) NOT NULL COMMENT ‘data’,   
    PRIMARY KEY (id),
    UNIQUE KEY uniq_a_b_c (a, b, c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’deadlock test’;
#a，b，c三列，组合成一个唯一索引，主键索引为id列。

事务隔离级别：

RR (Repeatable Read)

每个事务只有一个 SQL：

delete from dltask where a=? and b=? and c=?;

SQL 执行计划：

死锁日志：

众所周知，删除记录并不是真正意义上的物理删除，而是将记录标记为已删除。 （注：这些标识为已删除状态的记录会被后台Purge操作恢复并物理删除。但是，被删除状态的记录会在索引中保存一段时间。）在RR隔离级别下，唯一索引满足查询，但是是删除记录，如何锁定？这里的处理策略与前两种策略不同，或者是前两种策略的组合：对于满足条件的删除记录，下一个键锁X会加到记录上（给记录本身加X锁，在同时锁定记录前的GAP，防止插入符合条件的新记录。）查询，三种情况，对应三种锁定策略，总结如下：

在这里，我们看到了下一个钥匙锁，你熟悉吗？顺便说一下，前一个死锁中处于等待状态的事务1和事务2的锁都是next key锁。了解了这三种锁策略后，其实就构造了一定的并发场景，死锁的原因就已经很明显了。但是，还有一个前提策略需要引入，那就是内部采用的死锁预防策略。

死锁预防策略

在引擎（或所有数据库）中，有多种锁类型：事务锁（行锁、表锁）、互斥（保护内部共享变量操作）、（也叫Latch，保护内部Page读和修改）。

每页为 16K。读取页面时需要给页面添加S锁，更新页面时需要给页面添加X锁。在任何情况下，操作页面都会锁定页面。添加页面锁后，页面中存储的索引记录不会被并发修改。

那么，为了修改一条记录，内部如何处理：

死锁预防策略：相对于事务锁，页锁是持有时间较短的锁，而事务锁（行锁、表锁）是持有时间较长的锁。因此，为了防止页锁和事务锁之间的死锁。实现了防止死锁的策略：持有事务锁（行锁、表锁），可以等待获取页锁；但相反，持有页锁不能等待持有事务锁。

根据死锁预防策略mysql 共享锁 死锁，在持有页锁和添加行锁时，如果行锁需要等待。释放页锁，然后等待行锁。这时候行锁的获取没有任何锁保护，所以添加行锁后，记录可能已经被并发修改了。因此，此时必须加回页锁，必须重新判断记录的状态，在页锁的保护下重新锁定记录。如果此时没有并发修改记录，第二个锁可以很快完成，因为同模式的锁已经被持有了。但是，如果记录被并发修改，则可能会导致本文前面提到的死锁问题。

上面的死锁预防处理逻辑，对应的函数是.c::()。有兴趣的朋友可以跟踪调试一下这个函数的处理流程，虽然很复杂，但是浓缩了本质。

分析死锁原因

做了这么多伏笔，准备了3种锁逻辑、死锁预防策略等知识后mysql 共享锁 死锁，再来分析本文开头提到的死锁问题。事半功倍。

首先，假设只有一条记录：(1, ‘a’, ‘b’, ‘c’, ‘data’)。三个并发事务，同时执行如下SQL：

delete from dltask where a=’a’ and b=’b’ and c=’c’;

而当产生如下并发执行逻辑时，就会出现死锁：

上面分析的并发进程在死锁日志中充分展示了死锁的原因。实际上，按照事务1的step 6和事务0的step 3/4的顺序，死锁日志中可能会出现另一种情况，即等待事务1的锁模式是X lock + No on the 。间隙锁（X 锁定 rec 但不是 gap ）。这第二种情况也是同学“润杰”给出的死锁用例中使用.6.15进行死锁测试的原因。

这种死锁的几个先决条件：

以上内容希望对大家有所帮助。很多PHPer在进阶的时候总会遇到一些问题和瓶颈。写太多业务代码没有方向感。我不知道从哪里开始改进。整理了一些资料，包括但不限于：分布式架构、高扩展性、高性能、高并发、服务器性能调优、TP6、Redis、Swoft、Kafka、Mysql优化、shell脚本、微服务、Nginx等知识点,进阶进阶干货，可以免费分享给大家，需要点这里PHP进阶架构师>>>实战视频，各大厂商面试资料免费提供

文章来源:https://zhuanlan.zhihu.com/p/267522634

------本页内容已结束，喜欢请分享------

感谢您的来访，获取更多精彩文章请收藏本站。