mysql+查询被锁住_监控mysql锁定状态_MySQL 锁的监控及处理

故障模拟

# 添加两项配置

vi /etc/my.cnf

[mysqld]

autocommit=0

innodb_lock_wait_timeout = 3600

systemctl restart mysqld

# 登录数据库

use klvchen;

UPDATE t1 SET k1='av' WHERE id=1;

# 新开一个窗口，再登录数据库

use klvchen;

UPDATE t1 SET k1='az' WHERE id=1;

# 此时页面卡住

(一)看有没有锁等待

# 新开一个窗口，登录数据库

SHOW STATUS LIKE 'innodb_row_lock%';

+-------------------------------+-------+

| Variable_name | Value |

+-------------------------------+-------+

| Innodb_row_lock_current_waits | 1 |

| Innodb_row_lock_time | 0 |

| Innodb_row_lock_time_avg | 0 |

| Innodb_row_lock_time_max | 0 |

| Innodb_row_lock_waits | 1 |

+-------------------------------+-------+

(二)查看哪个事务在等待(被阻塞了)

USE information_schema;

SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT'\G;

*************************** 1. row ***************************

trx_id: 14597 # 事务ID号

trx_state: LOCK WAIT # 当前事务的状态

trx_started: 2019-12-21 09:20:25

trx_requested_lock_id: 14597:32:3:2

trx_wait_started: 2019-12-21 09:20:25

trx_weight: 2

trx_mysql_thread_id: 3 # 连接层的,连接线程ID(SHOW PROCESSLIST ===>Id或trx_id )

trx_query: UPDATE t1 SET k1='az' WHERE id=1 # 当前被阻塞的操作(一般是要丢给开发的)

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 1136

trx_rows_locked: 1

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 0

trx_is_read_only: 0

trx_autocommit_non_locking: 0

(三)查看锁源,谁锁的我

SELECT * FROM sys.innodb_lock_waits\G; # 被锁的和锁定它的之间关系

*************************** 1. row ***************************

wait_started: 2019-12-21 09:20:25

wait_age: 00:01:30

wait_age_secs: 90

locked_table: `klvchen`.`t1` # 哪张表出现的等待

locked_index: GEN_CLUST_INDEX

locked_type: RECORD

waiting_trx_id: 14597 # 等待的事务(与上个视图trx_id 对应)

waiting_trx_started: 2019-12-21 09:20:25

waiting_trx_age: 00:01:30

waiting_trx_rows_locked: 1

waiting_trx_rows_modified: 0

waiting_pid: 3 # 等待的线程号(与上个视图trx_mysql_thread_id)

waiting_query: UPDATE t1 SET k1='az' WHERE id=1

waiting_lock_id: 14597:32:3:2

waiting_lock_mode: X

blocking_trx_id: 14596 # 锁源的事务ID

blocking_pid: 2 # 锁源的线程号

blocking_query: NULL

blocking_lock_id: 14596:32:3:2

blocking_lock_mode: X

blocking_trx_started: 2019-12-21 09:20:12

blocking_trx_age: 00:01:43

blocking_trx_rows_locked: 5

blocking_trx_rows_modified: 0

sql_kill_blocking_query: KILL QUERY 2

sql_kill_blocking_connection: KILL 2

(四)找到锁源的 thread_id

SELECT * FROM performance_schema.threads WHERE processlist_id=2\G;

*************************** 1. row ***************************

THREAD_ID: 27

NAME: thread/sql/one_connection

TYPE: FOREGROUND

PROCESSLIST_ID: 2

PROCESSLIST_USER: root

PROCESSLIST_HOST: localhost

PROCESSLIST_DB: klvchen

PROCESSLIST_COMMAND: Sleep

PROCESSLIST_TIME: 261

PROCESSLIST_STATE: NULL

PROCESSLIST_INFO: UPDATE t1 SET k1='av' WHERE id=1

PARENT_THREAD_ID: 1

ROLE: NULL

INSTRUMENTED: YES

HISTORY: YES

CONNECTION_TYPE: Socket

THREAD_OS_ID: 2557

(五)找到锁源的SQL语句

-- 当前在执行的语句

SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=27\G;

*************************** 1. row ***************************

THREAD_ID: 27

EVENT_ID: 8

END_EVENT_ID: 8

EVENT_NAME: statement/sql/update

SOURCE:

TIMER_START: 16785505578000

TIMER_END: 16785741560000

TIMER_WAIT: 235982000

LOCK_TIME: 126000000

SQL_TEXT: UPDATE t1 SET k1='av' WHERE id=1

DIGEST: c75120019b1993cf2423bd9add827987

DIGEST_TEXT: UPDATE `t1` SET `k1` = ? WHERE `id` = ?

CURRENT_SCHEMA: klvchen

OBJECT_TYPE: NULL

OBJECT_SCHEMA: NULL

OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: NULL

MYSQL_ERRNO: 0

RETURNED_SQLSTATE: 00000

MESSAGE_TEXT: Rows matched: 1 Changed: 0 Warnings: 0

ERRORS: 0

WARNINGS: 0

ROWS_AFFECTED: 0

ROWS_SENT: 0

ROWS_EXAMINED: 4

CREATED_TMP_DISK_TABLES: 0

CREATED_TMP_TABLES: 0

SELECT_FULL_JOIN: 0

SELECT_FULL_RANGE_JOIN: 0

SELECT_RANGE: 0

SELECT_RANGE_CHECK: 0

SELECT_SCAN: 0

SORT_MERGE_PASSES: 0

SORT_RANGE: 0

SORT_ROWS: 0

SORT_SCAN: 0

NO_INDEX_USED: 0

NO_GOOD_INDEX_USED: 0

NESTING_EVENT_ID: NULL

NESTING_EVENT_TYPE: NULL

NESTING_EVENT_LEVEL: 0

-- 执行语句的历史

SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=27\G;

得出结果,丢给开发

表信息

被阻塞的

锁源SQL

原文：https://www.cnblogs.com/klvchen/p/12076019.html