- 浏览: 246374 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
nodonkey:
貌似还是不行,再等等吧,amfphp要出2.0了
amfphp1.9与php5.3.X版本不兼容 -
live711:
请问amfphp与php5.3.X搭配能用了吗?
amfphp1.9与php5.3.X版本不兼容 -
zhousheng193:
非常感谢!
安装flash player debug版本遇到的一些问题 -
sp42:
谢谢提示,我遇到的也是不能加密,用MAC地址代替之。
DI-624+A路由器韧体升级解决经常掉线的问题(转) -
心似海:
不错,要挖去了,哈哈
深入sql之merge into
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)回滚了第一个事务(因为死锁)
....
....
....
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)回滚了第一个事务(因为死锁)
....
....
....
发表评论
-
alter table move 与shrink space的区别
2012-03-06 13:51 2244转自:http://hi.baidu.co ... -
mysqlsla来分析MYSQL的性能及索引
2011-01-17 19:56 1321— Slow log: mysqlsla -lt slow ... -
六款常用mysql slow log分析工具的比较
2011-01-17 19:06 1281转自:http://www.iteye.com/topi ... -
MySQL的大小写敏感性
2011-01-12 14:05 1057转自: http://www.zeali.net/ent ... -
如何查看mysql的版本
2010-05-22 11:52 22037如果我们想要查看mysql数据库的版本有以下四种方法: ... -
MySQL 数据库的备份和恢复
2010-03-19 13:44 927转自 忧里修斯 http://tec ... -
mysql使用show命令以及replace函数批量修改数据
2010-03-19 13:37 2415一.mysql的show命令 a. show tables或 ... -
MySQL中的ROWNUM的实现
2010-01-26 13:43 2206本文转自 http://blog.csdn.net/ACMA ... -
一个MySQL死锁问题的分析及解决
2010-01-20 12:50 1261转自http://java-guru.iteye.com/bl ... -
sql 按指定顺序排序
2010-01-19 10:53 21831、在ORACLE中使用Decode Decode实 ... -
MYSQL 事务管理
2009-10-26 19:48 1064mysql_query("BEGIN"); ... -
delete 符合条件的记录中的前几条或者重复记录
2009-09-03 20:04 2073今天写代码,遇到了这个问题,只能删除符合条件的记录中的某几条. ... -
sql update delete 中 使用 inner join
2009-08-24 11:38 7200SQL中使用update inner join和delet ... -
What is the difference between VARCHAR, VARCHAR2 ?
2009-06-01 09:43 934Both CHAR and VARCHAR2 types ar ... -
Oracle index
2009-05-15 10:50 0索引是一种可以提高查 ... -
Views and Materialized Views 整理
2009-04-10 14:29 1147Views and Mat ... -
IN and EXISTS, NOT IN AND NOT EXISTS
2009-04-10 14:28 1443Functionally, they are the same ... -
ORACLE 之 TRUNCATE TABLE
2009-03-30 16:49 1986TRUNCATE Caution: Y ... -
深入sql之merge into
2009-01-08 16:38 4783转自 逆水流沙 http://hi.baidu.com/wen ... -
Oracle日期函数操作(收集整理版)
2008-12-04 16:50 2799经常在平时的开发中要用到oracle的日期函数,每次都要上 ...
相关推荐
最后,总结一下,死锁的分析和解决是一个综合性的任务,需要结合MySQL的事务处理、锁机制以及并发控制等多方面的知识。通过深入理解和解析死锁日志,我们可以更好地诊断和预防此类问题,从而提高数据库的性能和稳定...
### MySQL死锁分析 #### 死锁问题背景 在MySQL的使用过程中,死锁是一个较为常见的现象,尤其是在并发量较大的应用场景下。死锁的发生往往会给系统带来不可预知的影响,严重时甚至会导致整个数据库服务不可用。...
在MySQL数据库中,死锁是一种常见的并发问题,当两个或多个事务互相等待对方释放资源时,就会发生死锁。本案例中的死锁发生在“pop购药”系统的订单支付状态更新操作中,涉及到了两个事务,我们分别称之为Session1和...
MYSQL 死锁检测机制初探 在 MySQL 中,死锁检测机制是一种重要的机制,用于检测和解决事务之间的死锁问题。在本文中,我们将详细介绍 MySQL 死锁检测机制的原理和实现。 一、死锁的定义和原理 在 MySQL 中,死锁...
这篇博客文章《mysql死锁的一些案例》可能深入探讨了MySQL中死锁的产生原因、表现形式以及解决策略。虽然具体内容未给出,但我们可以根据通常的死锁情况来进行分析。 1. **死锁产生的原因**: - 资源请求顺序不同...
通过何登成的分享,我们可以看到,死锁分析需要深入理解事务逻辑、掌握锁的原理以及合理配置数据库和应用,从而确保数据库系统的稳定和高效。这些知识点不仅是DBA需要掌握的,对于任何涉及数据库设计和优化的开发者...
MySQL死锁问题是数据库管理员和开发者在工作中经常遇到的一种并发问题,尤其在面试中也常作为考核候选人的一个知识点。本文将以一个具体的死锁案例为背景,深入分析MySQL中的死锁机制,探讨死锁的成因,并提出预防...
MySQL 死锁案例详解 在 MySQL 中,死锁是指两个或两个以上的进程...在解决死锁问题时,需要根据具体的业务场景和锁的级别来进行分析和解决。 MySQL 死锁的解决方案是让不同的 Session 加锁有次序,以避免死锁的出现。
MySQL中的死锁问题是一个复杂而微妙的议题,尤其是在高并发的业务环境中,死锁可能导致服务的不稳定甚至数据的不一致。在2016年11月15日的这个生产bug中,我们看到的问题核心在于两个事务间的资源争夺,最终导致了...
MySQL中的死锁问题是一个常见的事务处理挑战,尤其是在...总之,理解MySQL的死锁现象、其日志分析以及如何预防是保证数据库高效、稳定运行的关键。通过细致的分析和良好的编程实践,我们可以有效地管理并避免死锁问题。
这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类汇总,试图通过死锁日志分析出每种死锁的原因,还原出死锁现场。 实际上,我们在定位死锁问题时,不仅应该对死锁日志进行分析,...
这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类汇总,试图通过死锁日志分析出每种死锁的原因,还原出死锁现场。 实际上,我们在定位死锁问题时,不仅应该对死锁日志进行分析,还...
管中窥豹——MySQL(InnoDB)死锁分析之道 阿里巴巴高级数据库专家
本文将探讨MySQL死锁的产生原因以及解决方案。 **死锁的产生原因** 1. **资源竞争与顺序依赖**:当两个事务A和B分别持有对方需要的资源,A等待B释放资源,B也在等待A释放资源,就会形成死锁。例如,事务A锁定表A后...
这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类汇总,试图通过死锁日志分析出每种死锁的原因,还原出死锁现场。 实际上,我们在定位死锁问题时,不仅应该对死锁日志进行分析,...
### 死锁分析 为了有效解决死锁,需要对死锁情况进行分析。从示例中可以看到,我们可以通过创建测试场景来模拟死锁,观察事务执行的顺序和加锁行为。 1. 准备工作:创建一个测试表,例如hero表,并插入测试数据。 2...