MySQL 死锁分析

1.MySQL锁和死锁的理解：

MyISAM和MEMORY存储引擎采用的是表级锁table-level locking
BDB存储引擎采用的是页面锁page-level locking,但也支持表级锁
InnoDB存储引擎既支持行级锁row-level locking,也支持表级锁,但默认情况下是采用行级锁
 
表级锁 开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
行级锁 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
页面锁 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般
 
仅从锁的角度来说:
表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用
行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理系统
 
死锁
所谓死锁<DeadLock>: 是指两个或两个以上的进程在执行过程中,
因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程.
 
表级锁不会产生死锁.所以解决死锁主要还是真对于最常用的InnoDB.
 
在遇到问题时
先执行show processlist找到死锁线程号.然后Kill　processNo
 
当然主要解决还是需要去看一下具体的操作.可能产生死锁
Show innodb status检查引擎状态 ,可以看到哪些语句产生死锁
 
然后就是解决了.
怎么解决还是要看具体什么问题.

 

2.参考文档：

存储引擎和表类型

http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html

 

 

3.实例（转）

下面是mysql中出现的死锁情况，其中的中文是我加的注释

mysql> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
060728 13:08:24 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 838, signal count 831
Mutex spin waits 1131, rounds 8687, OS waits 49
RW-shared spins 1529, OS waits 753; RW-excl spins 51, OS waits 33
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060728 13:00:39 Error in foreign key constraint creation for table `EACORPTEST/#sql-2564_1d95`.
A foreign key constraint of name `EACORPTEST/FK_LISTADDR_EACUSTOMERCORPID`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------------------
LATEST DETECTED DEADLOCK
------------------------
060728 9:55:22
*** (1) TRANSACTION:--第一个事务
TRANSACTION 0 44350172, ACTIVE 3472 sec, process no 9572, OS thread id 122063792 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1024, undo log entries 15
MySQL thread id 4758, query id 215216 172.20.16.197 EATEST Updating
UPDATE MAILREGINFOS SET MODIFIER = ?, MODIFIED = ?, STATUS = ? WHERE MAILREGINFOS.CORPID = ? AND MAILREGINFOS.PRODUCTIID = ? --该事务执行的SQL

AND MAILREGINFOS.MODIFIED = ?
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:--第一个事务在等待下面的这个锁
RECORD LOCKS space id 0 page no 8393 n bits 88 index `PRIMARY` of table `MAILTEST197/MAILREGINFOS` trx id 0 44350172 --可以看到其中的表名和数据库名

lock_mode X locks rec but not gap waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 19; compact format; info bits 0--记录锁，下面是该行记录的各列的值，有长度、文本值、十六进制值
0: len 11; hex 3130303030303030323541; asc 1000000025A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce04c5; asc ;; 4: len 8; hex 8000123ec0928589; asc > ;; 5:

len 5; hex 61646d696e; asc admin;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10:

len 30; hex 313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated);

11: len 4; hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 7fffffff; asc ;; 14: len 4; hex

80000014; asc ;; 15: len 1; hex 31; asc 1;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 1; hex 30; asc 0;;

18: len 4; hex 7fffffff; asc ;;

*** (2) TRANSACTION:--第二个事务
TRANSACTION 0 44349997, ACTIVE 3489 sec, process no 9572, OS thread id 85244848 starting index read
mysql tables in use 1, locked 1
12 lock struct(s), heap size 1024, undo log entries 20
MySQL thread id 4729, query id 215345 172.20.16.197 EATEST Updating
UPDATE MAILREGINFOS SET MODIFIER = ?, MODIFIED = ?, STATUS = ? WHERE MAILREGINFOS.CORPID = ? AND MAILREGINFOS.PRODUCTIID = ? --事务当前的SQL

AND MAILREGINFOS.MODIFIED = ?
*** (2) HOLDS THE LOCK(S):--第二个事务占据了下面的锁
RECORD LOCKS space id 0 page no 8393 n bits 88 index `PRIMARY` of table `MAILTEST197/MAILREGINFOS` trx id 0 44349997 --记录锁，可以看到数据库名、表名

lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 19; compact format; info bits 0--行(纪录)锁，下面是该行记录的各列值
0: len 11; hex 3130303030303030313041; asc 1000000010A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce0382; asc ;; 4: len 8; hex 8000123ec0925f9d; asc > _ ;; 5:

