一个mysql数据库中非常规死锁的检测和解决

前言

本文假设您有一定的mysql的相关经验，熟悉mysql的innodb存储引擎。并希望您对现代关系型数据库的事务隔离级别有一定的了解。

 

1．问题释出

    我们工作中碰到数据库死锁一般是因为业务逻辑中对业务表访问顺序不一致造成的。但是本文讲述的死锁是由mysql数据库本身的特性造成，所以我们称之为非常规死锁。

1.1环境说明及数据准备

环境说明：

Mysql版本:5.1.34

OS:windows XP SP2

 

环境初始化sql如下

Create DATABASE testdb;

 

CREATE TABLE `test` (

  `a` bigint(20) NOT NULL DEFAULT '0',

  `b` bigint(20) DEFAULT NULL,

  `c` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`a`),

  KEY `b` (`b`,`c`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

insert  into `test`(`a`,`b`,`c`) values (1,2,3),(2,3,4),(3,4,5);

 

初始数据如下：

a	b	c
1	2	3
2	3	4
3	4	5

 

 

1.2试验事务发生场景：

Transaction a	Transaction b
select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+	select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+
Set autocommit=0;	Set autocommit=0;
update test set b=2,c=3 where a=3
	Update test set b=9 where b=2 and c=3; Waiting lock……
update test set b=5,c=6 where a=1 发生死锁

 

 

2．问题检测

用innodb  monitor可以检测到有死锁产生，具体用法是

1、  CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

打开innodb  monitor

2、  Show innodb status

显示检测报告

3、DROP TABLE innodb_monitor;

   关闭innodb  monitor

现在让我们看一下检测报告：

=====================================

090622 10:54:09 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 42 seconds

----------

SEMAPHORES

----------

……

------------------------

LATEST DETECTED DEADLOCK

------------------------

090622 10:54:02

*** (1) TRANSACTION:

TRANSACTION 0 776710, ACTIVE 10 sec, OS thread id 2492 fetching rows

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 320, 3 row lock(s)

MySQL thread id 7, query id 170 localhost 127.0.0.1 root Searching rows for update

Update test set b=9 where b=2 and c=3

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 561 n bits 72 index `b` of table `sc_agt`.`test` trx id 0 776710 lock_mode X waiting

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 8; hex 8000000000000002; asc         ;; 1: len 8; hex 8000000000000003; asc         ;; 2: len 8; hex 8000000000000003; asc         ;;

 

*** (2) TRANSACTION:

TRANSACTION 0 776709, ACTIVE 21 sec, OS thread id 3168 starting index read, thread declared inside InnoDB 500

mysql tables in use 1, locked 1

4 lock struct(s), heap size 320, 3 row lock(s), undo log entries 1

MySQL thread id 6, query id 171 localhost 127.0.0.1 root Updating

update test set b=5,c=6 where a=1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 561 n bits 72 index `b` of table `sc_agt`.`test` trx id 0 776709 lock_mode X locks rec but not gap

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

 0: len 8; hex 8000000000000002; asc         ;; 1: len 8; hex 8000000000000003; asc         ;; 2: len 8; hex 8000000000000003; asc         ;;

 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 559 n bits 72 index `PRIMARY` of table `sc_agt`.`test` trx id 0 776709 lock_mode X locks rec but not gap waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

 0: len 8; hex 8000000000000001; asc         ;; 1: len 6; hex 0000000bd877; asc      w;; 2: len 7; hex 80000011530110; asc     S  ;; 3: len 8; hex 8000000000000002; asc         ;; 4: len 8; hex 8000000000000003; asc         ;;

 

*** WE ROLL BACK TRANSACTION (1)

------------

TRANSACTIONS

------------

Trx id counter 0 776711

Purge done for trx's n:o < 0 776709 undo n:o < 0 0

History list length 17

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0 0, not started, OS thread id 3468

MySQL thread id 10, query id 174 localhost 127.0.0.1 root

show innodb status

---TRANSACTION 0 776710, not started, OS thread id 2492

MySQL thread id 7, query id 170 localhost 127.0.0.1 root

---TRANSACTION 0 776709, ACTIVE 28 sec, OS thread id 3168

4 lock struct(s), heap size 320, 3 row lock(s), undo log entries 2

MySQL thread id 6, query id 171 localhost 127.0.0.1 root

--------

…

END OF INNODB MONITOR OUTPUT

============================

从报告中可以看出Update test set b=9 where b=2 and c=3在等待index `b`上的X锁（独占锁），而事务A的update test set b=5,c=6 where a=1在等待index `PRIMARY`上的X锁（独占锁），持有index `b`上的X锁（独占锁）。可是我们在操作test表的数据为什么会导致索引加锁，进而引起死锁呢？ 

3．问题释因

3.1 mysql的innodb引擎的行锁机制

mysql有多种存储引擎，其中InnoDB是支持事务和行级锁的一种存储引擎，也是目前使用比较多的数据存储引擎，据有很好的并发性能，这次我们试验就是在一个InnoDB的表上进行的，这一点可以从建表语句中的“ENGINE=InnoDB” 看出。不过，InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。这也就是我们会在上面的报告中看到诸如：“RECORD LOCKS space id 0 page no 559 n bits 72 index `PRIMARY` of table `sc_agt`.`test` trx id 0 776709 lock_mode X locks rec but not gap waiting”的提示的原因了。

 

   现在我们重现一下当时场景:

Transaction a

Transaction b

Set autocommit=0;

Set autocommit=0;

update test set b=2,c=3 where a=3

此处mysql会在主键索引的a=3处上加行锁，并且在索引b的b=2,c=3处加上行锁，也就是说原数据：

主键索引	Tran A
1
2
3	Lock

 

索引b		Tran A
b	c
2	3	Lock
3	4
4（2）	5（3）	Lock

 

 

 

Update test set b=9 where b=2 and c=3;

此处mysql会在主键索引的a=1处上加行锁，并且在索引b的b=2,c=3处请求加上行锁，但是发现所已经占用，进入等待

 

 

主键索引	Tran A	Tran B
1		Lock
2
3	Lock

 

索引b		Tran A	Tran B
b	c
2	3	Lock	Lock waiting
3	4
4（2）	5（3）	Lock

update test set b=5,c=6 where a=1

此处mysql会会在主键索引的a=1处上请求加行锁，但是发现所已经占用，进入等待

主键索引	Tran A	Tran B
1	Lock waiting	Lock
2
3	Lock

 

索引b		Tran A	Tran B
b	c
2	3	Lock	管理员在2009年8月13日编辑了该文章文章。 --> 阅读(844) | 评论(0) | 转发(0) | 0 上一篇：正则表达式－资料 下一篇：Lucene倒排索引 给主人留下些什么吧！~~ 评论热议 请登录后评论。 登录 注册 关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册 Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有 感谢所有关心和支持过ChinaUnix的朋友们 16024965号-6