mysql 锁等待表_MySQL - 锁等待超时与information_schema的三个表

引用地址：https://blog.csdn.net/J080624/article/details/80596958

回顾一下生产中的一次MySQL异常，Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction解决与处理。

【1】抛个异常

异常如下：

Cause: java.sql.SQLException: Lock wait timeout exceeded;

try restarting transaction

1

2

翻译：锁等待超时，尝试重启事务。

【2】information_schema的三个表

information_schema.innodb_trx–当前运行的所有事务，

information_schema.innodb_locks–当前出现的锁

information_schema.innodb_lock_waits–锁等待的对应关系

① information_schema.innodb_trx–当前运行的所有事务

FieldTypeNullKeyDefaultExtra

trx_idvarchar(18)NO事务ID

trx_statevarchar(13)NO事务状态：

trx_starteddatetimeNO0000-00-00 00:00:00事务开始时间；

trx_requested_lock_idvarchar(81)YESNULLinnodb_locks.lock_id

trx_wait_starteddatetimeYESNULL事务开始等待的时间

trx_weightbigint(21) unsignedNO0事务权重

trx_mysql_thread_idbigint(21) unsignedNO0事务线程ID

trx_queryvarchar(1024)YESNULL具体SQL语句

trx_operation_statevarchar(64)YESNULL事务当前操作状态

trx_tables_in_usebigint(21) unsignedNO0事务中有多少个表被使用

trx_tables_lockedbigint(21) unsignedNO0事务拥有多少个锁

trx_lock_structsbigint(21) unsignedNO0

trx_lock_memory_bytesbigint(21) unsignedNO0事务锁住的内存大小(B)

trx_rows_lockedbigint(21) unsignedNO0事务锁住的行数

trx_rows_modifiedbigint(21) unsignedNO0事务更改的行数

trx_concurrency_ticketsbigint(21) unsignedNO0事务并发票数

trx_isolation_levelvarchar(16)NO事务隔离级别

trx_unique_checksint(1)NO0是否唯一性检查

trx_foreign_key_checksint(1)NO0是否外键检查

trx_last_foreign_key_errorvarchar(256)YESNULL最后的外键错误

trx_adaptive_hash_latchedint(1)NO0

trx_adaptive_hash_timeoutbigint(21) unsignedNO0

② information_schema.innodb_locks–当前出现的锁

FieldTypeNullKeyDefaultExtra

lock_idvarchar(81)NO锁ID

lock_trx_idvarchar(18)NO拥有锁的事务ID

lock_modevarchar(32)NO锁模式

lock_typevarchar(32)NO锁类型

lock_tablevarchar(1024)NO被锁的表

lock_indexvarchar(1024)YESNULL被锁的索引

lock_spacebigint(21) unsignedYESNULL被锁的表空间号

lock_pagebigint(21) unsignedYESNULL被锁的页号

lock_recbigint(21) unsignedYESNULL被锁的记录号

lock_datavarchar(8192)YESNULL被锁的数据

③ information_schema.innodb_lock_waits–锁等待的对应关系

FieldTypeNullKeyDefaultExtra

requesting_trx_idvarchar(18)NO请求锁的事务ID

requested_lock_idvarchar(81)NO请求锁的锁ID

blocking_trx_idvarchar(18)NO当前拥有锁的事务ID

blocking_lock_idvarchar(81)NO当前拥有锁的锁ID

开始测试

第一步：创建测试表tx1，并插入测试数据；

create table tx1

(id int primary key ,

c1 varchar(20),

c2 varchar(30)),

c3 datetime

engine=innodb default charset = utf8 ;

insert into tx1 values

(1,'aaaa','aaaaa2',SYSDATE()),

(2,'bbbb','bbbbb2',,SYSDATE()),

(3,'cccc','ccccc2',,SYSDATE());

1

2

3

4

5

6

7

8

9

10

11

第二步：手动开启事务，查询三个表数据

start transaction;

update tx1 set c1='heyf',c2='heyf',c3=SYSDATE() where id =3 ;

select * from information_schema.innodb_trx;

select * from information_schema.INNODB_LOCKS;

select * from information_schema.INNODB_LOCK_WAITS;

1

2

3

4

5

6

7

8

此时没有锁，锁等待关系，只有innodb_trx表中有数据

