mysql锁表原因及解决

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锁表解决

-- 找到超时的表，查询超时的SQL
SELECT * 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';
 
-- 设置全局等待事务锁超时时间
SET  GLOBAL 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代码，找出问题代码