不要在存储过程中控制事务

MySQL技术内幕 InnoDB存储引擎 344页.

原来看过这段,总而言之,就是MySQL不要在存储过程中控制事务.
当时没有仔细看细节,只是记住了一个结论.
毕竟都21世纪了.还有用存储过程的?
但是..#（×&%￥×&@……&……#@&

以如下过程为例.

1. drop table nums;  
2. drop procedure pCreateNums;  
3.   
4. create table nums(id int not null primary key);  
5.   
6. delimiter $$  
7. create procedure pCreateNums(cnt int)  
8. begin  
9.     -- declare exit handler for sqlexception rollback;  
10.     start transaction;  
11.     insert into nums(id) values(cnt+rand()*100);      
12.     insert into nums(id) values(cnt);  
13.     commit;  
14. end $$  
15. delimiter ;  
16.   
17. call pCreateNums(10);  
18. call pCreateNums(10);  

连续调用两次过程,会触发主键冲突的异常.
最后的commit没有执行,第二个过程的事务并未完成.


这时,需要上层调用的程序,进行事务的提交或者回滚.

当然,也可以定义一个Handler进行异常处理.

1. drop table nums;  
2. drop procedure pCreateNums;  
3.   
4. create table nums(id int not null primary key);  
5.   
6. delimiter $$  
7. create procedure pCreateNums(cnt int)  
8. begin  
9.     declare exit handler for sqlexception rollback;  
10.     start transaction;  
11.     insert into nums(id) values(cnt+rand()*100);      
12.     insert into nums(id) values(cnt);  
13.     commit;  
14. end $$  
15. delimiter ;  
16.   
17. call pCreateNums(10);  
18. call pCreateNums(10); 

第一个过程执行成功,第二个过程触发异常处理自动回滚
但是,上层的JAVA程序对于这一切,都透明了..
他后续的工作怎么处理?
缓存是否更新?分布式架构下,任务还继续吗?给客户端返回什么?

所以,过程和JAVA程序还得约定异常的类型.

1. drop table nums;  
2. drop procedure pCreateNums;  
3.   
4. create table nums(id int not null primary key);  
5.   
6. delimiter $$  
7. create procedure pCreateNums(cnt int)  
8. begin  
9.     declare exit handler for sqlexception begin rollback;select -1;end;  
10.     start transaction;  
11.     insert into nums(id) values(cnt+rand()*100);      
12.     insert into nums(id) values(cnt);  
13.     commit;  
14.     select 1;  
15. end $$  
16. delimiter ;  

这样约定异常的常量,把异常处理,自己又实现了一遍.

MSSQL 可以自动回滚事务,并且会抛出异常,上层JAVA开发可以捕获这个异常.
但是MySQL还是做不到的.

所以事务控制最好由程序端完成.

1. drop table nums;  
2. drop procedure pCreateNums;  
3.   
4. create table nums(id int not null primary key);  
5.   
6. delimiter $$  
7. create procedure pCreateNums(cnt int)  
8. begin  
9.     start transaction;  
10.     insert into nums(id) values(cnt+rand()*100);      
11.     insert into nums(id) values(cnt);  
12. end $$  
13. delimiter ; 

JAVA程序调用过程之前,开启事务,然后调用过程,根据过程的执行情况,提交或者回滚.

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

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