鎖等待監控

1看看有沒有鎖等待：show status like 'innodb_row_lock%';

db03 [oldguo]>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         | 2     | #曆史鎖等待
+-------------------------------+-------+
5 rows in set (0.12 sec)

db03 [oldguo]>      

2檢視哪個事務在等待（被阻塞了）：

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;