数据库锁等待超时与资源限制问题排查与解决-优快云博客

本文链接：https://blog.youkuaiyun.com/BushQiang/article/details/128380871

问题背景

今天测试同事发现项目里面大部分接口报错，把日志捞出来看了下出现大量的锁等待超时的错误。

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)

这个提示就是数据库锁等待超时。

排查问题

排查问题主要用到的sql，可以参考文章最后相关表的说明

## 查引擎日志
show engine innodb status;
## 当前运行的所有事务
select * from information_schema.innodb_trx;   
## 当前出现的锁
select * from information_schema.innodb_locks;  
## 锁等待的对应关系
SELECT * FROM information_schema.innodb_lock_waits;
## innodb_trx.trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN
SELECT * from information_schema.processlist WHERE id = xxx; 
## 展示所有的连接数据
show full processlist;

trx_state状态为RUNNING就表示这个事物还在运行中，没有提交。LOCK_WAIT就是被阻塞了，需要等前面RUNNING的提交了才能到自己这边运行。这边可以看到UPMS_USER表就是这个情况。

可以通过kill trx_mysql_thread_id来杀掉这个线程暂时解决这个问题，但是这样只是解决一时的问题。我们还需要找到发生问题的具体原因。

在这里插入图片描述

这几个事物都被上锁了

在这里插入图片描述

锁的等待关系，可以对照innodb_lock_waits表来看。

在这里插入图片描述

查一下

## xxx为innodb_trx.trx_mysql_thread_id：事务线程 ID
SELECT * from information_schema.processlist WHERE id = xxx;

结果发现报错了。mysql的tmpdir空间不够。tmpdir主要用来存储一些中间的临时数据。

### Cause: java.sql.SQLException: Disk full (/data/mysql/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1021]; Disk full (/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space... (errno: 28 "No space left on device"); nested exception is java.sql.SQLException: Disk full (/data/mysql/tmp/#sql_1eaa2_60.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
    at

很明显就是磁盘空间不足了。

查看磁盘空间，发现已经使用100%了。

在这里插入图片描述

清理磁盘空间

除了上面的磁盘空间不足会引起这个问题，还可能是有大的事物导致执行时间过长，阻塞了其它的请求。遇到这种情况就需要优化业务，可以把大的事物进行拆分。通过减少事物持有锁的时间来解决问题。

errno: 11 "Resource temporarily unavailable

上面把磁盘空间清理后，发现项目的接口还是报错。报错日志如下。

### Cause: java.sql.SQLException: Error writing file '/data/mysql/binlog/bin-log-mysqld' (errno: 11 "Resource temporarily unavailable")
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [1026]; Error writing file '/data/mysql/binlog/bin-log-mysqld' (errno: 11 "Resource temporarily unavailable"); nested exception is java.sql.SQLException: Error writing file '/data/mysql/binlog/bin-log-mysqld' (errno: 11 "Resource temporarily unavailable")
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.3.6.RELEASE.jar:4.3.6.RELEASE]
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-2.0.1.jar:2.0.1]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[mybatis-spring-2.0.1.jar:2.0.1]
	at com.sun.proxy.$Proxy140.update(Unknown Source) ~[?:?]
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294) ~[mybatis-spring-2.0.1.jar:2.0.1]

去看了msyql的日志，定位到刚开始报错的位置

2022-12-19  5:52:31 140613203285760 [Warning] mysqld: 2022-12-19 13:59:07 140613203285760 [ERROR] mysqld: Error writing file '/data/mysql/bin-log-mysqld' (errno: 28 "No space left on device")
2022-12-19 13:59:07 140612815456000 [ERROR] mysqld: Error writing file '/data/mysql/bin-log-mysqld' (errno: 28 "No space left on device")
2022-12-19 13:59:07 140613207586560 [ERROR] mysqld: Error writing file '/data/mysql/bin-log-mysqld' (errno: 28 "No space left on device")
2022-12-19 13:59:07 140612812076800 [ERROR] mysqld: Error writing file '/data/mysql/bin-log-mysqld' (errno: 28 "No space left on device")
2022-12-19 13:59:07 140617973192448 [ERROR] mysqld: Error writing file '/data/mysql/bin-log-mysqld' (errno: 28 "No space left on device")
2022-12-19 13:59:07 140611924596480 [ERROR] mysqld: Error writing file '/data/mysql/binlog/bin-log-mysqld' (errno: 11 "Resource temporarily unavailable")

发现最早是因为磁盘空间不足导致无法写入bin-log，但是我们已经清理过磁盘了。

重启mysql就没有在报这个错误了。

字段描述

innodb_trx 表的字段描述：

字段	描述
trx_id	事务ID
trx_state	事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
trx_started	事务开始时间。
trx_requested_lock_id	事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
trx_wait_started	事务开始等待的时间。
trx_weight	事务的权重。
trx_mysql_thread_id	事务线程 ID，可以和 PROCESSLIST 表 JOIN。
trx_query	事务正在执行的 SQL 语句。
trx_operation_state	事务当前操作状态。
trx_tables_in_use	当前事务执行的 SQL 中使用的表的个数。
trx_tables_locked	当前执行 SQL 的行锁数量。
trx_lock_structs	事务保留的锁数量。
trx_lock_memory_bytes	事务锁住的内存大小，单位为 BYTES。
trx_rows_locked	事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
trx_rows_modified	事务更改的行数。
trx_concurrency_tickets	事务并发票数。
trx_isolation_level	当前事务的隔离级别。
trx_unique_checks	是否打开唯一性检查的标识。
trx_foreign_key_checks	是否打开外键检查的标识。
trx_last_foreign_key_error	最后一次的外键错误信息。
trx_adaptive_hash_latched	自适应散列索引是否被当前事务锁住的标识。
trx_adaptive_hash_timeout	是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

innodb_locks 表的字段描述

字段	描述
lock_id	锁 ID
lock_trx_id	拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
lock_mode	锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
lock_type	锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
lock_table	被锁定的或者包含锁定记录的表的名称。
lock_index	当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
lock_space	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
lock_page	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
lock_rec	当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
lock_data	当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

innodb_lock_waits 字段描述

字段	描述
requesting_trx_id	请求事务的 ID。
requested_lock_id	事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。
blocking_trx_id	阻塞事务的 ID。
blocking_lock_id	某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。