关键词:InnoDB、行锁、行锁等待、行锁等待超时
问题:数据库错误
Lock wait timeout exceeded;try restarting transaction
什么是锁等待(Lock Wait)呢?
当事务A对数据表进行DDL或DML操作时,数据库会为表前加表级的排它锁,添加后其它事务将该表的操作就必须等到事务A提交或回滚后才能开始。
例如:调试时如果对某个含有事务的方法断点的同时重启服务,此时数据库将会产生一个没有commit也没有rollback的事务,重启服务后如果继续执行相同的请求,对相同的数据库记录进行操作,此时产生的事务必须等待之前的事务commit或rollback。
InnoDB存储引擎出现行锁等待进而等待超时的现象?
当MySQL连接会话等待另一个会话持有的互斥锁时,会发生InnoDB引擎表行锁等待的状况。通常情况下,持有互斥锁的连接会话会迅速的执行完相关操作并释放掉持有的互斥锁(事务提交或事务回滚),进而等待的连接会话在行锁等待超时时间到来前获得该互斥行锁,来进行下一步的操作。
但在某些情况下,比如一个实例未感知到来自客户端应用的数据库连接会话已发生中断,持有该互斥锁的会话由于长时间不释放该互斥行锁,此时如果有其它连接会话前来申请该互斥行锁,此时会导致大量的行锁等待于行锁等待超时。
例如:一个事务一直没有提交或回滚,下一个已经提交的事务无法获取互斥行锁,此时就会出现行锁等待,进而产生行锁等待超时。
数据库则会抛错
Lock wait timeout exceeded;try restarting transaction
该错误说明数据库发生了行锁等待超时,但注意这里并非死锁。
MySQL系统库information_schema
中有三张表用于存储事务和锁相关的数据
内置表 | 描述 |
---|---|
innodb_trx | 存储当前运行的所有事务 |
innodb_locks | 存储当前出现的锁的事务 |
innodb_lock_waits | 存储锁等待的事务 |
查看数据库锁并了解数据库锁占用情况,查询当前数据库正在执行的事务。
mysql> SELECT * FROM information_schema.innodb_trx;
字段 | 说明 |
---|---|
trx_id | 事务ID |
trx_state | 事务状态 |
trx_started | 事务开始时间 |
trx_requested_lock_id | 行锁ID |
trx_wait_started | 事务开始等待的时间 |
trx_weight | 事务权重 |
trx_mysql_thread_id | 事务线程ID |
trx_query | SQL查询语句 |
trx_operation_state | 事务当前操作状态 |
trx_tables_in_use | 事务中有多少表被使用 |
trx_tables_locked | 事务拥有多少个锁 |
trx_lock_memory_bytes | 事务锁住的内存大小 |
trx_rows_locked | 事务锁住的行数 |
trx_rows_modified | 事务更改的行数 |
trx_concurrency_tickets | 事务并发票数 |
trx_isolation_level | 事务隔离级别 |
trx_unique_checks | 是否唯一性检查 |
trx_foreign_key_checks | 是否外键检查 |
trx_last_foreign_key_error | 最后的外键错误 |
使用MySQL的KILL
命令杀死trx_state
事务状态为LOCK WAIT
的线程trx_mysql_thread_id
,即可解决。但是如果处于行锁等待的事务一直没有被处理,则下一个事务就会出现Lock wait timeout exceeded;try restarting transaction
行锁等待超时的错误消息。
锁等待超时时间有多久呢?
MySQL自身提供了一个innodb_lock_wait_timeout
的参数来设置InnoDB放弃行锁的时间,默认为50秒。
mysql> SHOW VARIABLES LIKE "%innodb_lock_wait_timeout%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set