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
 

分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS