故障模拟
# 添加两项配置
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