mysql死锁

1、什么是mysql死锁?
官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。
举例:这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人。你让对面放人,对面让你放人,最后谁都不放,一直等待。

在实际应用中,一个表中有两条数据id=1和id=2,然后有两个事务T1和T2,首先T1先执行id=1的更新操作,然后要更新id=2的数据,但是更新id=2时被T2事务占用,导致无法获取到锁。T2首先执行id=2的更新操作,然后要更新id=1的数据,但是在更新id=1时被T1事务占用。导致获取到锁。这样无线等待就变成死锁
mysql死锁

MySQL有两种死锁处理方式:

  1. 等待,直到超时(innodb_lock_wait_timeout=50s)。
  2. 发起死锁检测,主动回滚一条事务,让其他事务继续执行(innodb_deadlock_detect=on)。

由于性能原因,一般都是使用死锁检测来进行处理死锁。

如何避免死锁

收集死锁信息:
 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
调试阶段开启 innodb_print_all_deadlocks,收集所有死锁日志。

减少死锁:
1、为了在单个InnoDB表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT ... FOR UPDATE语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。

2、在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁

3、如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会
通过SELECT ... LOCK IN SHARE MODE获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。

4、改变事务隔离级别


 

MyISAM避免死锁

在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。