Locate the SQL statement holding the lock by querying the log
MYSQL version:
Mysql > select version ()
+-+
| | version () |
+-+
| | 5.6.27-log |
+-+
1 row in set (0.00 sec)
1. Enable query log
Mysql > SET GLOBAL general_log=1
Query OK, 0 rows affected (0.02 sec)
Mysql > show variables like 'general_log%'
+-+
| | Variable_name | Value |
+-+
| | general_log | ON |
| | general_log_file | / app/oracle/oradata2/mysql-5.5.37/data/oradb.log |
+-+
2 rows in set (0.00 sec)
two。 View MYSQL thread information
-- MYSQL Thread 1
Mysql > show full processlist
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 1 | root | localhost | NULL | Query | 0 | init | show full processlist | |
+-+-
1 row in set (0.00 sec)
-- MYSQL Thread 2
Mysql > show full processlist
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 1 | root | localhost | NULL | Sleep | 145 | | NULL |
| | 2 | root | localhost | NULL | Query | 0 | init | show full processlist | |
+-+-
2 rows in set (0.00 sec)
-- MYSQL Thread 3
Mysql > show full processlist
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 1 | root | localhost | NULL | Sleep | 189 | | NULL |
| | 2 | root | localhost | NULL | Sleep | 44 | | NULL |
| | 3 | root | localhost | NULL | Query | 0 | init | show full processlist | |
+-+-
3 rows in set (0.00 sec)
-- MSYQL Thread 4
Mysql > show full processlist
+-+-
| | Id | User | Host | db | Command | Time | State | Info |
+-+-
| | 1 | root | localhost | NULL | Sleep | 238 | | NULL |
| | 2 | root | localhost | NULL | Sleep | 93 | | NULL |
| | 3 | root | localhost | NULL | Sleep | 49 | | NULL |
| | 4 | root | localhost | NULL | Query | 0 | init | show full processlist | |
+-+-
4 rows in set (0.00 sec)
-- MYSQL query log
[root@oradb data] # tail-f oradb.log
/ app/oracle/oradata2/mysql-5.6.27/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). Started with:
Tcp port: 3306 Unix socket: / tmp/mysql.sock
Time Id Command Argument
161116 10:48:57 1 Query show variables like 'general_log%'
161116 10:50:07 1 Query show full processlist
161116 10:52:24 2 Connect root@localhost on
2 Query select @ @ version_comment limit 1
161116 10:52:32 2 Query show full processlist
161116 10:53:09 3 Connect root@localhost on
3 Query select @ @ version_comment limit 1
161116 10:53:16 3 Query show full processlist
161116 10:53:57 4 Connect root@localhost on
4 Query select @ @ version_comment limit 1
161116 10:54:05 4 Query show full processlist
ID: represents the MYSQL thread ID
3. Use the query log to find out the blocking SQL caused by holding the lock
-- MYSQL thread 1 performs updates, and the uncommitted transaction persists in holding the lock
Use test
START TRANSACTION
UPDATE t1 SET NAME='test11' WHERE id
