解决:InnoDB会自动检测事务死锁,立即回滚其中某个事务,并且返回一个错误。它根据某种机制来选择那个最简单(代价最小)的事务来进行回滚
死锁场景一之select for update:
产生场景:两个transaction都有两个select for update,transaction a先锁记录1,再锁记录2;而transaction b先锁记录2,再锁记录1
写锁:for update,读锁:for my share mode show engine innodb status
验证下死锁的场景:
第一步更新会话一:
start TRANSACTION; select * from wnn_test where a=199 for update;
第二步更新会话二:
start TRANSACTION; select * from wnn_test where a=101 for update;
第三步更新会话一:
select * from wnn_test where a=101 for update;
第四步更新会话二;
select * from wnn_test where a=199 for update;
在更新到第三步和第四步的时候,已经发生了死锁。
来看下执行的日志:
show engine innodb status;最后一个锁的时间,锁的表,引起锁的语句。其中session1被锁 14秒(ACTIVE 14),session 2被锁了10秒(Active 10)
死锁场景二之两个update
产生场景:两个transaction都有两个update,transaction a先更新记录1,再更新记录2;而transaction b先更新记录2,再更新记录1
产生日志:
注意:仔细查看上面2个例子可以发现一个现象,当2条资源锁住后,再执行第三个会执行成功,但是第四个会提示死锁。在mysql5.7中,执行第三个的时候就会一直在Running状态了,本博文使用的是mysql8.0 ,其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测,当启用了死锁检测时(默认设置),InnoDB 自动执行事务的死锁检测,并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务,事务的大小由它所插入、更新或者删除的数据行数决定。
那么这个innodb_deadlock_detect参数,到底要不要启用呢?
对于高并发的系统,当大量线程等待同一个锁时,死锁检测可能会导致性能的下降。此时,如果禁用死锁检测,而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说,应该启用死锁检测,并且在应用程序中尽量避免产生死锁,同时对死锁进行相应的处理,例如重新开始事务。只有在确认死锁检测影响了系统的性能,并且禁用死锁检测不会带来负面影响时,可以尝试关闭 innodb_deadlock_detect 选项。另外,如果禁用了 InnoDB 死锁检测,需要调整参数 innodb_lock_wait_timeout 的值,以满足实际的需求。
四、程序开发过程中应该如何注意避免死锁
锁的本质是资源相互竞争,相互等待,往往是两个(或以上)的Session加锁的顺序不一致
如何有效避免:
在程序中,操作多张表时,尽量以相同的顺序来访问(避免形成等待环路)
批量操作单张表数据的时候,先对数据进行排序(避免形成等待环路) A线程 id:1 ,10 ,20按顺序加锁 B线程id:20,10,1 这种的话就容易锁。
如果可以,大事务化成小事务,甚至不开启事务 select for update==>insert==>update = insert into update on duplicate key
尽量使用索引访问数据,避免没有 where 条件的操作,避免锁表 有走索引是记录行锁,没走索引是表锁
使用等值查询而不是范围查询查询数据,命中记录,避免间隙锁对并发的影响 1,10,20 等值where id in (1,10,20) 范围查询 id>1 and id<20
避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句;我们经常会有一些定时脚本,避免它们在同一时间点运行
到此这篇关于Mysql锁机制之行锁、表锁、死锁的实现的文章就介绍到这了,更多相关Mysql 行锁、表锁、死锁内容请搜索
更多相关Mysql内容来自木庄网络博客
标签:Mysql
相关阅读 >>
更多相关阅读请进入《mysql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。