存储过程与事务应用两三事

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Spring等框架支持对事务的管理,可以更好的实现存储和业务逻辑的分离,不过代码或者框架层面的事务管理,最终还是落实到数据库上,存储过程作为一组预编译的SQL语句,性能会优于代码层面的多次SQL操作。在一些复杂的业务场景,或者对性能要求比较高的垂直业务上,可以适当的直接操作数据库。

项目开发中通常会有一些复杂业务场景,比如多个数据表的插入更新等,这些业务通常需要事务的支持。
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判断操作是否成功。

product

2.在存储过程中使用事务实现

这里模拟一个简化的商品下单操作,包括用户下单,首先查库存,库存足够则更新库存,订单表增加一条记录。在存储过程中使用事务加独占锁完成功能。

  • ORDER表
CREATE TABLE `order` (
  `order_id` int(11)  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,或者显式的设置隔离级别等操作,在存储过程中应用事务,可以处理比较复杂的业务。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7月前
|
存储
SQLServer存储过程中事务的创建
SQLServer存储过程中事务的创建
51 0
|
存储 SQL 安全
【数据库原理及应用】创建与使用存储过程
主要是创建简单存储过程,游标的存储过程及创建和使用事务
487 0
|
13天前
|
存储 SQL
物料清单应用输入模板的SQL存储过程设计
物料清单应用输入模板的SQL存储过程设计
|
13天前
|
存储 SQL 数据库
数据库第十二次作业 存储过程(PL/SQL语句集)的应用
数据库第十二次作业 存储过程(PL/SQL语句集)的应用
36 0
|
5月前
|
存储 XML Java
Spring中service层与存储过程的事务回滚
Spring中service层与存储过程的事务回滚
62 0
|
10月前
|
存储 SQL 数据库
第一次机房收费系统——存储过程的应用
存储过程只需要在创建时进行编译,以后每次调用此存储过程都不需要在进行编译,而一般的SQL语句每执行一次就编译一次,所以使用存储过程可以提高数据库执行速度
|
10月前
|
存储
SqlServer存储过程应用二:分页查询数据并动态拼接where条件 (下)
SqlServer存储过程应用二:分页查询数据并动态拼接where条件 (下)
|
10月前
|
存储 SQL 数据库连接
SqlServer存储过程应用二:分页查询数据并动态拼接where条件 (上)
SqlServer存储过程应用二:分页查询数据并动态拼接where条件
|
11月前
|
存储 SQL 关系型数据库
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 2
288 0
|
11月前
|
存储 SQL NoSQL
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
【MySQL速通篇003】MySQL视图,MySQL触发器,MySQL函数,MySQL存储过程(参数分类,存储过程的增删改查等),SQL的动态执行,支持事务的存储过程,pymysql 1
453 0