mysql中的死锁问题

2019独角兽企业重金招聘Python工程师标准>>>

本文解决的问题主要有： 1.什么是死锁？ 2.如何产生死锁的？ 3.如何预防死锁？ 4.mysql产生死锁后如何处理？

什么是死锁？

   死锁就是当mysql服务器有多个事务在执行的时候，因为资源（这里就指表中的数据）访问产生相互等待的情况，如果没有外界干预，将会一直等待下去。

如何产生死锁？

    假如有一张用户评论表，使用了Innodb引擎

id	userId	content
1	cbz8kel	感觉还不错！
2	almmdds	差评。。。
3	kiwwqss	物流太慢了啊。
4	freelwq	衣服质量不错
5	kiwwqss	追加评论，质量还不怎么样

id为主键(默认使用聚簇索引，主键索引的叶节点存储行数据)，userId创建非唯一索引，叶节点存储的主键值。

userId的索引结构：

userId  | id
---|---
almmdds | 2
cbz8kel | 1
freelwq | 4
kiwwqss | 3
kiwwqss | 5

索引和锁的关系：
    
    delete from tb_user_content where id = 1
    
        由于id是主键，会直接锁住id=1的正行记录
    
    delete from tb_user_content where userId = kiwwqss
        
        由于userId是二级索引，会先锁住索引中userId为kiwwqss的记录，这里会锁两条索引记录，然后去锁住表中id=3和id=5的记录。
    
    delete from tb_user_content where content = ' 差评。。。'
        由于content没有索引，因此会锁住整张表

死锁产生的原因：

1.事务A和事务B操作两张表，顺序不同可能引发的死锁：

事务A	事务B
begin	begin
delete from tb_1 where id=1
	update tb_user_content where userId ="freelwq"
update tb_user_content where userId ="freelwq"
	delete from tb_1 where id=1

在事务A中先删除tb_1表中id=1的数据，首先锁住id=1的行，由于事务没有提交，锁会一直持有

事务B中更新tb_user_content userId="freelwq"的记录，就会锁住表中userId="freelwq"的记录，事务提交前也会持有该锁。

事务A中尝试更新tb_user_content中userId为“freelwq”的记录，由于该记录被事务B锁住，所以会等待事务B释放该锁。

事务B尝试删除tb_1中id=1的记录，由于该锁被事务A获取没有释放，所以会一直等待事务A释放该锁

事务A和事务B会相互等待，产生了死锁问题

事务A和事务B操作同一张表，执行顺序不同产生的死锁

事务A	事务B
begin	begin
update tb_user_content where id=1
	update tb_user_content where id = 2
update tb_user_content where id=2
	update tb_user_content where id = 1

事务A更新id=1的记录时锁住该行，事务B锁住id=2的行，事务A和事务B都没有提交，因此会一直持有锁，当事务A去更新id=2的记录时，id=2的记录被事务B锁住，因此会等待，同样，事务B去更新id=1的记录时也会等待事务A释放锁，因此就产生了死锁问题。

不同索引引发的死锁问题

事务A	事务B
begin	begin
update tb_user_content where userId>'a'
	delete from tb_user_content where id > 1

事务A的加锁顺序和事务B的加锁顺序不同，可能导致死锁问题。类似于上面的死锁

如何避免死锁：

1.固定顺序访问，例如将上面事务A的删除和更新操作修改为和事务B相同，就能避免死锁
2.拆分较大的事务，事务大的情况下比较容易产生死锁，
3.同一个事务中，尽量一次锁定所有的访问资源，减小死锁的概率。
4.降低隔离级别。
5.为表创建合理的索引，如果不走索引，就会全表扫描，容易产生死锁。

解除正在死锁的状态有两种方法：

第一种： 1.查询是否锁表 show OPEN TABLES where In_use > 0; 2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程） show processlist 3.杀死进程id（就是上面命令的id列） kill id

第二种： 1.查看下在锁的事务 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 2.杀死进程id（就是上面命令的trx_mysql_thread_id列） kill 线程ID

转载于:https://my.oschina.net/u/2477500/blog/1586244