加载中…
个人资料
张永强
张永强
  • 博客等级:
  • 博客积分:0
  • 博客访问:228,053
  • 关注人气:16
  • 获赠金笔:0支
  • 赠出金笔:0支
  • 荣誉徽章:
相关博文
推荐博文
谁看过这篇博文
加载中…
正文 字体大小:

MySQL死锁

(2020-01-29 18:49:30)
分类: 软件开发
0、基本概念
(1)MyISAM引擎是表锁,不存在死锁,下面都是基于InnoDB讨论。InnoDB下行锁可细分为记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next_Key Lock)。
(2)共享锁又称为读锁,简称S锁。排他锁又称为写锁,简称X锁。delete的间隙锁属于共享锁。
(3)MySQL会自动处理死锁,一个执行成功,另一个执行失败,执行失败的报错为:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
(4)MySQL事务隔离级别如下[3] 
事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)

 (5)本地事务通过 ACID 保证数据的强一致性[4] 。ACID 是 Atomic(原子性)、Consistency(一致性)、 Isolation(隔离性)和 Durability(持久性)的缩写 。其中,原子性,即事务内的操作要么全部成功,要么全部失败,不会在中间的某个环节结束。一致性,即使数据库在一个事务执行之前和执行之后,数据库都必须处于一致性状态。如果事务执行失败,那么需要自动回滚到原始状态,换句话说,事务一旦提交,其他事务查看到的结果一致,事务一旦回滚,其他事务也只能看到回滚前的状态。隔离性,即在并发环境中,不同的事务同时修改相同的数据时,一个未完成事务不会影响另外一个未完成事务。持久性,即事务一旦提交,其修改的数据将永久保存到数据库中,其改变是永久性的。

  • 原子性:使用 undo log,从而达到回滚;
  • 持久性:使用 redo log,从而达到故障后恢复;
  • 隔离性:使用 读写锁+MVCC;
  • 一致性:是通过原子性、持久性、隔离性来实现的。原子性、持久性、隔离性折腾半天的目的也是为了保障数据的一致性!
一、初始化表数据
CREATE TABLE `gap_lock` (
    `id` BIGINT ( 20 ) NOT NULL,
    `number` INT ( 11 ) NOT NULL,
    PRIMARY KEY ( `id` ),
    KEY `idx_number` ( `number` )
);
INSERT INTO `gap_lock`(`id`, `number`) VALUES (1, 1),(10, 10),(100, 100);

MySQL死锁

二、设置自动提交参数
mysql> show variables like 'autocommit';  -- show variables like "tx_isolation";  -- SELECT @@tx_isolation;
mysql> set autocommit = 0;
......    测试场景的SQL    ......
mysql> commit;
mysql> rollback;

三、测试场景
基于RR事务隔离级别,间隙锁只有在 RR 中才会产生,RC间隙锁将会失效。
示例1-普通索引的间隙锁,死锁
事务A:delete from `gap_lock` where number = 2;
事务B:delete from `gap_lock` where number = 3;
事务A:insert into `gap_lock` values(5,5);
事务B:insert into `gap_lock` values(6,6);

示例2-普通索引的临建锁,死锁
事务A:delete from `gap_lock` where number = 1;
事务B:delete from `gap_lock` where number = 10;
事务A:insert into `gap_lock` values(5,5);
事务B:insert into `gap_lock` values(6,6);

示例3-主键(唯一索引)的间隙锁,死锁
事务A:delete from `gap_lock` where id = 2;
事务B:delete from `gap_lock` where id = 3;
事务A:insert into `gap_lock` values(5,5);
事务B:insert into `gap_lock` values(6,6);

删除number字段后,只留主键字段
示例4-主键(唯一索引)的间隙锁,死锁
事务A:delete from `gap_lock` where id = 2;
事务B:delete from `gap_lock` where id = 3;
事务A:insert into `gap_lock` values(5);
事务B:insert into `gap_lock` values(6);

