MySQL死锁检查处理的正常方法

正常情况下，死锁发生时，权重最小的连接将被kill并回滚。但是为了找出语句来优化，启用可启用死锁将死锁信息记录下来。

#step 1：窗口一mysql> start transaction;mysql> update aa set name='aaa' where id = 1; #step 2：窗口二mysql> start transaction;mysql> update bb set name='bbb' where id = 1; #step 3：窗口一mysql> update bb set name='bbb';

#查看最近一个死锁情况mysql> SHOW ENGINE INNODB STATUS\G ;...............------------------------LATEST DETECTED DEADLOCK------------------------2018-03-12 11:01:06 7ffb4993a700 #发生时间*** (1) TRANSACTION: #事务1TRANSACTION 130718515, ACTIVE 19 sec starting index readmysql tables in use 1, locked 1 #正被访问的表LOCK WAIT 4 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1 #影响行数MySQL thread id 5, OS thread handle 0x7ffb498f8700, query id 205 localhost root updating #线程/连接host/用户update bb set name='bb' #请求语句*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #等待以下资源 （锁定位置及锁模式）RECORD LOCKS space id 65 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`bb` trx id 130718515 lock_mode X waitingRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000300; asc  ;; 1: len 6; hex 000007ca9b34; asc  4;; 2: len 7; hex 1f000002092075; asc  u;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6262; asc bb;; *** (2) TRANSACTION: #事务2TRANSACTION 130718516, ACTIVE 14 sec starting index readmysql tables in use 1, locked 14 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 0x7ffb4993a700, query id 206 localhost root updatingupdate aa set name='aa' #请求语句*** (2) HOLDS THE LOCK(S): #持有锁资源RECORD LOCKS space id 65 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`bb` trx id 130718516 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000301; asc  ;; 1: len 6; hex 000007ca9b17; asc  ;; 2: len 7; hex 9000000144011e; asc  D ;; 3: len 4; hex 80000002; asc  ;; 4: len 2; hex 6262; asc bb;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000300; asc  ;; 1: len 6; hex 000007ca9b34; asc  4;; 2: len 7; hex 1f000002092075; asc  u;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6262; asc bb;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 64 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`aa` trx id 130718516 lock_mode X waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000200; asc  ;; 1: len 6; hex 000007ca9b33; asc  3;; 2: len 7; hex 1e000001d53057; asc  0W;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6161; asc aa;; *** WE ROLL BACK TRANSACTION (2)...............

[mysqld]log-error =/var/log/mysqld3306.loginnodb_lock_wait_timeout=60  #锁请求超时时间(秒)innodb_rollback_on_timeout = 1 #事务中某个语句锁请求超时将回滚真个事务innodb_print_all_deadlocks = 1 #死锁都保存到错误日志

更多相关文章

随机推荐