问题
使用的数据库为mysql,而InnoDB表类型会出现锁等待的情况,在出现锁等待时,会根据参数innodb_lock_wait_timeout(默认50s)的配置,判断是否需要进行timeout的操作,如果等待时间超过了设置的时间就会报错。
information_schema查询数据库使用情况
information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问等等信息都保存在information_schema表里面。
- innodb_trx: 当前运行的所有事务
- innodb_locks:当前出现的锁
- innodb_lock_waits:锁等待的对应关系
select * from information_schema.innodb_trx
show full processlist; # 查看mysql的所有线程
#trx_mysql_thread_id对应线程中的id,知道哪条语句锁了
kill 这个id
锁等待超时可能出现的情况
- 同一事务内先后对同一条数据进行插入和更新操作。
- 多台服务器操作同一数据库。
- 瞬时出现高并发现象,spring事务造成数据库死锁,后续操作超时抛出异常。
- 事务A对记录C进行更新/删除操作的请求未commit时,事务B也对记录C进行更新/删除操作。此时,B会等A提交事务,释放行锁。当等待时间超过innodb_lock_wait_timeout设置值时,会产生“LOCK WAIT”事务。
- 数据库内存不足,导致无法执行写操作。
解决办法
- innodb_lock_wait_timeout 锁定等待时间改大
修改超时时间将 #innodb_lock_wait_timeout = 50 修改为 innodb_lock_wait_timeout = 500。
缺点:全局更改,影响也是全局的,等待时间加长,容易使等待事务增多导致堆积问题。
- kill掉事务
通过SELECT * FROM information_schema.innodb_trx查询未提交事务,查到一个一直没有提交的只读事务(trx_state=”LOCK WAIT”),找到对应线程,执行:kill 线程ID。线程id为表中的trx_mysql_thread_id字段。
- kill耗时任务久的线程
执行SELECT * from information_schema.
PROCESSLIST
WHERE Time > 1000 AND USER = ‘xxx’ ORDER BY TIME desc;找到线程:kill 线程ID。
- 优化锁表的事务
根据实际业务进行解决,举例(网上找的):司机APP进行运单签收,需要对et_waybill_info表某些记录进行更新操作。一直处于锁等待状态,直到超时报错。经排查发现:系统定时器定时执行任务,将所有未标识亮的已装车或签收的运单,按批次处理,如果运单装车了但长时间未上传GPS、温湿度等信息,会一直被定时器处理。数据量越积越大,队列长时间等待,对et_waybill_info表锁住没有释放,致使签收要操作et_waybill_info表无法拿到锁,进行数据操作。
临时解决方案:停掉定时器任务。
根本解决方案:优化定时器。