MySQL lock Blocking analysis, mysql lock Blocking

MySQL lock Blocking analysis, mysql lock Blocking

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: RR

2. 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!

How can we see that mysql is not blocked?

The following query is used to query the current database, the transactions, and the resources locked. SELECTtrx_id AS 'transaction id', trx_state AS 'transaction status', trx_requested_lock_id AS 'resources to wait for the transaction ', trx_wait_startedAS 'transaction start wait time', trx_tables_in_use AS 'transaction use table ', trx_tables_locked AS 'transaction lock', trx_rows_locked AS 'transaction lock row', trx_rows_modified AS 'transaction change row' FROMinformation _ schema. innodb_trx; SELECTlock_idAS 'lock id', lock_trx_id AS 'transaction ID with lock', lock_mode AS 'lock mode', lock_type AS 'lock type ', lock_table AS 'locked table', lock_index AS 'locked Index', lock_space AS 'locked tablespace number', lock_page AS 'locked page number ', lock_rec AS 'record number locked ', lock_data AS 'locked data' FROMinformation _ schema. innodb_locks; SELECT requesting_trx_id AS 'request lock transaction id', requested_lock_id AS 'request lock id', blocking_trx_id AS 'current lock transaction id ', blocking_lock_id AS 'current lock ID 'FROM innodb_lock_waits;

Mysql does not understand the lock table mechanism.

Table-level locks, page-level locks, and row-level locks are locks of different granularities. The difference is the lock range. As the name suggests, table-level locks request a lock throughout the table, A page lock requests a lock on a data page, and a row lock requests a lock on a row.
Generally, the update operation has a higher priority than the read operation, provided that the read operation has not been executed in the queue. If the read operation has started to obtain data from the data, at this time, the lock resource of the update request has been locked by the read operation, so it can only be executed after the read operation is complete.