MYSQL 由一个锁问题,带出MYSQL事务错误不回滚的问题

2020-06-27 00:00:00 语句 记录 信息 实际上 不回

大约两个礼拜前有同学抛出这个图片问是怎么回事, 没有时间随即记下,有时间来处理。假期本来想懒懒,但答应人家的事情,是要做的。

实际上,上面的图是一个很经典的MYSQL的 record locks 的问题, 问题的起因应该是 testdb.a 这张表的某条记录,例如

select name from a where name = 'Jassica' for update; 

在操作时,如果有其他语句在另一个session中也操作 name = 'Jassica' 这条记录,就可能会产生上面的情况。

官方的文档也是这样说的,但实际上估计有人会不大信服, 怎么能模拟出那个show engine innodb status 中出现的上述的锁信息。

下面我们就来做一做看看怎样的情况能出现上面的信息


1 请创建一个简单的表,具体有多简单,1 要有主键, 2 要有一个非主键的字段,例如varchar(30), 然后输入一些信息,类似下面这样。


然后启动两个 sessions 


1  session 1 begin;

2  session 1 update a set name = 'aaa'  where name > 'PPP';

3  session 2 begin;

4  session 2 update a set name = 'PPP' where name > 'Jassica';


然后和上面同学给我的截图类似的锁的信息就有了。


这里有两个问题


1 name > 'PPP' 我们并不知道到底有几个记录被UPDATE 

2  name > 'Jassica"  又有几条记录我们也不知道

问题1  有5条记录被更新, 符合name > 'PPP' 的有5条记录

问题2  > 'Jassica' 的


所以死锁信息中

的信息就是 sinomina , x_professor, x_man   四条记录。

与SESSION 2 中的要更新的 5条记录,有冲突。


以上均在MYSQL 8.019  RC 模式下。


到此出现错误的信息的原因大概是弄清了, 其实到这里我们今天的主题才刚刚开始,问题是如果在 update 语句之前事务中还有其他的udpate语句, 到底是回滚不回滚。

答案是:  不 不 不回滚


我们看一下是不是这样



1  session 1 begin;

2  session 1 update a set name = 'aaa'  where name > 'PPP';

3  session 2 begin;

4  session 2 update a set name = '111111' where name = 'PPP';

5  session 2 update a set name = 'PPP' where name > 'Jassica';

6  session 1 commit;

7  session 2 commit;

session 2  失败了, 到底 PPP  变成了 111111 吗?  这就是今天关键,按照传统数据库来说, 当然是不能,应该全部回滚。

那你的MYSQL 这里一8.019 为例 , 答案是什么。

答案:不出所料,如果你的失败的事务上面有其他的DML语句,一定会被执行

这就和SQL SERVER 默认的事务执行的方式一样, 如果事务错误,则上面执行的就不回 OMG, 我想着和开发人员想的不大一样。


实际上MYSQL 和 SQL SERVER 一样,具体SQL SERVER 怎么做避免这个问题(请自行百度,或查找之前很久写过这样的文字)。


这里不管SQL SERVER , MYSQL 实际上有一个参数默认是 disabled

我们需要打开, innodb_rollback_on_timeout = 1 这个参数。他的功能是,自动回滚不会发生InnoDB锁等待超时错误。并且这个参数需要关闭MYSQL 在配置文件中配置,在重启动生效。

session 2

session 1


所以,如果有开发反应数据库的数据不大对头的时候,那DB门是不是要关注这个参数是ENABLED OR DISABLED。


QQ群有电子书自取,可能会加不上人数可能满了

相关文章