len 5; hex 61646d696e; asc admin;; 6: len 8; hex 80001232f6f64740; asc 2 G@;; 7: len 8; hex 80001240ef4138f7; asc @ A8

;; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10: len 30; hex

313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated); 11: len 4;

hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 8000000f; asc ;; 14: len 4; hex 8000000f; asc

;; 15: len 1; hex 30; asc 0;; 16: len 6; hex 6c696e676d69; asc lingmi;; 17: len 1; hex 30; asc 0;; 18: len 4; hex

8000001e; asc ;;

Record lock, heap no 17 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 11; hex 3130303030303030323241; asc 1000000022A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce0511; asc ;; 4: len 8; hex 8000123ec092858c; asc > ;; 5:

len 5; hex 61646d696e; asc admin;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10:

len 30; hex 313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated);

11: len 4; hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 80000005; asc ;; 14: len 4; hex

80000014; asc ;; 15: len 1; hex 30; asc 0;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 4; hex 31303030; asc

1000;; 18: len 4; hex 8000000a; asc ;;

Record lock, heap no 19 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 11; hex 3130303030303030323541; asc 1000000025A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce04c5; asc ;; 4: len 8; hex 8000123ec0928589; asc > ;; 5:

len 5; hex 61646d696e; asc admin;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10:

len 30; hex 313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated);

11: len 4; hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 7fffffff; asc ;; 14: len 4; hex

80000014; asc ;; 15: len 1; hex 31; asc 1;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 1; hex 30; asc 0;;

18: len 4; hex 7fffffff; asc ;;

Record lock, heap no 20 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 11; hex 3130303030303030333041; asc 1000000030A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce03ce; asc ;; 4: len 8; hex 8000123ec0926583; asc > e ;; 5:

len 5; hex 61646d696e; asc admin;; 6: len 8; hex 8000123c4725d740; asc <G% @;; 7: len 8; hex 8000123e9b3554f7; asc > 5T

;; 8: len 1; hex 32; asc 2;; 9: len 4; hex 8000000a; asc ;; 10: len 30; hex

313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated); 11: len 4;

hex 80000000; asc ;; 12: len 4; hex 800000c8; asc ;; 13: len 4; hex 80000005; asc ;; 14: len 4; hex 80000014; asc

;; 15: len 1; hex 30; asc 0;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 1; hex 30; asc 0;; 18: len 4; hex

8000000a; asc ;;

Record lock, heap no 21 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 11; hex 3130303030303030323041; asc 1000000020A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4ba2d; asc -;; 3: len 7; hex 00000020ce02e4; asc ;; 4: len 8; hex 8000123ec0925f89; asc > _ ;; 5:

len 5; hex 61646d696e; asc admin;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10:

len 30; hex 313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated);

11: len 4; hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 80000005; asc ;; 14: len 4; hex

80000014; asc ;; 15: len 1; hex 30; asc 0;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 1; hex 30; asc 0;;

18: len 4; hex 8000001e; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:--第二个事务在等待这个锁
RECORD LOCKS space id 0 page no 8393 n bits 88 index `PRIMARY` of table `MAILTEST197/MAILREGINFOS` trx id 0 44349997

lock_mode X locks rec but not gap waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 11; hex 3130303030303030323141; asc 1000000021A;; 1: len 30; hex

386662653433636633636539313164616530376530303130613465376164; asc 8fbe43cf3ce911dae07e0010a4e7ad;...(truncated); 2: len 6;

hex 000002a4badc; asc ;; 3: len 7; hex 00000020d71af4; asc ;; 4: len 8; hex 8000123ec0926f31; asc > o1;; 5:

len 5; hex 61646d696e; asc admin;; 6: SQL NULL; 7: SQL NULL; 8: len 1; hex 32; asc 2;; 9: len 4; hex 80000000; asc ;; 10:

len 30; hex 313131313131313030303131313030303030303030303030303030303030; asc 111111100011100000000000000000;...(truncated);

11: len 4; hex 80000000; asc ;; 12: len 4; hex 7fffffff; asc ;; 13: len 4; hex 7fffffff; asc ;; 14: len 4; hex

80000014; asc ;; 15: len 1; hex 30; asc 0;; 16: len 8; hex 6c696e677169616f; asc lingqiao;; 17: len 1; hex 30; asc 0;;

18: len 4; hex 7fffffff; asc ;;

*** WE ROLL BACK TRANSACTION (1)--我们(mysqld)回滚了第一个事务(因为死锁)
....
....
....