Mysql的等待锁和死锁

问题：

       下午因为钉钉服务异常的问题，开发的微应用一直报jsapi鉴权失败，同时查看日志的时候还发现有锁等待的错误日志。报的错误信息为：java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction，这是事物超时报的错，跟踪排查了下，发现原来从待处理消息表中处理消息发送给钉钉接口时，钉钉接口访问超时，在超时失败前，事物会一直等待，这就造成其他线程再往消息表插数据时失败，从而报了上面的错误。

        Mysql查询事务列表、锁等待相关的sql如下：

show full processlist;
select * from information_schema.INNODB_TRX;  #当前运行的所有事物
select * from information_schema.INNODB_LOCKS;  #当前出现的锁
select * from information_schema.INNODB_LOCK_WAITS; #锁等待的对应关系
#事物超时的时间，默认50s，即放弃行级锁的超时时间
show VARIABLES like 'innodb_lock_wait_timeout';
#下面两行，如果设置为ON，则innoDB会自动检测死锁进行回滚，或者终止死锁的情况。
show VARIABLES like 'innodb_table_locks'; 
show VARIABLES like 'autocommit';

       关于事物超时的相关知识，mysql数据库自身有一个默认配置参数innodb_lock_wait_timeout来设置InnoDb放弃行级锁的时间，默认是50s。而通过Spring的@Transaction来配置的超时时间发现实际超时时间往往会更差一点，具体移步这里查看。

MySql锁

关于Mysql锁的情况，假设存在以下表：

用户表(user)

字段	说明	备注
id	用户id	主键，不能为空
name	用户姓名	姓名信息

 

 

 

 

1.共享锁

select * from user where id='1' lock in share mode;

共享锁又称为读锁，简称S锁，表示多个事物对于同一数据可以共享一把锁，都能访问到该数据，但是只能读不能修改。不加共享锁的话，数据会被其他事物修改。这是区别

2.排它锁

update user set name='zhangsan' where id='1';

select * from user for update;

排它锁又称为写锁，简称X锁，表示只能自己进行增删改查，其他的事物只能等待。

3.表级锁

update user set name='zhangsan' where name='wangwu';

把非主键作为条件的排它锁就是表级锁，会把整个表都锁到，其实也算是悲观锁。

4.行级锁

update user set name='zhangsan' where id='1';

把主键作为条件的排它锁是行级锁，这个我个人认为也算是乐观锁，通过这种方式进行更新，不影响表其他数据的读写。

5.所等待超时

Lock wait timeout exceeded; try restarting transaction

T1:  mysql>begin update user set name ='zhangsan' where id='5'

T2：mysql>begin update user set name='lisi' where id='5'

事物T1占着行级锁，不commit，那么T2就要一直等，直到T1commit了，或者超过50s超时了。如果超时了，就报上面的错误。

6.死锁

Deadlock found when trying to get lock;

T1:

mysql>begin;

mysql>update user set name='zhangsan' where id='5';

mysql>update user set name='lisi' where name='zh';

T2：

mysql>begin;

mysql>update user set name='ww' where id='5'

假如T1和T2同时执行，首先T1会占有行级锁，然后T2会排队等待T1释放行级锁，而事物T1的第二条表锁更新语句会等待事物2释放行级锁，这就出现T1等T2，T2等T1的情况，然后就死锁了。

T1一直没commit，那么T2就会一直等T1，但是T1第二条语句想执行，就要等T2commit，这是死锁出现的情况。

Mysql数据库的内部机制会自动重启事物T2，解决死锁问题，保证T1正常进行，但是oracle会死锁等待。

参考：https://blog.csdn.net/baidu_34217927/article/details/78601665

 

另：

   在钉钉开发中，非常容易出现异常的操作，钉钉的微应用允许同一个用户同时PC在线和手机在线。我们的业务中有这样的场景，用户要处理问单，他同时在PC上和手机上都打开问单，在手机上对问单进行回复操作，此时问单状态已发生变化了，问单已终结了。但是他立马又在PC上对问单进行错单转移的操作。但其实问单已经是处理成功了，再进行其他的操作都是错误的。为了避免出现这种情况，在进行处理事务中，对于更新问单状态的sql使用乐观锁，通过加问单id和问单状态为条件进行update，从而避免出现这种异常情况。