MySQL lock Blocking Analysis

MySQL lock Blocking Analysis

During routine maintenance, threads are often blocked, resulting in slow database response. Let's take a look at how to obtain which thread causes blocking.

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

1. Environment Description RHEL 6.4 x86_64 + MySQL 5.6.19
Transaction isolation level: RR2. test process

3. view the lock blocking thread information. Here we use several methods for analysis: 3.1 Use 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)

This method is not very well validated if the database has multiple threads. 3.2 directly use show engine innodb status to view

------------ TRANSACTIONS ------------ Trx id counter 4131 Purge done for trx's n: o <4119 undo n: o <0 state: running but idleHistory list length 126 list of transactions for each session: --- TRANSACTION 0, not startedMySQL thread id 2, OS thread handle 0x7f953ffff700, query id 115 localhost root initshow engine innodb status --- TRANSACTION 4130, ACTIVE 41 sec starting index readmysql tables in use 1, locked 1 lock wait 2 lock struct (s), heap size 360, 1 row lock (s) MySQL thread id 4, OS thread handle 0x7f953ff9d700, query id 112 localhost root updatingdelete from emp where empno = 7788 ------- trx has been waiting 41 sec for this lock to be granted: # Wait for 41 sRECORD LOCKS space id 16 page no 3 n bits 88 index 'primary' of table '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 is waiting for test. apply the X lock to the primary key of 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 45 sec starting index readmysql tables in use 1, locked 1 lock wait 2 lock struct (s), heap size 360, 1 row lock (s) mySQL thread id 7, OS thread handle 0x7f953ff6c700, query id 111 localhost root updatingupdate emp set sal = 3500 where empno = 7788 ------- TRX HAS BEEN WAITING 45 SEC FOR THIS LOCK TO BE GRANTED: # Wait for 45 sRECORD LOCKS space id 16 page no 3 n bits 88 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 is waiting for test. apply the X lock to the primary key of 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 51 sec2 lock struct (s), heap size 360, 1 row lock (s) MySQL thread id 3, OS thread handle 0x7f953ffce700, query id 110 localhost root cleaning up

We know that the main cause is thread = 3, but this result cannot be analyzed from innodb status.

From the above, thread 4 and thread 7 are waiting for test. the X lock is applied to the primary key of emp. page num is 3, but the waiting time of thread 7 is 45 s, while that of thread 4 is 41 s, it is a lock applied after thread 7, so it can be determined that thread 7 blocks thread 4. Why does thread 7 wait? The root cause cannot be analyzed here.

3.3 Use mysqladmin debug to 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, we can find that the thread ID = 3 and 7 are blocked, but it is still not accurate. It cannot be determined that thread 7 is blocked by thread ID = 3. 3.4 use innodb_lock_monitor to obtain the blocking lock thread

MySQL [test]> create table innodb_lock_monitor (a INT) ENGINE = INNODB; # If you CREATE this TABLE in a database, lock monitorQuery OK, 0 rows affected, 1 warning (0.07 sec) mySQL [test]> show warnings \ G *************************** 1. row ************************** Level: Warning Code: 131 Message: using the table name innodb_lock_monitor to enable diagnostic output is deprecated and may be removed in future releases. use INFORMATION_SCHEMA or PERFORMANCE_SCHEMA tables or set global innodb_status_output = ON.1 row in set (0.00 sec)

Note: There is a warning in 5.6, but this does not affect the usage.

Then use show engine innodb status to view:

------------ TRANSACTIONS ------------ Trx id counter 4667 Purge done for trx's n: o <4659 undo n: o <0 state: running but idleHistory list length 138 list of transactions for each session: --- TRANSACTION 0, not startedMySQL thread id 9, OS thread handle 0x7f813c5f7700, query id 152 localhost root initshow engine innodb status --- TRANSACTION 4663, ACTIVE 78 sec starting index readmysql tables in use 1, locked 1 lock wait 2 lock struct (s), heap size 360, 1 row lock (s) MySQL thread id 4, OS thread handle 0x7f813c628700, query id 149 localhost root updatingdelete from emp where empno = 7788 ------- trx has been waiting 78 sec for this lock to be granted: # Wait for 78 sRECORD LOCKS space id 16 page no 3 n bits 88 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 # thread 4 is waiting for test. apply the X lock to the primary key of 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 applying the X lock to the primary key row, apply the intention lock ixrecord locks space id 16 page no 3 n bits 88 index 'primary' of table 'test' to the table '. '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 0: len 4; hex 80001e6c; asc l; 1: len 6; hex 000000001018; asc; 2: len 7; hex 91000001420084; asc B; 3: len 5; hex 534f5454; 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 81 sec starting index readmysql tables in use 1, locked 1 lock wait 2 lock struct (s), heap size 360, 1 row lock (s) MySQL thread id 7, OS thread handle 0x7f813c5c6700, query id 148 localhost root updatingupdate emp set sal = 3500 where empno = 7788 ------- trx has been waiting 81 sec for this lock to be granted: # Wait for 81 sRECORD LOCKS space id 16 page no 3 n bits 88 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 is waiting for test. apply the X lock to the primary key of 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 # before applying the X lock to the primary key row, apply the intention lock ixrecord locks space id 16 page no 3 n bits 88 index 'primary' of table 'test' to the table '. '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 0: len 4; hex 80001e6c; asc l; 1: len 6; hex 000000001018; asc; 2: len 7; hex 91000001420084; asc B; 3: len 5; hex 534f5454; 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 1222 mysql tables in use 2, locked 02 lock struct (s), heap size 360, 1 row lock (s) MySQL thread id 3, OS thread handle 0x7f813c659700, query id 147 localhost root Sending dataselect count (*) from t3 a, t3 B # This is the SQLTrx read view currently being executed by thread 3 will not see trx with id> = 4662, sees <4659 table lock table 'test '. 'emp' trx id 4615 lock mode IX # thread 3 is having the intention IX lock on the table and has test. row-Level X lock of the PRIMARY key in the emp table. page num = 3 record locks space id 16 page no 3 n bits 88 index 'primary' of table 'test '. 'emp' trx 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 534f5454; 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 does thread 3 currently execute a select t3 table operation, but it locks page num = 3 in the test. emp table?
It may be because the operation transaction for the test. emp table was not committed in time before thread 3.
So it is concluded that thread 3 blocks thread 7, and thread 7 blocks thread 4, so the root cause is thread 3. Let thread 3 submit or kill as soon as possible. 4. Conclusion

Comparison of several methods when analyzing lock blocking in innodb:

(1) Use show processlist to view unreliable data;
(2) directly use show engine innodb status to view the cause of the problem;
(3) Use mysqladmin debug to view all threads that generate locks, but cannot determine which is the root cause;
(4) After innodb_lock_monitor is enabled, use show engine innodb status to view the cause of lock blocking.

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

-- Bosco QQ: 375612082
---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!