MySQL Lock blocking analysis

In routine maintenance, the threads are often blocked, causing the database to respond very slowly, and see how to obtain which thread is causing the blocking.

Blog Address:http://blog.csdn.net/hw_libo/article/details/39080809

1. Environment Description Rhel 6.4 x86_64 + MySQL 5.6.19
Transaction ISOLATION Level: RR

2. Test process

3. View lock blocking thread information here are some methods to analyze:

3.1 Using show processlist to view

MySQL [(None)]> show processlist;+----+------+-----------+------+---------+------+--------------+------------- -----------------------------+| Id | User | Host      | db   | Command | Time | State        | Info                                     |+----+------+-----------+------+---------+------+--------------+------------------------------------- -----+|  2 | Root | localhost | NULL | Query   |    0 | Init         | show Processlist                         | |  3 | Root | localhost | Test | Query   |   70 | Sending Data | Select COUNT (*) from T3 a,t3 b           | |  4 | Root | localhost | Test | Query   |   65 | Updating     | Delete from emp where empno=7788         | |  7 | Root | localhost | Test | Query   |   68 | Updating     | Update emp set SAL=3500 where empno=7788 |+----+------+-----------+------+---------+------+---------- ----+------------------------------------------+4 rows in Set (0.00 sec)

If there are more threads in the database, this method is really not very good to confirm.

3.2 Direct use of Show engine InnoDB status View

------------transactions------------Trx ID counter 4131Purge done for Trx ' s N:o < 4119 undo N:o < 0 state:running b UT idlehistory list length 126LIST of transactions for each SESSION:---TRANSACTION 0, not startedmysql thread ID 2, OS THR EAD handle 0x7f953ffff700, query ID + localhost root initshow engine InnoDB status---TRANSACTION 4130, ACTIVE + sec sta Rting index Readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct (s), heap size, 1 row lock (s) MySQL thread ID 4, OS thread handle 0x7f953ff9d700, query ID, updatingdelete from emp where empno=7788-------TRX have BEEN Waiting-SEC for-this LOCK-to-be granted: # # waits for 41sRECORD LOCKS Space ID-page No 3 n bits to index ' PRIMARY ' of tab Le ' Test '. ' EMP ' TRX ID 4130 lock_mode X locks Rec but not gap Waitingrecord lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 4 waits for an X lock on the primary key in Test.emp, page num=3 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:Len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;; ---------------------TRANSACTION 4129, ACTIVE sec starting index Readmysql tables in use 1, locked 1LOCK WAIT 2 Lock St Ruct (s), heap size, 1 row lock (s) MySQL thread ID 7, OS thread handle 0x7f953ff6c700, query ID 111 localhost root updat Ingupdate EMP Set sal=3500 where empno=7788-------TRX have BEEN waiting SEC for this LOCK to be granted: # # wait 45sREC ORD LOCKS Space ID page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4129 lock_mode X LOCKS Rec but not Gap Waitingrecord Lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 7 waits for an X lock on the primary key in Test.emp, page num=3 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:len 7; Hex 91000001420084;ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;; ---------------------TRANSACTION 4128, ACTIVE Wuyi sec2 lock struct (s), heap size, 1 row lock (s) MySQL thread ID 3, OS th Read handle 0x7f953ffce700, query ID. localhost Root cleaning up

We know that the main root cause is still thread=3, but the result cannot be analyzed from the InnoDB status.

From the above, thread 4 and thread 7 are waiting for the primary key to Test.emp in the X lock, page num=3, but thread 7 waits for 45s, and thread 4 waits for 41s, is the lock requested after the thread 7, so it can be determined that thread 7 is blocking thread 4. As for why thread 7 waits, there is no root cause analysis.

3.3 Using mysqladmin Debug view

# mysqladmin-s/tmp/mysql3306.sock Debug

Then in the error log, you will see:

Thread database.table_name          locked/waiting        lock_type3       test.t3                     locked-read Low Priority         Read Lock7       test.emp                    locked-write High priority        write lock

In this method, can find the thread id=3 and 7 is the block, but still not very accurate, not to judge the thread 7 is also blocked by thread id=3.

3.4 Using Innodb_lock_monitor to get a blocking lock thread

MySQL [test]> CREATE TABLE innodb_lock_monitor (a INT) Engine=innodb;  # # Create this table in a database, and it will open lock Monitorquery OK, 0 rows affected, 1 warning (0.07 sec) MySQL [test]> show warnings\g********* 1. Row ***************************  level:warning   code:131message:using The table name Innodb_lock_monitor to Enable diagnostic output is deprecated and may be removed in the future releases. Use INFORMATION_SCHEMA or Performance_schema tables or set GLOBAL Innodb_status_output=on.1 row in SET (0.00 sec)

Description: This has a warning in 5.6, but does not affect the use.

Then use show engine InnoDB status to view:

