MySQL数据库的一次死锁实例分析

2024-07-24 12:53:19

字体：大中小

来源：转载

供稿：网友

1、故事起因于2016年11月15日的一个生产bug。业务场景是：归档一个表里边的数据到历史表里边，同是删除主表记录。

2、背景场景简化如下（数据库引擎InnoDb，数据隔离级别RR[REPEATABLE]）

-- 创建表test1 CREATE TABLE test1 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); insert into test1 values('hello'); -- 创建表test2 CREATE TABLE test2 ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ); -- Transcation 1 begin; insert into test2 select * from test1 where id = 1; delete from test1 where id = 1; -- Transcation 2 begin; insert into test2 select * from test1 where id = 1;

3、具体执行顺序

Transcation1	Transcation2
begin; — 这条sql得到test1表主键索引锁共享锁S（id=1） insert into test2 select * from test1 where id = 1;
	begin; — 这条sql试图获取test1表主键索引锁共享锁S（id=1），但是已经被T1占有，所以它进入锁请求队列. insert into test2 select * from test1 where id = 1;
— 这条sql试图把自己拥有的test1表主键索引锁共享锁S(id=1）升级为排它锁X（id=1） — 这时T1也发起一个锁请求，这个时候mysql发现锁请求队列里边已存在一个事物T2对（id=1）的这条记录申请了S锁，死锁产生了。 delete from test1 where id = 1;
	死锁产生后mysql根据两个事务的权重，事务2的权重更小，被选为死锁的牺牲者，rollback。
T2 rollback 之后T1成功获取了锁执行成功

Mysql 官方解释

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDBgenerates an error for one of the clients and releases its locks. The client returns this error。

实际场景和mysql文档有些区别，文档里边要获取的是X锁。具体事例里边要获取的是S锁。

下面我们来具体的一步步分析下mysql的死锁

1、MySQL常用存储引擎的锁机制

　　MyISAM和MEMORY采用表级锁(table-level locking)

　　BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

　　InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁

2、各种锁特点

　　表级锁：开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低

上一篇：解决mysql输入密码闪退的问题

下一篇：利用rpm安装mysql 5.6版本详解