mysql锁表原因及解决

问题如图

锁表发生原因

  • 锁表发生在 insert、update、delete中；
  • 锁表的原理是数据库使用独占式锁机制，当执行上面的语句时，对表进行锁住，直到发生commit或者rollback或者退出数据库用户；
  • 锁表的原因：
    • A程序执行了对table_1的insert、update、delete，并还未commit时，B程序也对table_1进行insert、update、delete`时会发生资源正忙的异常，也就是锁表；
    • 锁表常发生与并发而不是并行（并行时，一个线程操作数据库时，另一个线程是能操作数据库的，cpu和i/o分配原则）
    • 锁表也发生在事务嵌套，外层事务对table_1进行了insert、update、delete，内层事务（PROPAGATION_REQUIRES_NEW）也对table_1进行了insert、update、delete，内层事务commit的时需要等待外层事务先commit释放资源（但是是不可能的），最终导致死锁（本次问题就是事务嵌套导致）。多查几次SELECT * FROM information_schema.innodb_trx ;如果锁跟着业务结束（connect超时）锁没了，那么基本上可以确定是业务代码导致，需要分析业务代码。

mysql锁表解决

-- 找到超时的表，查询超时的SQLSELECT * FROM information_schema.innodb_trx ;-- 查看当前被使用的表，查询是否有锁表-- SHOW OPEN TABLES：列举在表缓存中当前被打开的非TEMPORARY表。-- In_use：表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。show OPEN TABLES where In_use > 0;
-- 查询全局等待事务锁超时时间SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 设置全局等待事务锁超时时间SETGLOBAL innodb_lock_wait_timeout=100;-- 查询当前会话等待事务锁超时时间SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';-- 查看进程id，然后用kill id杀掉进程show processlist;SELECT * FROM information_schema.PROCESSLIST;-- 查询正在执行的进程SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;-- 查看被锁住的SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;-- 等待锁定SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;-- innodb_locks表在8.0.13版本中由performance_schema.data_locks表所代替，innodb_lock_waits表则由performance_schema.data_lock_waits表代替-- 杀掉锁表进程kill 5601

事务嵌套引起的死锁

这时候就不能简单的kill掉进程了，需要review代码，找出问题代码