示例5-主键(唯一索引)不存在临键锁,不死锁
事务A:delete from `gap_lock` where id = 1;
事务B:delete from `gap_lock` where id = 10;
事务A:insert into `gap_lock` values(5);
事务B:insert into `gap_lock` values(6);

示例6-主键(唯一索引)的记录锁,死锁
事务A:UPDATE `gap_lock` SET `number` = 2 WHERE `id` = 1; 
事务B:UPDATE `gap_lock` SET `number` = 2 WHERE `id` = 10; 
事务A:UPDATE `gap_lock` SET `number` = 2 WHERE `id` = 10;
事务B:UPDATE `gap_lock` SET `number` = 2 WHERE `id` = 1;

示例7-不同索引,死锁(MySQL的行锁是针对索引加的锁,不是针对记录加的锁)
事务A:select * from `gap_lock` where number =5 for update;
事务B:select * from `gap_lock` where id =10 for update;
事务A:delete from `gap_lock` where number =10;
事务B:delete from `gap_lock` where id =10;

四、总结
1、如果没有索引,那么是表锁,不会发生死锁;
2、主键索引(唯一索引)有数据是记录锁,没有数据是间隙锁;
3、普通索引有数据是临键锁,没有数据是间隙锁;
4、在MySQL中select称为快照读 (snapshot read),不需要锁,而insert、update、delete、select for update则称为当前读(current read),需要给数据加锁,幻读中的“读”即是针对当前读。RR事务隔离级别允许存在幻读,但InnoDB RR级别却通过Gap锁避免了幻读。
5、幻读:修改update的结果,被select用“当前读”看到,不能称为幻读。幻读仅专指新插入的行。行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB只好引入间隙锁。

五、参考文献
[1] https://blog.csdn.net/weixin_34006468/article/details/88039873
[2] https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961535&idx=1&sn=b62e9d71836ac5cf2d3cedf69e1ef395&chksm=bd2d0d638a5a84750adfc39d7e177a63330d6bde0f56600764b2d79e0fb9d96ad69e26e19ff1&scene=0&key=5439c4edd659b24500c881258718071b5496feefdd3f684b7b060d36a921f3da4d3672c54fe6f205f20cb3d726ed86b0e6ecba06a6bdac262023155e03994049d3ee706e7cbf086141d3597924d215d1&ascene=14&uin=MjQzNjA2ODAyMA==&devicetype=Windows+10&version=6208006f&lang=zh_CN&exportkey=A/VBw1wviiEq38ufKP6LhTQ=&pass_ticket=b72xYC18sPOMtsho9tYpNbxjrmkAJ9B9hMQzA3R4gc/DZ3BPrlHXfzkNAgWUzoqR
[3] https://www.cnblogs.com/wyaokai/p/10921323.html
[4] https://mp.weixin.qq.com/s?__biz=MjM5MDE0Mjc4MA==&mid=2651023844&idx=1&sn=444cd999345af249f9c61d5d6c79d464&chksm=bdbe91b78ac918a1b775de0b4899ff6f7bd8e3c1f654c999b8070e366bf2a5a1beb1bb5b91a1&mpshare=1&scene=24&srcid=&sharer_sharetime=1579677032582&sharer_shareid=a81d2741c0930789e27f292e2e2fa9b4&key=c9aa73ab86bb25d0c8b0bb5533bacc172af7c5fae5a41a73b481c29eb2bf8c7bb4e3263edd8e6ddd8147ba6775b93339b9e41f775285d2420300b1c372bc3da2cb056446f7c7b10071341b3878744e15&ascene=14&uin=MjQzNjA2ODAyMA==&devicetype=Windows+10&version=6208006f&lang=zh_CN&exportkey=Awq1Xh1sPJUWaHUsOrcT2Bo=&pass_ticket=ZkkmexLVwHEHUh5RT7Ns1UPs3/jy0/YxnRyaGEeOS7etp/kbrKQq1FZ5LxgoiQis

0

阅读 评论 收藏 转载 喜欢 打印举报/Report
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4000520066 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有