准备
create database test;
use test;
create table user(id bigint not null auto_increment primary key ,name varchar(100) not null default "");
insert into user(id,name) values(1,"hello");
开启一个事务
begin;
select * from user where id = 1 for update;
开启另一个事务
begin;
delete from user where id = 1;
查询等待锁
mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 1289
requested_lock_id: 1289:23:3:2
blocking_trx_id: 1288
blocking_lock_id: 1288:23:3:2
1 row in set, 1 warning (0.00 sec)
查看锁信息
mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
lock_id: 1289:23:3:2
lock_trx_id: 1289
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: PRIMARY
lock_space: 23
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 1288:23:3:2
lock_trx_id: 1288
lock_mode: X
lock_type: RECORD
lock_table: `test`.`user`
lock_index: PRIMARY
lock_space: 23
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set, 1 warning (0.00 sec)
查看对应的innodb事务
mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 1289
trx_state: LOCK WAIT
trx_started: 2020-08-25 03:25:07
trx_requested_lock_id: 1289:23:3:2
trx_wait_started: 2020-08-25 05:21:48
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: delete from user 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: 10
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
*************************** 2. row ***************************
trx_id: 1288
trx_state: RUNNING
trx_started: 2020-08-25 03:21:22
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 6
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
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
2 rows in set (0.00 sec)
查到对应的thread_id
根据上面的trx_mysql_thread_id
mysql> select * from performance_schema.threads where PROCESSLIST_ID= 6 \G;
*************************** 1. row ***************************
THREAD_ID: 31
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 6
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
PROCESSLIST_TIME: 5218
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: select * from user where id = 1 for update
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 1584
1 row in set (0.00 sec)
查到实际sql
根据上面查到的thread_id
mysql> select * from performance_schema.events_statements_current where thread_id = 31\G;
*************************** 1. row ***************************
THREAD_ID: 31
EVENT_ID: 18
END_EVENT_ID: 18
EVENT_NAME: statement/sql/select
SOURCE:
TIMER_START: 2752787228819000
TIMER_END: 2752787541706000
TIMER_WAIT: 312887000
LOCK_TIME: 162000000
SQL_TEXT: select * from user where id = 1 for update
DIGEST: f4039aca83092729d9f240cc984992d2
DIGEST_TEXT: SELECT * FROM SYSTEM_USER WHERE `id` = ? FOR UPDATE
CURRENT_SCHEMA: test
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 1
ROWS_EXAMINED: 1
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
1 row in set (0.00 sec)
