MySQL过程慎用if not exists写法

最新推荐文章于 2025-04-14 09:37:27 发布

coolmsn8786

最新推荐文章于 2025-04-14 09:37:27 发布

阅读量3.7k

点赞数 1

文章标签：数据库

接前文
http://blog.itpub.net/29254281/viewspace-2133705/

环境MySQL 5.6.14
事务隔离级别读提交
事务的开启和结束由JAVA程序控制.

上次报死锁的过程,抽象如下

delimitr $$
CREATE PROCEDURE `test_proc`(
pid int
)
begin
if not exists (select * from t where id=pid) then
insert into t(id) values(pid);
end if;
update t set total=total+1 where id=pid;
end $$
delimiter ;

死锁原因已经明白了，就是并发情况下,Insert遇到排它锁,则尝试加共享锁。
在最后Update的时候,两个持有共享锁的连接,都尝试申请排它锁,则导致了死锁.

但是问题是...怎么会走到了最后一行的Update语句?
另外两个连接,不是应该在Insert语句时报 Duplicate entry 'xx' for key 'PRIMARY'错误吗?

问题应该出在这种结构里
if not exists (select * from t where id=pid) then
xxx
end if;

使用 if not exists 模式,真心要注意啊.在这种结构里出现的异常,不会报错,而是直接跳出IF判断,继续执行！！

实验准备

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`total` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
truncate table t;
drop procedure if exists test_proc;
delimiter $$
CREATE PROCEDURE `test_proc`(
pid int,
ptotal int
)
begin
if not exists (select * from t where id=pid) then
insert into t(id,total) value(pid,ptotal);
update t set total=ptotal+1 where id=pid;
end if;
select ptotal+1;
end $$
delimiter ;

打开三个客户端，分别执行过程

第一个客户端执行，并没有提交.
第二，第三客户端处于阻塞状态.
等第一个客户端提交,

第二个客户端返回 201
第三个客户端返回 301
且没有任何的报错信息.

三个客户端都提交之后，查看T表信息
只有一个记录,id为1,total为101

也就是说,第二个,第三个客户端,在得到主键冲突的异常后,没有报错,没有继续执行IF块内剩下的语句,而是直接跳出了IF块,继续执行IF块外的语句！！

该报错的地方不报错,在大段的存储过程中,导致死锁还是小问题,就怕引起数据的错乱,而不自知.

针对这种情况,如果有主键或者唯一约束,我觉得干脆改为如下的方式.
delimiter $$
CREATE PROCEDURE `test_proc`(
    pid int,
    ptotal int
)
begin
    insert into t(id,total) value(pid,ptotal);
    update t set total=ptotal+1 where id=pid;
    select ptotal+1;
end $$
delimiter ;

简单,粗暴,易懂...反正主键冲突了,JAVA应用程序直接回滚了事.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-2133936/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-2133936/