mysql中如何檢視表是否被鎖問題

use dbName;  // 切換到具體資料庫
show engine innodb status;  // 查詢db是否發生死鎖

show OPEN TABLES where In_use > 0; 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

innodb_trx ## 當前執行的所有事務
innodb_locks ## 當前出現的鎖
innodb_lock_waits ## 鎖等待的對應關係

root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field       | Type                | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id     | varchar(81)         | NO   |     |         |       |#鎖ID
| lock_trx_id | varchar(18)         | NO   |     |         |       |#擁有鎖的事務ID
| lock_mode   | varchar(32)         | NO   |     |         |       |#鎖模式
| lock_type   | varchar(32)         | NO   |     |         |       |#鎖型別
| lock_table  | varchar(1024)       | NO   |     |         |       |#被鎖的表
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |#被鎖的索引
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的表空間號
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的頁號
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |#被鎖的記錄號
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |#被鎖的資料
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field             | Type        | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO   |     |         |       |#請求鎖的事務ID
| requested_lock_id | varchar(81) | NO   |     |         |       |#請求鎖的鎖ID
| blocking_trx_id   | varchar(18) | NO   |     |         |       |#當前擁有鎖的事務ID
| blocking_lock_id  | varchar(81) | NO   |     |         |       |#當前擁有鎖的鎖ID
+——————-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
   
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field                      | Type                | Null | Key | Default             | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id                     | varchar(18)         | NO   |     |                     |       |#事務ID
| trx_state                  | varchar(13)         | NO   |     |                     |       |#事務狀態：
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |#事務開始時間；
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |#innodb_locks.lock_id
| trx_wait_started           | datetime            | YES  |     | NULL                |       |#事務開始等待的時間
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |#事務執行緒ID
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |#具體SQL語句
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |#事務當前操作狀態
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |#事務中有多少個表被使用
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |#事務擁有多少個鎖
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |#
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |#事務鎖住的記憶體大小（B）
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |#事務鎖住的行數
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |#事務更改的行數
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |#事務並行票數
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |#事務隔離級別
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |#是否唯一性檢查
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |#是否外來鍵檢查
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |#最後的外來鍵錯誤
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |#
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |#
+—————————-+———————+——+—–+———————+——-+
22 rows in set (0.01 sec)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS.

show status like '%lock%';

show processlist

# 檢視表鎖的情況：
mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 11 |
+----------------------------+---------+

# 檢視InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 159372 |
| Innodb_row_lock_time_avg      | 39843  |
| Innodb_row_lock_time_max      | 51154  |
| Innodb_row_lock_waits         | 4      |
+-------------------------------+--------+
5 rows in set (0.01 sec)

mysql>

id：integer	
token：varchar	
message：varchar