博客访问： 3430290
博文数量： 1037
博客积分： 12070
博客等级： 上将
技术积分： 11442
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Mysql/postgreSQL
innodb大量AUTO-INC
LOCK 导致的DeadLOCK
graph of transactions */
graph of transactions */
LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK
return(LOCK_EXCEED_MAX_DEPTH);
3.2 innodb_autoinc_lock_mode选项
3.2.1 innodb_autoinc_lock_mode = 0
(“traditional” lock mode)
3.2.2 innodb_autoinc_lock_mode = 1
(“consecutive” lock mode)
3.2.3 innodb_autoinc_lock_mode = 2
(“interleaved” lock mode)
阅读(7171) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。关键词： innodb 死锁 索引 binlog
&
前言
本文假设您有一定的的相关经验，熟悉mysql的innodb存储引擎。并希望您对现代关系型的事务隔离级别有一定的了解。
&
1．问题释出
&&& 我们工作中碰到死锁一般是因为业务逻辑中对业务表访问顺序不一致造成的。但是本文讲述的死锁是由mysql数据库本身的特性造成，所以我们称之为非常规死锁。
1.1环境说明及数据准备
环境说明：
Mysql版本:<st1:chsdate w:st="on" IsROCDate="False" IsLunarDate="False" Day="30" Month="12" Year=".34
OS:windows XP SP2
&
环境初始化sql如下
Create DATABASE
&
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=
insert& into `test`(`a`,`b`,`c`) values (1,2,3),(2,3,4),(3,4,5);
&
初始数据如下：
5
&
&
1.2试验事务发生场景：
Transaction a
Transaction b
select @@tx_
+-----------------+
| @@tx_isolation& |
+-----------------+
| REPEATABLE-READ |
+-----------------+
select @@tx_
+-----------------+
| @@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_
&& 关闭innodb& monitor
现在让我们看一下检测报告：
=====================================
:54:09 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
----------
SEMAPHORES
----------
……
------------------------
LATEST DETECTED DEADLOCK
------------------------
: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; info bits 0
&0: len 8; hex 0002; asc&&&&&&&& ;; 1: len 8; hex 0003; asc&&&&&&&& ;; 2: len 8; hex 0003; 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; info bits 0
&0: len 8; hex 0002; asc&&&&&&&& ;; 1: len 8; hex 0003; asc&&&&&&&& ;; 2: len 8; hex 0003; 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; info bits 0
&0: len 8; hex 0001; asc&&&&&&&& ;; 1: len 6; hex 7; asc&&&&&; 2: len 7; hex 10; asc&&&& S& ;; 3: len 8; hex 0002; asc&&&&&&&& ;; 4: len 8; hex 0003; 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表的数据为什么会导致索引加锁，进而引起死锁呢？&
相关资料：｜｜｜｜｜｜｜一个mysql数据库中非常规死锁的检测和解决1来源网络，如有侵权请告知，即处理！编程Tags：　　　　　　　　　　　　　　　　&　　　　　　　　　　　　　　　　　　　　}