锁表分析及解决方案

锁表

​ 表处于被锁定状态，无法进行后续对表的操作

业务中产生原因

业务逻辑中对表进行insert/update/delete 后未进行commite或者回滚或者退出数据库用户，后续进行insert/update/delete 时发生资源正忙异常（发生锁表），也就是insert/update/delete 操作间隔commit或者回滚或者退出数据库用户时间过长导致

锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）

程序级别原因

sql原因：insert/update/delete操作sql执行时间过长。可能是where条件没有走索引导致的。

业务代码原因：可能insert/update/delete操作后续执行了较为耗时的业务代码逻辑，再进行commit操作

解决方案

1. 优化sql，适当增加索引,让其走索引（减少锁表概率）（常用方案）

2. 优化代码，使其sql的insert/update/delete操作到commit操作的中间代码运行时间短（减少锁表概率），具体点批量执行改为单个执行、优化sql自身的非执行速度， 如果异常对事物进行回滚。（一般是业务需求无法优化）

3. 直接杀掉锁表进程（已经发生死锁，打开死锁）（临时方案，常用）

   事务：

   # 查询事务是否发生死锁
   select * from information_schema.innodb_trx
   # 杀死进程
   kill thread_id;
   

   非事务：

   #查看进程id，然后用kill id杀掉进程
   show processlist;
   SELECT * FROM information_schema.PROCESSLIST；
   #查询正在执行的进程
   SELECT * FROM information_schema.PROCESSLIST where length(info) >0 ;
   #查询是否锁表
   show OPEN TABLES where In_use > 0;
   #查看被锁住的
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
   #等待锁定
   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
   #杀掉锁表进程
   kill 5601
   

企业开发中造成的死锁

由于企业中常常是微服务架构，常常使用SpringBoot的@Transactional注解进行声明式事务管理，大量使用该注解出现死锁的情况。

1. 被事务托管的方法出现异常，而Transactional注解却未指定rollbackFor异常回滚属性，发生死锁

   @Transactional(rollbackFor = Exception.class)
   

2. 业务中，服务A中的某个方法操作了调用了服务B的另一个方法，且服务A的方法被事务管理，而两个方法均操作同一张表，服务A操作表在前，服务B操作表在后，导致死锁。

   分析：因为服务A操作表且被事务管理，未提交前会将该表锁住，此事调用服务B继续操作该表，产生死锁

3. 业务中存在并发操作或者多个方法操作同一张表，发生锁表，等待未响应，某个方法执行时间过于较长时，当超过100秒未结束的事务，会被做超时处理，抛出异常。解决方案，杀死死锁进程

解决方案

1. 运行以下命令，查找提交事务的数据，杀掉线程即可解决

 select * from information_schema.innodb_trx
 kill thread_id;

3. 增加锁等待时间，即增大下面配置项参数值，单位为秒（s）

  innodb_lock_wait_timeout=500

4. 优化存储过程,事务避免过长时间的等待。

可以用下面三张表来查原因-information_schema：

  innodb_trx ## 当前运行的所有事务
  innodb_locks ## 当前出现的锁
  innodb_lock_waits ## 锁等待的对应关系

其他：

1、开始事务（使用@transtion）必须指定超时时间

@Transactional( rollbackFor = Exception.class , isolation = Isolation.REPEATABLE_READ, timeout = 30)

2、事务中存在批量修改、删除的语句的时候，where 条件尽量加索引

3、事务中存在批量修改、删除数据时，尽可能减少事务的执行时间。将代码解耦，不要将很多的更新操作在一个长事务中使用

4、在没必要的情况下，去除@Transactional注解