Lock wait timeout exceeded; try restarting transaction

时间 2021-01-06

问题出现的原因：假设有两个事务A和事务B,他们两个都存在update 同一条记录，A 先修改，但是没有提交事务，B也想修改但是一直等，直到等到了超过了innodb_lock_wait_timeout所设置的时间，就会爆出此异常

模仿异常的出现：

创建一个表：

CREATE TABLE `emp` (
`id` int(4) NOT NULL,
`deptno` int(4) DEFAULT NULL,
`col3` int(4) DEFAULT NULL,
`col4` int(4) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

并在表中随便插入几条数据。

用navicate 连接上mysql服务，并设置客户端set autocommit=0 ，用mysql-client 也连接上服务并使用相应的数据库，并设置set autocommit=0 。

在第一个客户端执行

START TRANSACTION;
update emp SET deptno=2 WHERE id=2; 此时不要commit；

在第二个客户端执行对同一条记录的修改，如

START TRANSACTION;

update emp SET deptno=2 WHERE id=2;

此时执行结果如下图：

第一个图中的update 语句执行完成后并未commit；接着第二个执行语句执行后会发发现一直在运行，并没有停止，直到出现

[SQL]update emp set deptno=2,col3=6 WHERE id=1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

那么如果出现这种情况该怎么处理呢？

方案一：

show prosseslist; 然后kill 掉对应的进程就可以了，查看sql服务中的所有的进程可暂时解决问题，但是如果进程偏多的话就难以查看了

方案二：

在5.5中，information_schema库中增加了三个关于锁的表（MEMORY引擎）；
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

下面看下各个表的简介：

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 | |#被锁的数据

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 ||#

有了这三个表就可以在事故发生时查看表中的记录

根据当前所有运行的时候可以清楚的找到等待的事务即没有获取锁的事务，这样的就可能是发生timeout 的原因所在

根据锁的等待关系，可以看出正在等待的事务id。

事务等待锁的超时的异常一般是事务未提交导致的，比如一个系统正在运行还未提交事务但是此时这个系统突然间宕掉了，那么如果此时别的系统的事务也在请求同一条记录的锁，那么就会出现事务等待锁，这种可以从系统代码优化方面来考虑如何避免这种情况。

感谢观看。