与锁定相关的参数
1、死锁探测
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_deadlock_detect | ON |
| innodb_print_all_deadlocks | OFF |
+----------------------------+-------+
2、锁超时
innodb_lock_wait_timeout = 50 （默认50秒）
与锁定相关的统计
mysql> show global status like '%innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 1 |当前正在等待锁定的数量
| Innodb_row_lock_time | 328560 |锁定的总时间，单位ms
| Innodb_row_lock_time_avg | 32856 |每次等待所花平均时间
| Innodb_row_lock_time_max | 51598 |等待最长的一次所花的时间
| Innodb_row_lock_waits | 10 |产生等待的次数，越少越好
+-------------------------------+--------+
5 rows in set (0.03 sec)
与锁相关的视图
--information_schema
INNODB_TRX ---INNODB事务信息
INNODB_LOCKS ---INNODB锁定信息
INNODB_LOCK_WAITS ---INNODB锁等待信息。8.0后在sys下，并且更详细，集成了INNODB_TRX，INNODB_LOCKS的信息
通过视图定位BLOCKER
5.7在information_schema下,且需要与INNODB_TRX，INNODB_LOCK关连得到更详细的信息
8.0在sys下，信息很详细,下面展示的8.0下的结果
mysql> select * from sys.innodb_lock_waits\G
wait_started: 2020-05-03 06:30:05
wait_age: 00:00:13
wait_age_secs: 13
locked_table: `test`.`t2`
locked_table_schema: test
locked_table_name: t2
locked_table_partition: NULL
locked_table_subpartition: NULL
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 20003 <<<<与INNODB_TRX.trx_id关连
waiting_trx_started: 2020-05-03 05:44:51
waiting_trx_age: 00:45:27
waiting_trx_rows_locked: 8
waiting_trx_rows_modified: 0
waiting_pid: 10 <<<<与INNODB_TRX.trx_mysql_thread_id 关连,PROCESSLIST.id
waiting_query: update t2 set c1='test2' where id=1
waiting_lock_id: 140293259925328:4:4:2:140293160620944
waiting_lock_mode: X,REC_NOT_GAP <<<<
blocking_trx_id: 20001 <<<<
blocking_pid: 8 <<<<
blocking_query: NULL
blocking_lock_id: 140293259926240:4:4:2:140293160624488
blocking_lock_mode: X
blocking_trx_started: 2020-05-03 05:43:44
blocking_trx_age: 00:46:34
blocking_trx_rows_locked: 5
blocking_trx_rows_modified: 0
sql_kill_blocking_query: KILL QUERY 8
sql_kill_blocking_connection: KILL 8
mysql 5.7
select requesting_trx_id,requested_lock_id
,ta.trx_query requesting_trx_query
,blocking_trx_id,blocking_lock_id,
tb.trx_state blocking_trx_state,tb.trx_started blocking_trx_started,tb.trx_query blocking_trx_query,concat('kill ',tb.trx_mysql_thread_id,';' )
from INNODB_LOCK_WAITS a,INNODB_TRX ta,INNODB_TRX tb where a.requesting_trx_id=ta.trx_id and a.blocking_trx_id=tb.trx_id;
+-------------------+-------------------+------------------------------+-----------------+------------------+--------------------+----------------------+--------------------+--------------------------------------------------------+
| requesting_trx_id | requested_lock_id | requesting_trx_query | blocking_trx_id | blocking_lock_id | blocking_trx_state | blocking_trx_started | blocking_trx_query | concat('kill ',tb.trx_mysql_thread_id,';' ) |
+-------------------+-------------------+------------------------------+-----------------+------------------+--------------------+----------------------+--------------------+--------------------------------------------------------+
| 1601195 | 1601195:99:3:4 | insert into t values (6,6,6) | 1601194 | 1601194:99:3:4 | RUNNING | 2020-05-12 17:42:48 | NULL | kill 68489; |
+-------------------+-------------------+------------------------------+-----------------+------------------+--------------------+----------------------+--------------------+--------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
通过show engine innodb stuatus定位
------------
TRANSACTIONS
------------
Trx id counter 1601171
Purge done for trx's n:o < 1601165 undo n:o < 0 state: running but idle
History list length 23
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421281351343728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1601170, ACTIVE 30 sec starting index read <<<<waiter trx id 1601170,ACTIVE 309 活动事务时长
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 68481, OS thread handle 139806317295360, query id 205480 localhost root updating
update t111 set c2=c2 where c1='aaa' <<<<waiter current sql
------- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: <<<<<等待时长
RECORD LOCKS space id 102 page no 3 n bits 72 index PRIMARY of table `test`.`t111` trx id 1601170 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 ---下面为dump发生锁等待的row #rowid(主键)+DB_TRX_ID+DB_ROLL_PTR+其他字段
0: len 3; hex 616161; asc aaa;; <<<<col 1 c1 'aaa' primary key
1: len 6; hex 000000186e91; asc n ;; <<<<DB_TRX_ID 持有LOCK的 trx_id (1601169),可以找到blocker transaction
2: len 7; hex fa000007280110; asc ( ;; <<<<DB_ROLL_PTR (segment id\page no\offset) Decodes a roll pointer. */ //从高位到低位依次是 第1位是否是insert //第2到8位是segmentid//第9到40位为page no //第41位到56位为OFFSET
3: len 3; hex 626262; asc bbb;; <<<<col 2 c2 'bbb'
------------------
---TRANSACTION 1601169, ACTIVE 64 sec <<<< blocker transaction
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 68482, OS thread handle 139806317029120, query id 205478 localhost root
死锁分析
show engine innodb stuatus 可以显示最后产生的死锁阻塞dump信息
LATEST DETECTED DEADLOCK
2020-05-11 15:15:47 0x7f2731dd2700
*** (1) TRANSACTION:
TRANSACTION 1601176, ACTIVE 125 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 68481, OS thread handle 139806317295360, query id 205501 localhost root updating
update t111 set c2=c2 where c1=‘ccc’
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: <<<<<
RECORD LOCKS space id 102 page no 3 n bits 72 index PRIMARY of table test.t111 trx id 1601176 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 636363; asc ccc;;
1: len 6; hex 000000186e93; asc n ;;
2: len 7; hex fc0000092c0110; asc , ;;
3: len 3; hex 646464; asc ddd;;
*** (2) TRANSACTION:
TRANSACTION 1601177, ACTIVE 62 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 68482, OS thread handle 139806317029120, query id 205502 localhost root updating
update t111 set c2=c2 where c1=‘aaa’
*** (2) HOLDS THE LOCK(S): <<<<<<
RECORD LOCKS space id 102 page no 3 n bits 72 index PRIMARY of table test.t111 trx id 1601177 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 636363; asc ccc;;
1: len 6; hex 000000186e93; asc n ;;
2: len 7; hex fc0000092c0110; asc , ;;
3: len 3; hex 646464; asc ddd;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 102 page no 3 n bits 72 index PRIMARY of table test.t111 trx id 1601177 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 3; hex 616161; asc aaa;;
1: len 6; hex 000000186e91; asc n ;;
2: len 7; hex fa000007280110; asc ( ;;
3: len 3; hex 626262; asc bbb;;
*** WE ROLL BACK TRANSACTION (2 <<<<<
