当我们跑MySQL服务器查看日志的时候,如果发现如下类似日志:

171013 2:04:25 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Lock wait timeout exceeded; try restarting transaction
171013 2:06:26 [ERROR] /usr/local/mysql/bin/mysqld: Lock wait timeout exceeded; try restarting transaction
171013 2:06:26 [ERROR] /usr/local/mysql/bin/mysqld: Sort aborted: Lock wait timeout exceeded; try restarting transaction
171013 2:07:33 [ERROR] /usr/local/mysql/bin/mysqld: Lock wait timeout exceeded; try restarting transaction
…..

出现这些日志主要原因往往是,有些事务执行过dml操作,然后一直没commit/rollback,新事务一直等待旧事务释放锁资源,出现锁等待超时,即新事务等待时间超过了mysql设置的lock_wait_timeout(指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败,参数的时间单位是秒,最小可设置为1s)该参数设定的值后,mysql又尝试重新执行此事务。

有时候看看show engine innodb status , 并结合 show global full processlist; 能暂时解决问题;但一直不能精确定位;

在5.5以上版本中,information_schema 库中增加了三个关于锁的表(MEMORY引擎);
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

root> 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> 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> 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)

接下来我们动手测试下:
##建立测试库,和测试表,关闭自动提交
mysql> set @@autocommit=off;
mysql> create database test;
mysql> use test;
mysql> create table test as select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ROW_FORMAT,TABLE_ROWS,AVG_ROW_LENGTH, MAX_DATA_LENGTH from information_schema.tables limit 1,10;
mysql> desc test;
+—————–+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+———————+——+—–+———+——-+
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
+—————–+———————+——+—–+———+——-+
7 rows in set (0.00 sec)
表数据如下:

产生事务;

Session1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set table_schema=’test’ where row_format=’Fixed’;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
mysql> select * from information_schema.innodb_trx \G;

*** 1. row ***
trx_id: 3378
trx_state: RUNNING
trx_started: 2017-11-13 15:40:45
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 10
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: 11
trx_rows_modified: 8
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

trx_state为running

由于没有产生锁等待,下面两个表没有数据 ;

mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)
mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)

产生锁等待

Session 2

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set table_schema=’information’ where row_format=’Fixed’;

等待中

再查看下面三个视图信息

mysql> select * from information_schema.innodb_trx \G;
*** 1. row ***
trx_id: 3379 ##第二个事务
trx_state: LOCK WAIT ## 处于等待状态
trx_started: 2017-11-13 15:42:03
trx_requested_lock_id: 3379:29:3:36 ##请求的锁ID
trx_wait_started: 2017-11-13 15:42:03
trx_weight: 2
trx_mysql_thread_id: 7
trx_query: update test set table_schema=’information’ where row_format=’Fixed’ ##处于等待锁的SQL
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: 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. row ***
trx_id: 3378
trx_state: RUNNING
trx_started: 2017-11-13 15:40:45
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 10
trx_mysql_thread_id: 6
trx_query: select * from information_schema.innodb_trx
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: 11
trx_rows_modified: 8
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
mysql> select * from information_schema.innodb_locks;
+————–+————-+———–+———–+—————+—————–+————+———–+———-+—————-+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+————–+————-+———–+———–+—————+—————–+————+———–+———-+—————-+
| 3379:29:3:36 | 3379 | X | RECORD | test.test | GEN_CLUST_INDEX | 29 | 3 | 36 | 0x000000000202 |
| 3378:29:3:36 | 3378 | X | RECORD | test.test | GEN_CLUST_INDEX | 29 | 3 | 36 | 0x000000000202 |
+————–+————-+———–+———–+—————+—————–+————+———–+———-+—————-+
2 rows in set, 1 warning (0.00 sec)

3379:29:3:36为第二个事务需要的锁,3378:29:3:36为第一个事务需要的锁

mysql> select * from information_schema.innodb_lock_waits;
+——————-+——————-+—————–+——————+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+——————-+——————-+—————–+——————+
| 3379 | 3379:29:3:36 | 3378 | 3378:29:3:36 |
+——————-+——————-+—————–+——————+
1 row in set, 1 warning (0.00 sec)
requesting_trx_id:3379 ##请求锁的事务
requested_lock_id :3379:29:3:36 ##请求锁的锁ID
blocking_trx_id :3378 ##拥有锁的事务
blocking_lock_id :3378:29:3:36 ##拥有锁的锁ID

最后session2由于等待session1的没有commit,等待超时,最后显示以下信息:

mysql> update test set table_schema=’information’ where row_format=’Fixed’;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
可以通过kill临时处理,kill 6,即可释放锁,或者再事务后面追加commit进行提交即可。