银川迅雷网站建设,移除wordpress上边栏,酷炫的网站,企业网站建设 英铭14.5.5.1 An InnoDB Deadlock Example 一个InnoDB 死锁实例下面的例子演示了一个错误可以发生当一个lock 请求会导致一个死锁,例子设计2个客户端#xff0c;A和B:Jekins:/root# mysql -uroot -p1234567 -eSHOW ENGINE INNODB STATUS\G; | grep -i dead
Warning: … 14.5.5.1 An InnoDB Deadlock Example 一个InnoDB 死锁实例下面的例子演示了一个错误可以发生当一个lock 请求会导致一个死锁,例子设计2个客户端A和B:Jekins:/root# mysql -uroot -p1234567 -eSHOW ENGINE INNODB STATUS\G; | grep -i dead
Warning: Using a password on the command line interface can be insecure.首先, client A 创建一个表包含一个记录, 然后开始一个事务,在事务中,通过selecting在共享模式得到一个S锁mysql CREATE TABLE t (i INT) ENGINE InnoDB;
Query OK, 0 rows affected (1.07 sec)mysql INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql SELECT * FROM t WHERE i 1 LOCK IN SHARE MODE;
------
| i |
------
| 1 |
------接着,Client B 开始一个事务和尝试删除记录mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)mysql DELETE FROM t WHERE i 1;删除操作需要一个X锁,lock 不能被立即授权 因为它不兼容S锁 客户端A持有的,最后,Client A 尝试删除表的记录mysql DELETE FROM t WHERE i 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transactionDeadlock 发生 因为client A 需要一个X锁来删除记录。然而,lock 请求不能被立即授予因为Client B有一个请求X锁 在等待客户端A 来释放他的S锁。S锁被client A持有的也不能升级为X锁 。这样,InnoDB 产生一个错误------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-10-31 10:06:09 7f0341d30700
*** (1) TRANSACTION:
TRANSACTION 15945, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 5, OS thread handle 0x7f0341ae9700, query id 368 localhost root updating
DELETE FROM t WHERE i 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 583 page no 3 n bits 72 index GEN_CLUST_INDEX of table zjzc.t trx id 15945 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 6; hex 000000005c00; asc \ ;;1: len 6; hex 000000003e47; asc G;;2: len 7; hex c7000001be0110; asc ;;3: len 4; hex 80000001; asc ;;*** (2) TRANSACTION:
TRANSACTION 15944, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 4, OS thread handle 0x7f0341d30700, query id 369 localhost root updating
DELETE FROM t WHERE i 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 583 page no 3 n bits 72 index GEN_CLUST_INDEX of table zjzc.t trx id 15944 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 6; hex 000000005c00; asc \ ;;1: len 6; hex 000000003e47; asc G;;2: len 7; hex c7000001be0110; asc ;;3: len 4; hex 80000001; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 583 page no 3 n bits 72 index GEN_CLUST_INDEX of table zjzc.t trx id 15944 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 00: len 6; hex 000000005c00; asc \ ;;1: len 6; hex 000000003e47; asc G;;2: len 7; hex c7000001be0110; asc ;;3: len 4; hex 80000001; asc ;;*** WE ROLL BACK TRANSACTION (1)转载于:https://www.cnblogs.com/zhaoyangjian724/p/6199043.html
