MySQL OCP5.7-确定导致性能较慢的原因(锁等待)
1.打个两个终端T1和T2
T1:
[root@node232 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
root@localhost[(none)]>
T2:
[root@node232 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
root@localhost[(none)]>
2.在T1终端中执行以下的命令：
root@localhost[(none)]>use employees;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
root@localhost[employees]>desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char(4) | NO | PRI | NULL | |
| dept_name | varchar(40) | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@localhost[employees]>show create table departments;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| departments | CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Database changed
root@localhost[employees]>start transaction;
Query OK, 0 rows affected (0.00 sec)
root@localhost[employees]>select * from departments for update;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.00 sec)
3.在T2终端中插入一条记录：
root@localhost[employees]>use employees;
Database changed
root@localhost[employees]>insert into departments values('d010','Distribution');
#此时这条语句插入不成功，原因是这个表已被T1终端锁住了全部记录
#在等待50s以后会报以下错误:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
在T2中增加锁超时的的参数由50修改为3600，再重复上面的插入操作：
root@localhost[employees]>set innodb_lock_wait_timeout=3600;
Query OK, 0 rows affected (0.00 sec)
root@localhost[employees]>insert into departments values('d010','Distribution');
#结果还是被锁住，只是这次需要等待3600s，才会报锁等待超时错误
4.在T1终端中查询锁等待相关数据：
root@localhost[employees]>show full processlist\G;
*************************** 1. row ***************************
Id: 3
User: root
Host: localhost
db: employees
Command: Query
Time: 0
State: starting
Info: show full processlist
*************************** 2. row ***************************
Id: 4
User: root
Host: localhost
db: employees
Command: Query
Time: 99
State: update
Info: insert into departments values('d010','Distribution')
2 rows in set (0.00 sec)
ERROR:
No query specified
#以上State:update 状态,这可能表示该语句已被阻止，但也可能表示表示它正在正常执行
查看sys.session视图查看更详细的信息：
root@localhost[employees]>select * from sys.session\G;
*************************** 1. row ***************************
thd_id: 29
conn_id: 4
user: root@localhost
db: employees
command: Query
state: update
time: 219
current_statement: insert into departments values('d010','Distribution')
statement_latency: 3.66 m
progress: NULL
lock_latency: 104.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2240
program_name: mysql
*************************** 2. row ***************************
thd_id: 28
conn_id: 3
user: root@localhost
db: employees
command: Query
state: Sending data
time: 0
current_statement: select * from sys.session
statement_latency: 16.87 ms
progress: NULL
lock_latency: 15.78 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2238
program_name: mysql
2 rows in set (0.11 sec)
ERROR:
No query specified
通过sys.innodb_lock_waits视图查看锁的相关信息：
root@localhost[employees]>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
waiting_trx_id: 1927437
waiting_pid: 4
waiting_query: insert into departments values('d010','Distribution')
blocking_trx_id: 1927435
blocking_pid: 3
blocking_query: SELECT waiting_trx_id, waiti ... ry FROM sys.innodb_lock_waits
1 row in set, 3 warnings (0.00 sec)
#以上显示了等待的事务ID，SQL语句，以下加锁的事务ID和SQL语句
通过information_schema.innodb_locks可以查看到被锁等待的SQL锁类型
root@localhost[employees]>select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
lock_id: 1927437:99:4:3
lock_trx_id: 1927437
lock_mode: X,GAP
lock_type: RECORD
lock_table: `employees`.`departments`
lock_index: dept_name
lock_space: 99
lock_page: 4
lock_rec: 3
lock_data: 'Finance'
*************************** 2. row ***************************
lock_id: 1927435:99:4:3
lock_trx_id: 1927435
lock_mode: X
lock_type: RECORD
lock_table: `employees`.`departments`
lock_index: dept_name
lock_space: 99
lock_page: 4
lock_rec: 3
lock_data: 'Finance'
2 rows in set, 1 warning (0.00 sec)
通过视图 查看锁等待的详细信息以及解除锁等待的方法：
root@localhost[employees]>select * from sys.innodb_lock_waits \G;
*************************** 1. row ***************************
wait_started: 2021-07-04 01:12:52
wait_age: 00:18:48
wait_age_secs: 1128
locked_table: `employees`.`departments`
locked_index: dept_name
locked_type: RECORD
waiting_trx_id: 1927437
waiting_trx_started: 2021-07-04 01:12:52
waiting_trx_age: 00:18:48
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 1
waiting_pid: 4
waiting_query: insert into departments values('d010','Distribution')
waiting_lock_id: 1927437:99:4:3
waiting_lock_mode: X,GAP
blocking_trx_id: 1927435
blocking_pid: 3
blocking_query: select * from sys.innodb_lock_waits
blocking_lock_id: 1927435:99:4:3
blocking_lock_mode: X
blocking_trx_started: 2021-07-04 01:07:56
blocking_trx_age: 00:23:44
blocking_trx_rows_locked: 19
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 3
sql_kill_blocking_connection: KILL 3 #可以通过这个命令来解除锁等待的状态
1 row in set, 3 warnings (0.00 sec)
ERROR:
No query specified
5.再启动T3终端，来执行解除锁等待的状态：
[root@node232 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
root@localhost[(none)]>show processlist;
+----+------+-----------+-----------+---------+------+----------+-------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-----------+---------+------+----------+-------------------------------------------------------+
| 3 | root | localhost | employees | Sleep | 252 | | NULL |
| 4 | root | localhost | employees | Query | 1442 | update | insert into departments values('d010','Distribution') |
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+-----------+---------+------+----------+-------------------------------------------------------+
3 rows in set (0.01 sec)
root@localhost[(none)]>kill 3;
Query OK, 0 rows affected (0.00 sec)
6.查看终端T2的情况：
root@localhost[employees]>insert into departments values('d010','Distribution');
Query OK, 1 row affected (24 min 12.14 sec)
root@localhost[employees]>select * from departments where dept_no='d010';
+---------+--------------+
| dept_no | dept_name |
+---------+--------------+
| d010 | Distribution |
+---------+--------------+
1 row in set (0.00 sec)
#当在终端T3执行了命令KILL 3 之后，在终端T2上的SQL马山执行成功了，并已插入数据库中，到此锁等待已解除锁定
7.通过slap测试脚本来查看锁等待情况：
cat /labs/slap-test-updates.sh
#!/bin/bash
mysqlslap --user=root --password=oracle --concurrency=5 \
--iterations=100 --number-char-cols=4 --number-int-cols=7 \
--auto-generate-sql --number-of-queries=10000 \
--auto-generate-sql-load-type=update
7.1在终端T1中执行以上SHELL脚本
[root@node232 shell_scripts]# /bin/bash ./slap-test-updates.sh
7.2在终端T2中查看相关视图：
[root@node232 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.18-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
root@localhost[(none)]>SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: employees
Command: Sleep
Time: 706
State:
Info: NULL
*************************** 2. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Sleep
Time: 853
State:
Info: NULL
*************************** 3. row ***************************
Id: 8
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
*************************** 4. row ***************************
Id: 9
User: root
Host: localhost
db: mysqlslap
Command: Sleep
Time: 5
State:
Info: NULL
*************************** 5. row ***************************
Id: 10
User: root
Host: localhost
db: mysqlslap
Command: Query
Time: 0
State: updating
Info: UPDATE t1 SET intcol1 = 489319456,intcol2 = 331666021,intcol3 = 1121937,intcol4 = 775036055,intcol5
*************************** 6. row ***************************
Id: 11
User: root
Host: localhost
db: mysqlslap
Command: Query
Time: 0
State: query end
Info: UPDATE t1 SET intcol1 = 2094622929,intcol2 = 415592277,intcol3 = 672009206,intcol4 = 884149446,intco
*************************** 7. row ***************************
Id: 12
User: root
Host: localhost
db: mysqlslap
Command: Query
Time: 0
State: query end
Info: UPDATE t1 SET intcol1 = 2094622929,intcol2 = 415592277,intcol3 = 672009206,intcol4 = 884149446,intco
*************************** 8. row ***************************
Id: 13
User: root
Host: localhost
db: mysqlslap
Command: Query
Time: 0
State: query end
Info: UPDATE t1 SET intcol1 = 2094622929,intcol2 = 415592277,intcol3 = 672009206,intcol4 = 884149446,intco
*************************** 9. row ***************************
Id: 14
User: root
Host: localhost
db: mysqlslap
Command: Query
Time: 0
State: query end
Info: UPDATE t1 SET intcol1 = 2111080261,intcol2 = 670272225,intcol3 = 1308330959,intcol4 = 1017309884,int
9 rows in set (0.00 sec)
root@localhost[(none)]>SELECT * FROM sys.session\G
*************************** 1. row ***************************
thd_id: 30
conn_id: 5
user: root@localhost
db: NULL
command: Sleep
state: NULL
time: 857
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 0 ps
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: kill 3
last_statement_latency: 162.32 us
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2260
program_name: mysql
*************************** 2. row ***************************
thd_id: 29
conn_id: 4
user: root@localhost
db: employees
command: Sleep
state: NULL
time: 710
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 126.00 us
rows_examined: 1
rows_sent: 1
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: select * from departments where dept_no='d010'
last_statement_latency: 327.33 us
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2240
program_name: mysql
*************************** 3. row ***************************
thd_id: 34
conn_id: 9
user: root@localhost
db: mysqlslap
command: Sleep
state: NULL
time: 9
current_statement: NULL
statement_latency: NULL
progress: NULL
lock_latency: 117.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 1
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: INSERT INTO t1 VALUES (1880074 ... AF3FKMtuKwCtCBdruTJKwuvLhKm')
last_statement_latency: 1.08 ms
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: mysqlslap
*************************** 4. row ***************************
thd_id: 33
conn_id: 8
user: root@localhost
db: NULL
command: Query
state: Sending data
time: 0
current_statement: SELECT * FROM sys.session
statement_latency: 52.39 ms
progress: NULL
lock_latency: 47.86 ms
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 4
tmp_disk_tables: 1
full_scan: YES
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2310
program_name: mysql
*************************** 5. row ***************************
thd_id: 35
conn_id: 10
user: root@localhost
db: mysqlslap
command: Sleep
state: NULL
time: 0
current_statement: NULL
statement_latency: 584.04 us
progress: NULL
lock_latency: 109.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: NULL
*************************** 6. row ***************************
thd_id: 36
conn_id: 12
user: root@localhost
db: mysqlslap
command: Query
state: updating
time: 0
current_statement: UPDATE t1 SET intcol1 = 465375 ... YtuxHD9rRoZEAbqEjlhpRCvKf6HF'
statement_latency: 11.02 ms
progress: NULL
lock_latency: 173.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: NULL
*************************** 7. row ***************************
thd_id: 37
conn_id: 11
user: root@localhost
db: mysqlslap
command: Query
state: updating
time: 0
current_statement: UPDATE t1 SET intcol1 = 465375 ... YtuxHD9rRoZEAbqEjlhpRCvKf6HF'
statement_latency: 7.36 ms
progress: NULL
lock_latency: 143.00 us
rows_examined: 0
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: NULL
*************************** 8. row ***************************
thd_id: 38
conn_id: 14
user: root@localhost
db: mysqlslap
command: Query
state: updating
time: 0
current_statement: UPDATE t1 SET intcol1 = 465375 ... YtuxHD9rRoZEAbqEjlhpRCvKf6HF'
statement_latency: 29.02 ms
progress: NULL
lock_latency: 217.00 us
rows_examined: 99
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: NULL
*************************** 9. row ***************************
thd_id: 39
conn_id: 13
user: root@localhost
db: mysqlslap
command: Query
state: updating
time: 0
current_statement: UPDATE t1 SET intcol1 = 591450 ... W9OE6eIFLcYJPv8o0FDmaaoZPgxr'
statement_latency: 21.32 ms
progress: NULL
lock_latency: 121.00 us
rows_examined: 2
rows_sent: 0
rows_affected: 0
tmp_tables: 0
tmp_disk_tables: 0
full_scan: NO
last_statement: NULL
last_statement_latency: NULL
current_memory: 0 bytes
last_wait: NULL
last_wait_latency: NULL
source: NULL
trx_latency: NULL
trx_state: NULL
trx_autocommit: NULL
pid: 2312
program_name: NULL
9 rows in set (0.24 sec)
root@localhost[(none)]>SELECT waiting_trx_id, waiting_pid, waiting_query, blocking_trx_id, blocking_pid, blocking_query FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
waiting_trx_id: 1935015
waiting_pid: 13
waiting_query: UPDATE t1 SET intcol1 = 578001 ... xzpBjITfmcxTn9s36jd2l4vHIy1B1'
blocking_trx_id: 1935014
blocking_pid: 11
blocking_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
*************************** 2. row ***************************
waiting_trx_id: 1935015
waiting_pid: 13
waiting_query: UPDATE t1 SET intcol1 = 578001 ... xzpBjITfmcxTn9s36jd2l4vHIy1B1'
blocking_trx_id: 1935013
blocking_pid: 10
blocking_query: UPDATE t1 SET intcol1 = 591450 ... 5W9OE6eIFLcYJPv8o0FDmaaoZPgxr'
*************************** 3. row ***************************
waiting_trx_id: 1935015
waiting_pid: 13
waiting_query: UPDATE t1 SET intcol1 = 578001 ... xzpBjITfmcxTn9s36jd2l4vHIy1B1'
blocking_trx_id: 1935012
blocking_pid: 12
blocking_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
*************************** 4. row ***************************
waiting_trx_id: 1935014
waiting_pid: 11
waiting_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
blocking_trx_id: 1935013
blocking_pid: 10
blocking_query: UPDATE t1 SET intcol1 = 591450 ... 5W9OE6eIFLcYJPv8o0FDmaaoZPgxr'
*************************** 5. row ***************************
waiting_trx_id: 1935014
waiting_pid: 11
waiting_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
blocking_trx_id: 1935012
blocking_pid: 12
blocking_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
*************************** 6. row ***************************
waiting_trx_id: 1935013
waiting_pid: 10
waiting_query: UPDATE t1 SET intcol1 = 591450 ... 5W9OE6eIFLcYJPv8o0FDmaaoZPgxr'
blocking_trx_id: 1935012
blocking_pid: 12
blocking_query: UPDATE t1 SET intcol1 = 465375 ... pYtuxHD9rRoZEAbqEjlhpRCvKf6HF'
6 rows in set, 3 warnings (0.01 sec)
总结：
锁等待以及死锁的监控相关SQL:
1. 看有没有锁等待
SHOW STATUS LIKE 'innodb_row_lock%';
2. 查看哪个事务在等待(被阻塞了)
USE information_schema
SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';
trx_id : 事务ID号
trx_state : 当前事务的状态
trx_mysql_thread_id:连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query : 当前被阻塞的操作(一般是要丢给开发的)
3.查看锁源,谁锁的我!
SELECT * FROM sys.innodb_lock_waits; ## ====>被锁的和锁定它的之间关系
locked_table : 哪张表出现的等待
waiting_trx_id: 等待的事务(与上个视图trx_id 对应)
waiting_pid : 等待的线程号(与上个视图trx_mysql_thread_id)
blocking_trx_id : 锁源的事务ID
blocking_pid : 锁源的线程号
4. 找到锁源的thread_id
SELECT * FROM performance_schema.threads WHERE processlist_id=15;
5. 找到锁源的SQL语句
-- 当前在执行的语句
SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;
-- 执行语句的历史
SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;
锁监控设计到的命令:
show status like 'innodb_rows_lock%'
select * from information_schema.innodb_trx;
select * from sys.innodb_lock_waits;
select * from performance_schema.threads;
select * from performance_schema.events_statements_current;
select * from performance_schema.events_statements_history;
死锁监控
show engine innodb status\G
show variables like '%deadlock%';
vim /etc/my.cnf
innodb_print_all_deadlocks = 1
视频讲解地址：
https://mp.weixin.qq.com/s/-PiwlRkRS8-G_lO3_Z0UDg
