MySQL InnoDB deadlock analysis

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 (one)  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&NBSP;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# viewing deadlock information "[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,&NBSP;134731.00&NBSP;RW-EXCL,&NBSP;21.89&NBSP;RW-SX------------------------latest detected  deadlock  #监测出最近的死锁信息------------------------2016-12-20 21:56:29 0x7fa0bffff700***  (1)  TRANSACTION:  #第一个事物TRANSACTION  13899, active 51 sec starting index  read  #事物id  13899, active for 51 seconds mysql tables in use 1, locked 1 # MySQL has a table in use, a table is locked lock wait 3 lock struct (s), heap size 1136, 2  Row lock (s)   #有3个锁链表, the size of the heap in memory 1136,  two rows of records are locked mysql thread id 10041, os  Thread handle 140330009478912, query id 31128 localhost root updating   #mysql线程id  10041, query id 31128, localhost root user to perform the update operation delete  from t100 where id =3  #执行了这个SQL语句的时候, there was a lock waiting ***  (1)  WAITING FOR  this lock to be&nbsp 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# type: Row lock, waiting on T100 primary key  page  Num 3, add an X lock (not gap waiting), lock the 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, active for 31 seconds mysql tables in use 1, locked 1   #mysql有一个表在使用, a watch is locked 3 lOck struct (s),  heap size 1136, 2 row lock (s)   #有3个锁链表, size of heap in memory 1136,   Two rows of records are locked mysql thread id 10042, os thread handle 140328392718080,  query id 31129 localhost root updating   #mysql线程id  10042, query  id 31129, localhost root user to perform the update operation delete from t100 where id =5   #执行了这个SQL语句的时候, there was a lock waiting ***  (2)  holds the lock (S):  #事物  13900  held lock 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# type: Row lock, Thing  id 13900, on T100 's primary key  page num  3, add an X lock (not gap  no gap Lock), lock 80 bitsrecord lock, heap no 5 physical  record: n_fields 3;  COMPACT&NBSP;FORMAT;&NBSP;INFO&NBSP;BITS&NBSP;0&NBSP;0:&NBSP;LEN&NBSP;4;&NBSP;HEX&NBSP;80000003;&NBSP;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, there is a lock waiting 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# type: Row lock, Wait for  page num 3 on the primary key of the T100, add an X lock (not gap waiting  no gap Lock), lock 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 was rolled back. Error message: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&Nbsp;lock (s),  undo log entries 1mysql thread id 10041, os thread  handle 140330009478912, query id 31128 localhost root cleaning  Up-------Notes: (1)   in MySQL, row-level locking is not a direct lock record, but a lock index. Indexes are primary key index and non-primary key index Two, if a SQL statement operation primary Key index, MySQL will lock the primary key index, if a   statement operation non-primary key index, MySQL will first lock the non-primary key index, and then lock the relevant primary key index.      in the update, delete operation, MySQL not only locks all index records scanned by the Where condition, but also locks adjacent key values, known as next-key locking (2)   Writes the deadlock information to Error.log.     set global innodb_print_all_deadlocks = 1;

This article is from the "12427643" blog, please be sure to keep this source http://12437643.blog.51cto.com/12427643/1884561

MySQL InnoDB deadlock analysis