存储过程与事务应用两三事
更多深度文章，请关注云计算频道：https://yq.aliyun.com/cloud
Spring等框架支持对事务的管理，可以更好的实现存储和业务逻辑的分离，不过代码或者框架层面的事务管理，最终还是落实到数据库上，存储过程作为一组预编译的SQL语句，性能会优于代码层面的多次SQL操作。在一些复杂的业务场景，或者对性能要求比较高的垂直业务上，可以适当的直接操作数据库。
一、MySQL对事务的支持
维基百科对事务的定义中，一个数据库事务的存在包含有以下两个目的：
为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
事务的原子性和一致性可以通过定义回滚等操作完成，在数据库的并发控制中，通过隔离级别用来平衡系统性能和事务的隔离性。
SQL标准定义了4类隔离级别，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
READ UNCOMMITTED（读取未提交内容）：最低级别的隔离，它允许一个事务读取还没COMMIT的数据，这样会发生脏读（Dirty Read），实际很少采用
READ COMMITTED（读取提交内容）：在一个事务中只允许已经COMMIT的记录可见。READ COMMITTED满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。但是可能会出现不可重复读，指在一个事务内，多次读同一数据，在这个事务还没有结束时，另外一个事务也访问该同一数据。那么在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的
REPEATABLE READ（可重复读）：在一个事务开始后，其他连接对数据库的修改在本事务中不可见，直到本事务结束。这样保证在一个事务中重复读的结果一样，除非本事务中UPDATE数据库。 这是InnoDB引擎的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。但是可重复读下可能会出现幻读，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和存储引擎通过多版本并发控制（MVCC）机制解决了该问题
SERIALIZABLE（可串行化）：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他连接不能修改数据直到前一事务结束，在这个级别，可能导致大量的超时现象和锁竞争
1.MySQL的事务支持与具体的存储引擎相关
MyISAM：不支持事务，用于只读程序提高性能
InnoDB：支持ACID事务、行级锁、并发
Berkeley DB：支持事务
2.查看InnoDB存储引擎系统级的隔离级别和会话级的隔离级别
SELECT @@global.tx_isolation,@@tx_isolation;
可以使用如下语句设置MySQL的session隔离级别：
SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED| REPEATABLE READ | SERIALIZABLE}二、在存储过程中使用事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。
1.打开事务
为了打开事务，需要允许在COMMIT和ROLLBACK之前多条语句被执行：
（1）设置MySQL的autocommit属性为0，默认为1，通过下面的命令查看是否打开自动提交：
select @@autocommit;
或者
show variables like 'autocommit';
（2）使用START TRANSACTION语句显式的打开一个事务
# 开启事务 START TRANSACTION; # 需要事务的sql语句 # 提交事务 COMMIT;
2.定义异常后的回滚
在存储过程中应用事务需要注意对回滚的处理。
在处理事务时，可以使用SQLException捕获SQL错误，然后进行相关处理；在存储过程中，我们可以捕获SQL异常，判断回滚(ROLLBACK)还是提交(COMMIT)，根据业务逻辑定义不同的操作。
下面的语句定义发生异常后立即回滚：
#事务失败后回滚 declare exit handler for sqlexception rollback;
更多的情况，我们需要通过异常来进行一些业务处理，这时候可以通过定义一个变量来处理异常：
DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; START TRANSACTION; INSERT INTO t_test1 VALUES('test_id_01', 'test_value_01'); INSERT INTO t_test2 VALUES('test_id_01', 'test_value_02'); IF t_error = 1 THEN ROLLBACK; #其他业务操作 ELSE #其他业务操作 COMMIT; END IF;三、一个典型SELECT...UPDATE业务的存储过程
SELECT...UPDATE并发更新是常见的一类数据库业务，比如用户支付更新账户余额，购物车下单扣减库存等，需要通过首先进行一次查询操作，比较余额/库存等，判断是否进行下一步操作。
1.SELECT...UPDATE业务的解决
这类业务需要注意的是请求并发时的处理，对库存等的查询和更新，例如多个请求同时读取库存并且进行扣减操作，如果不能正确的进行数据隔离，可能会出现超卖等问题。
实现的方式也比较多，在InnoDB默认的的REPEATABLE READ隔离级别下，可以使用事务结合独占的行级锁进行更新，确保不同请求同时操作库存和扣减发生冲突。还有一种方式是使用CAS乐观锁，在扣减动作发生时比较当前的库存值和上次查询操作的库存值，最后结合AFFECT ROWS判断操作是否成功。
2.在存储过程中使用事务实现
这里模拟一个简化的商品下单操作，包括用户下单，首先查库存，库存足够则更新库存，订单表增加一条记录。在存储过程中使用事务加独占锁完成功能。
ORDER表
CREATE TABLE `order` ( `order_id` varchar(32) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID', `product_id` int(11) DEFAULT 0 COMMENT '商品ID', `user_id` int(11) DEFAULT 0 COMMENT '用户ID', `purchase_no` int(11) DEFAULT 0 COMMENT '购买数量') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';
PRODUCT表
CREATE TABLE `product` ( `product_id` varchar(32) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID', `stock` int(11) DEFAULT 0 COMMENT '库存') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='库存表';
新建一个存储过程，传入商品id,用户id和购买数量，返回操作结果和库存数量：
CREATE PROCEDURE take_order( IN product_id varchar(32), IN user_id varchar(32), IN count int, OUT code int, OUT stock int) BEGIN #定义发生异常时回滚 DECLARE t_error INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; #开启事务 START TRANSACTION; #解决并发数据不一致，可以使用写独占锁或者CAS机制,这里使用写独占锁 SELECT product.stock INTO stock FROM product WHERE product.product_id=product_id LIMIT 1 FOR UPDATE; IF(stock>=count) THEN INSERT INTO order (`product_id`, `user_id`, `purchase_no`)VALUES (product_id,user_id,count); UPDATE product SET product.stock=(stock-count) WHERE product.product_id=product_id; #提交事务或者回滚 IF t_error = 1 THEN ROLLBACK; SET code=0; ELSE COMMIT; SET code=1; SET stock=stock-count; END IF; ELSE #释放独占锁 COMMIT; SET code=0; END IF;END
数据库的事务管理还支持配置SAVEPOINT，或者显式的设置隔离级别等操作，在存储过程中应用事务，可以处理比较复杂的业务。