mysql innodb 死結分析

標籤：死結分析   mysql   innodb   

mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper #mysql版本 5.7.16Connection id: 10042Current database: china9129Current user: [email protected]SSL: Not in useCurrent pager: stdoutUsing outfile: ‘‘Using delimiter: ;Server version: 5.7.16-log MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /data/mysql/mysql9129/sock/mysql9129.sockUptime: 4 days 6 hours 2 min 31 secThreads: 4 Questions: 31133 Slow queries: 0 Opens: 254 Flush tables: 3 Open tables: 58 Queries per second avg: 0.084SESSION 1:"[email protected]:mysql9129.sock [(none)]>set global TRANSACTION ISOLATION LEVEL REPEATABLE READ;"[email protected]:mysql9129.sock [(none)]>select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)"[email protected]:mysql9129.sock [china9129]>show create table t100;+-------+----------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+----------------------------------------------------------------------------------------------------------+| t100 | CREATE TABLE `t100` (  `id` int(11) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)"[email protected]:mysql9129.sock [china9129]>select * from t100;+-----+| id |+-----+| 1 || 2 || 3 || 4 || 5 || 111 |+-----+"[email protected]:mysql9129.sock [china9129]>begin;Query OK, 0 rows affected (0.00 sec)"[email protected]:mysql9129.sock [china9129]>select * from t100 where id=5 for update;+----+| id |+----+| 5 |+----+1 row in set (0.00 sec)"[email protected]:mysql9129.sock [china9129]>delete from t100 where id =3;Query OK, 1 row affected (3.62 sec)==========================================================================================================SESSION 2:"[email protected]:mysql9129.sock [(none)]>begin;Query OK, 0 rows affected (0.00 sec)"[email protected]:mysql9129.sock [(none)]>use china9129;Database changed"[email protected]:mysql9129.sock [china9129]>select * from t100 where id=3 for update;+----+| id |+----+| 3 |+----+1 row in set (0.00 sec)"[email protected]:mysql9129.sock [china9129]>delete from t100 where id =5;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction#查看死結資訊"[email protected]:mysql9129.sock [china9129]>show engine innodb status \G*************************** 1. row ***************************  Type: InnoDB  Name: Status: =====================================2016-12-20 21:57:45 0x7fa0bffff700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 24 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 1133 srv_active, 0 srv_shutdown, 362529 srv_idlesrv_master_thread log flush and writes: 363662----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 4540OS WAIT ARRAY INFO: signal count 5766RW-shared spins 0, rounds 4683, OS waits 474RW-excl spins 0, rounds 134731, OS waits 1327RW-sx spins 6332, rounds 138603, OS waits 2382Spin rounds per wait: 4683.00 RW-shared, 134731.00 RW-excl, 21.89 RW-sx------------------------LATEST DETECTED DEADLOCK #監測出最近的死結資訊------------------------2016-12-20 21:56:29 0x7fa0bffff700*** (1) TRANSACTION: #第一個事物TRANSACTION 13899, ACTIVE 51 sec starting index read #事物id 13899，活躍了51秒mysql tables in use 1, locked 1 #mysql有一個表在使用，一個表被鎖住LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) #有3個鎖鏈表，記憶體中堆的大小1136, 兩行記錄被鎖定MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root updating #mysql線程id 10041, query id 31128, localhost root使用者執行update操作delete from t100 where id =3 #執行了這個SQL語句的時候，發生了鎖等待*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #等待這個鎖被釋放RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13899 lock_mode X locks rec but not gap waiting#類型：行鎖，等待在t100的主鍵上 page num 3，加一個X鎖(not gap waiting)，鎖住80 bits。Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000363b; asc 6;;; 2: len 7; hex c7000001660110; asc f ;;*** (2) TRANSACTION:#第二個事物TRANSACTION 13900, ACTIVE 31 sec starting index read #事物id 13900，活躍了31秒mysql tables in use 1, locked 1 #mysql有一個表在使用，一個表被鎖住3 lock struct(s), heap size 1136, 2 row lock(s) #有3個鎖鏈表，記憶體中堆的大小1136, 兩行記錄被鎖定MySQL thread id 10042, OS thread handle 140328392718080, query id 31129 localhost root updating  #mysql線程id 10042, query id 31129, localhost root使用者執行update操作delete from t100 where id =5 #執行了這個SQL語句的時候，發生了鎖等待*** (2) HOLDS THE LOCK(S): #事物 13900 持有的鎖RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap#類型：行鎖，事物 id 13900，在t100的主鍵上 page num 3，加一個X鎖(not gap 沒有間隙鎖)，鎖住80 bitsRecord lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000363b; asc 6;;; 2: len 7; hex c7000001660110; asc f ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED: #當事物2在執行delete from t100 where id =5的時候，發生鎖等待RECORD LOCKS space id 74 page no 3 n bits 80 index PRIMARY of table `china9129`.`t100` trx id 13900 lock_mode X locks rec but not gap waiting#類型：行鎖，等待在t100的主鍵上 page num 3，加一個X鎖(not gap waiting 沒有間隙鎖)，鎖住80 bits。Record lock, heap no 7 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 00000000363d; asc 6=;; 2: len 7; hex c90000014c0110; asc L ;;*** WE ROLL BACK TRANSACTION (2) #事物2，TRANSACTION 13900被復原了。報錯資訊：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction------------TRANSACTIONS------------Trx id counter 13905Purge done for trx‘s n:o < 13905 undo n:o < 0 state: running but idleHistory list length 568LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 421807334705888, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 13899, ACTIVE 127 sec3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1MySQL thread id 10041, OS thread handle 140330009478912, query id 31128 localhost root cleaning up-------備忘:(1) 在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引；如果一 條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。    在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的索引值，即所謂的next-key locking(2) 把死結資訊寫入到error.log。    set global innodb_print_all_deadlocks = 1;

本文出自 “12427643” 部落格，請務必保留此出處http://12437643.blog.51cto.com/12427643/1884561

mysql innodb 死結分析