*************************** 1. row ***************************

trx_id: 75E34

trx_state: RUNNING

trx_started: 2018-06-06 16:55:37

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 235

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 376

trx_rows_locked: 1

trx_rows_modified: 1

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: 10000

1 row in set (1.02 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

第三步：在另一会话中更新该记录，产生锁等待；

start transaction;

update tx1 set c1='heyfffff',c2='heyffffff',c3=SYSDATE()

where id =3 ;

1

2

3

查看innodb_trx表数据：

mysql> select * from information_schema.innodb_trx\G;

*************************** 1. row ***************************

trx_id: 75E35

trx_state: LOCK WAIT##事务状态

trx_started: 2018-06-06 17:02:33

trx_requested_lock_id: 75E35:0:7509:5

trx_wait_started: 2018-06-06 17:02:33

trx_weight: 2

trx_mysql_thread_id: 238

trx_query: update tx1 set c1='heyfffff',c2='heyffffff',c3=SYSDATE() where id =3

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 376

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: 10000

*************************** 2. row ***************************

trx_id: 75E34

trx_state: RUNNING##事务状态

trx_started: 2018-06-06 16:55:37

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 235

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 376

trx_rows_locked: 1

trx_rows_modified: 1

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: 10000

2 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

查看INNODB_LOCKS表数据：

mysql> select * from information_schema.INNODB_LOCKS\G;

*************************** 1. row ***************************

lock_id: 75E35:0:7509:5

lock_trx_id: 75E35

lock_mode: X

lock_type: RECORD

lock_table: `test`.`tx1`

lock_index: `PRIMARY`

lock_space: 0

lock_page: 7509

lock_rec: 5

lock_data: 3

*************************** 2. row ***************************

lock_id: 75E34:0:7509:5

lock_trx_id: 75E34

lock_mode: X

lock_type: RECORD

lock_table: `test`.`tx1`

lock_index: `PRIMARY`

lock_space: 0

lock_page: 7509

lock_rec: 5

lock_data: 3

2 rows in set (0.00 sec)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

查看INNODB_LOCK_WAITS表数据：

mysql> select * from information_schema.INNODB_LOCK_WAITS;\G

*************************** 1. row ***************************

requesting_trx_id: 75E35 ## 请求锁的事务

requested_lock_id: 75E35:0:7509:5 ## 请求锁的锁ID

blocking_trx_id: 75E34 ## 拥有锁的事务

blocking_lock_id: 75E34:0:7509:5 ## 拥有锁的锁ID

1

2

3

4

5

6

在执行第二个update的时候，由于第一个update事务还未提交，故而第二个update在等待，其事务状态为LOCK WAIT，等待时间超过innodb_lock_wait_timeout值时，则会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction异常。

在第二个update锁等待超时之后，对第一个update手动提交事务，则第一个update语句成功更新数据库中数据表。

锁等待递进

如果是多个锁等待，比如有三个update，update同一行记录，则锁等待关系会层级递进，第二个第三个update都保留对第一个update的锁等待且第三个update保留对第二个update的锁等待，如下图：

【3】解决办法

① 查看并修改变量值

show GLOBAL VARIABLES like '%innodb_lock_wait_timeout%';

set GLOBAL innodb_lock_wait_timeout=100;##设置大小值看系统情况

1

2

3

innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间，超过这个时间还未分配到资源则会返回应用失败。参数的时间单位是秒，默认值50S。

② 找到一直未提交事务导致后来进程死锁等待的进程，并杀掉

根据锁等待表中的拥有锁的事务id(blocking_trx_id)，从innodb_trx表中找到trx_mysql_thread_id值，kill掉。

如 这里杀掉 进程235：

select trx_mysql_thread_id from information_schema.innodb_trx it

JOIN information_schema.INNODB_LOCK_WAITS ilw

on ilw.blocking_trx_id = it.trx_id;

##trx_mysql_thread_id: 235

kill 235

③ 优化SQL，优化数据库，优化项目。第一个update未执行完，第二个update就来了，超过等待时间就会报锁等待超时异常。在数据并发项目遇到这种情况概率比较大，这时候就要从项目、数据库、执行SQL多方面入手了。

————————————————

版权声明：本文为CSDN博主「流烟默」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。

原文链接：https://blog.csdn.net/J080624/article/details/80596958