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)

图片[1]-Mysql锁类型和加锁分析及解决方案锁的方法-4747i站长资讯

然后锁定(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 持有两个锁,然后他们访问彼此的锁定数据导致死锁。

图片[2]-Mysql锁类型和加锁分析及解决方案锁的方法-4747i站长资讯

案例 5

图片[3]-Mysql锁类型和加锁分析及解决方案锁的方法-4747i站长资讯

两条单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 执行计划:

死锁日志:

图片[4]-Mysql锁类型和加锁分析及解决方案锁的方法-4747i站长资讯

众所周知,删除记录并不是真正意义上的物理删除,而是将记录标记为已删除。 (注:这些标识为已删除状态的记录会被后台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’;

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

图片[5]-Mysql锁类型和加锁分析及解决方案锁的方法-4747i站长资讯

上面分析的并发进程在死锁日志中充分展示了死锁的原因。实际上,按照事务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

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

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

© 版权声明
THE END
喜欢就支持一下吧
点赞6 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容