锁是需要事务结束后才释放的。
一个是 MVCC,一个是两阶段锁协议。
为什么要并发控制呢?是因为多个用户同时 *** 作 MySQL 的时候,为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样(为了解决脏读、不可重复读、幻读)
官方定义:
两阶段锁协议是指所有事务必须分两个阶段对数据加锁和解锁,在对任何数据进行读、写 *** 作之前,事务首先要获得对该数据的封锁;在释放一个封锁之后,事务不再申请和获得任何其他封锁。
对应到 MySQL 上分为两个阶段:
但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁(innodb在需要的索引列数据才锁行),并且在加锁阶段没有顺序要求,所以这种并发控制方式会形成死锁。
MySQL有两种死锁处理方式:
死锁检测 (默认开启)
死锁检测的原理是构建一个以事务为顶点、锁为边的有向图,判断有向图是否存在环,存在即有死锁。
回滚
检测到死锁之后,选择插入更新或者删除的行数最少的事务回滚,基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。
收集死锁信息:
减少死锁:
死锁解决:
多线程开启事务处理。每个事务有多个update *** 作和一个insert *** 作(都在同一张表)。
默认隔离级别:Repeatable Read
只有hotel_id=2和hotel_id=11111的数据
逻辑删除原有数据
插入新的数据
根据现有数据情况,update的时候没有数据被更新
报了非常多一样的错
发现居然有死锁。
根据常识考虑,我每个线程(事务)更新的数据都不冲突,为什么会产生死锁?
带着这个问题,打印mysql最近一次的死锁信息
show engine innodb status
显示如下
发现事务1在等待一个锁
事务2也在等待一个锁
而且事物2持有了事物1需要的锁
关于锁的描述,出现了 lock_mode , gap before rec , insert intention 等字眼,看不懂说明了什么?说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。
通过搜索引擎,
锁的持有兼容程度如下表
那么再回到死锁日志,可以知道 :
事务1正在获取插入意向锁
事务2正在获取插入意向锁,持有排他gap锁
再看我们上面的锁兼容表格,可以知道, gap lock和insert intention lock是不兼容的
那么就可以推断出: 事务1持有gap lock,等待事务2的insert intention lock释放;事务2持有gap lock,等待事务1的insert intention lock释放,从而导致死锁。
那么新的问题就来了,事务1的intention lock 为什么会和事务2的gap lock 有交集,或者说,事务1要插入的数据的位置为什么会被事务2给锁住?
让我回顾一下gap lock的定义:
间隙锁,锁定一个范围,但不包括记录本身。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况
那为什么是gap lock,gap lock到底是基于什么逻辑锁的记录?发现自己相关的知识储备还不够。那就开始调查。
调查后发现,当当前索引是一个 普通索引 的时候,会加一个gap lock来防止幻读, 此gap lock 会锁住一个左开右闭的区间。 假设索引为xx_idx(xx_id),数据分布为1,4,6,8,12,当更新xx_id=9的时候,这个时候gap lock的锁定记录区间就是(8,12],也就是锁住了xxid in (9,10,11,12)的数据,当有其他事务要插入xxid in (9,10,11,12)的数据时,就会处于等待获取锁的状态。
ps:当前索引不是普通索引,而且是唯一索引等其他情况,请参考下面资料
MySQL 加锁处理分析
回到我自己的案例中,重新屡一下事务1的执行过程:
因为普通索引
KEY hotel_date_idx ( hotel_id , rate_date )
的关系 这段sql会获取一个gap lock,范围(2,11111]
这段sql会获取一个insert intention lock (waiting)
再看事务2的执行过程
因为普通索引
KEY hotel_date_idx ( hotel_id , rate_date )
的关系 这段sql也会获取一个gap lock,范围也是(2,11111](根据前面的知识,gap lock之间会互相兼容,可以一起持有锁的)
这段sql也会获取一个insert intention lock (waiting)
看到这里,基本也就破案了。因为普通索引的关系,事务1和事务2的gap lock的覆盖范围太广,导致其他事务无法插入数据。
重新梳理一下:
所以从结果来看,一堆事务被回滚,只有10007数据被更新成功
gap lock 导致了并发处理的死锁
在mysql默认的事务隔离级别(repeatable read)下,无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。
共享锁、排他锁、意向共享、意向排他
record lock、gap lock、next key lock、insert intention lock
show engine innodb status
查看MySQL数据库的死锁日志
1. 使用终端或命令提示符登录到MySQL,输入命令:mysql -h xxxx.xxx.xxx -P 3306 -u username -p 解释:xxxx.xxx.xxx是数据库IP地址,username是数据库用户名,输入命令后,会让你输入username对应的密码,就可以登录了
2. 如何查看MySQL数据库的死锁信息 在MySQL客户端下输入命令: show engine innodb status \G
3. 如何定位MySQL数据库的死锁信息 在打印出来的信息中找到“LATEST DETECTED DEADLOCK”一节内容,看图中红线
4. 如何分析日志,定位死锁原因 看3里面的图,紫色划线部分 分析: 事务1,等待 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`,这个位置的X锁 事务2,持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁 事务2,等待这个地方的X锁 理论上这个事务2是可以提交的不会,死锁,但是这个事务日志只打印最后一部分死锁,信息,这里面隐含的条件是,事务1也持有 RECORD LOCKS space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.`score_user`这个地方的S锁,这样,事务2不能加X锁,同时事务1也不能加X锁,产生死锁。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)