------------transactions------------Trx ID counter 4667Purge done for Trx ' s N:o < 4659 undo N:o < 0 state:running b UT idlehistory list length 138LIST of transactions for each SESSION:---TRANSACTION 0, not startedmysql thread ID 9, OS THR EAD handle 0x7f813c5f7700, query ID $ localhost root initshow engine InnoDB status---TRANSACTION 4663, ACTIVE sec sta Rting index Readmysql tables in use 1, locked 1LOCK WAIT 2 lock struct (s), heap size, 1 row lock (s) MySQL thread ID 4, OS thread handle 0x7f813c628700, query ID 149 localhost root updatingdelete from emp where empno=7788-------TRX have BEEN Waiting-SEC for-this LOCK-to-be granted: # # waits for 78sRECORD LOCKS Space ID-page No 3 n bits to index ' PRIMARY ' of tab Le ' Test '. ' EMP ' TRX ID 4663 lock_mode X locks Rec but not gap Waitingrecord lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 4 waits for an X lock on the primary key in Test.emp, page num=3 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:Len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;;  ------------------table lock table ' test '. ' EMP ' TRX ID 4663 lock Mode IX # # before adding an X lock to the primary key line, first add the intent lock Ixrecord LOCKS space ID to the table Page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4663 lock_mode X locks Rec but not gap Waitingrecord Lock, Heap No 9 physical record:n_fields 10; Compact format; Info bits 0 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;; ---TRANSACTION 4662, ACTIVE bayi sec starting index ReaDmysql tables in use 1, locked 1LOCK WAIT 2 lock struct (s), heap size, 1 row lock (s) MySQL thread ID 7, OS thread Handl  e 0x7f813c5c6700, query ID 148 localhost root updatingupdate emp set sal=3500 where empno=7788-------TRX has BEEN waiting Bayi SEC for this LOCK to be granted: # # wait for 81sRECORD LOCKS Space ID-page No 3 n bits to index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4662 lock_mode X locks Rec but not gap Waitingrecord lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 # # Thread 7 waits for an X lock on the primary key in Test.emp, page num=3 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;; ------------------table lock table ' test '. ' EMP ' TRX ID 4662 lock Mode IX # # To add an intent lock on the table before adding X lock to the primary key lineIxrecord LOCKS Space ID page No 3 n bits the index ' PRIMARY ' of table ' test '. ' EMP ' TRX ID 4662 lock_mode X LOCKS Rec but Not Gap Waitingrecord Lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;; ---TRANSACTION 4615, ACTIVE 1579 sec, thread declared inside InnoDB 1222mysql tables in use 2, locked lock struct (s), H EAP size, 1 row lock (s) MySQL thread ID 3, OS thread handle 0x7f813c659700, query ID 147 localhost root sending Datasel ECT COUNT (*) from T3 a,t3 B # # This is thread 3 currently executing Sqltrx read view would not see Trx with ID >= 4662, sees < 4659TABLE LOC K table ' Test '. ' EMP ' TRX ID 4615 lock Mode IX # # # # # # # # # # # # # # # 3 in the owning tableThe Intent IX lock, and the row-level x lock of the primary key on the Test.emp table, page Num=3record LOCKS Space ID-page No 3 n bits to index ' PRIMARY ' of table ' test '. ' EMP ' TR X ID 4615 lock_mode x Locks Rec but not gaprecord lock, heap No 9 physical record:n_fields 10; Compact format; Info bits 0 0:len 4; Hex 80001e6c; ASC L;; 1:len 6; Hex 000000001018; ASC;; 2:len 7; Hex 91000001420084; ASC B;; 3:len 5; Hex 53434f5454; ASC SCOTT;; 4:len 7; Hex 414e414c595354; ASC ANALYST;; 5:len 4; Hex 80001d8e; ASC;; 6:len 4; Hex 208794f0; ASC;; 7:len 4; Hex 80000bb8; ASC;; 8:sql NULL; 9:len 4; Hex 80000014; ASC;;

Why is thread 3 currently performing a select T3 table operation, but locking the page num=3 on the test.emp table?
It is possible that the operation of the Test.emp table before thread 3 was not committed in a timely manner.
So it concludes that thread 3 is blocking thread 7, and thread 7 is blocking thread 4, so the root cause is thread 3, so that thread 3 commits as soon as possible or kill.

4. Conclusion

In the analysis of InnoDB lock blocking, the comparison of several methods:

(1) Use show processlist to view the non-reliable;
(2) Direct use of Show engine InnoDB status View, unable to determine the root cause of the problem;
(3) Using Mysqladmin Debug view, can see all the threads that generate the lock, but can not determine which is the root cause;
(4) After opening innodb_lock_monitor, then use show engine InnoDB status View, can find the root cause of lock blocking.

Blog Address: http://blog.csdn.net/hw_libo/article/details/39080809

--Bosco qq:375612082
----END----
-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!

MySQL Lock blocking analysis