作业提交表单地址

作业1：

学习目标：

1.识记关键字，掌握事务的基本特点、作用和应用场景。

2.理解事务的四大特性和隔离级别。

3.分析业务需求，设计事务的程序处理方式。

4.创建带有事务处理的存储过程保证数据完整性和安全性。

学习导航：

画板

一、新知学习：

1.什么是事务（what）？有何特性？

•事务是一组有着内在逻辑联系的SQL语句。

•支持事务的数据库系统要么正确执行事务里的所有SQL语句，要么把它们当作整体全部放弃，也就是说事务永远不会只完成一部分。

•事务可以由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。

•在事务中的操作，要么都执行，要么都不执行，这就是事务的目的，也是事务的重要特征。

四大特性：ACID

•原子性（Atomicity）

•一致性（Consistency）

•隔离性（Isolation）

•持久性（Durability）

2.为什么要用事务？

使用事务可以大大提高数据安全性和执行效率，因为在执行多条SQL命令的过程中不需要使用LOCK命令锁定整个数据表 。

3.哪些场景中使用？

在逻辑上相关联的表的数据更新与维护会引起其他数据同步更新的场景中适用。

如：用户下单订购某些商品，orders表中新增订单，同时所订购商品在ordersITem表中有相关数据，对应需要修改goods表的商品库存和销售数量，修改orders表中的订单金额account，同时会根据金额修改users表中的用户积分。

4.事务的隔离级别，默认第三种，repeatable read

7.8 事务（Transaction） - 图2

2.事务的隔离级别：（安全性及效率）

（1）查看事务的隔离级别，默认：REPEATABLE-READ

SHOW VARIABLES LIKE ‘%ISOLATION%’;

（2）设置事务的隔离级别

  1. SET SESSION TRANSACTION ISOLATION LEVEL read UNCOMMITTED;
  2. SET SESSION TRANSACTION ISOLATION LEVEL read COMMITTED;
  3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;# 默认
  4. SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

二、实践应用：

准备工作：创建一张表icbc，表示银行储蓄账户信息。

  1. CREATE TABLE icbc(
  2. uid BIGINT UNSIGNED auto_increment PRIMARY KEY COMMENT '账户',
  3. uname varchar(20) not null COMMENT '用户名',
  4. account DECIMAL(20,2) DEFAULT 0
  5. );
  6. -- 添加账户信息
  7. insert icbc(uname,account)
  8. VALUES ('郭辉',1000),('蔡静',1000);

1.购买支付业务，转账功能实现。

  1. -- 正常的业务流程：
  2. START transaction;
  3.         -- 1.买家郭辉账户减去100，余额 900
  4.         update icbc b
  5.         SET  b.account = b.account -100
  6.         WHERE b.uname = '郭辉';
  7.         -- 2.卖家蔡静账户加100，余额 600
  8.         update icbc b
  9.         SET  b.account = b.account +100
  10.         WHERE b.uname = '蔡静';
  11. COMMIT ; -- 成功就提交
  12. ROLLBACK; -- 失败就回滚

改写成存储过程：

  1. drop procedure if exists proc_pay;
  2. create procedure proc_pay(buyer varchar(10),seller varchar(10),fee decimal(10,2))
  3. begin
  4.   -- 声明变量rowCount统计受影响的行数
  5.     DECLARE rowCount1,rowCount2 int;
  6.     -- 开启显式事务，转账过程置于事务内
  7.   start TRANSACTION;
  8.     -- 1.买家转出fee
  9.     update icbc b
  10.         SET  b.account = b.account -fee
  11.         WHERE b.uname = buyer;
  12.      -- 获取操作影响的函数
  13.       SET rowCount1 = ROW_COUNT();
  14.      -- 2.卖家入账fee
  15.      update icbc b
  16.         SET  b.account = b.account + fee
  17.         WHERE b.uname = seller;
  18.      -- 获取操作影响的函数
  19.      SET rowCount2 = ROW_COUNT();
  20.      -- 判断是否成功，有一个不成功就回滚，如果不成功就回滚
  21.      if rowCount1 = 0  or rowCount2 = 0 then
  22.        rollback;
  23.      else
  24.        COMMIT;
  25.      end if; 
  26. end;

调用存储过程实现支付：

  1. -- 接收方姓名错误，执行失败，回滚
  2. call proc_pay('郭辉','蔡 静',100);
  3. -- 执行成功，持久性
  4. call proc_pay('郭辉','蔡静',100);

执行失败，观察数据无变化。

执行成功，转账数据发生一致性的变化。

2.在网上商城数据库中，创建存储过程，实现用户从购物车中，下单购买商品业务。使用事务机制实现。

（提示：用户从购物车中下单购买商品时，需要完成订单表，订单详情表相应的数据插入操作，将该商品从购物车中删除，同时，更新商品的已售数量以及库存量） 
  1. -- 创建存储过程，实现用户从购物车中，下单购买商品业务。使用事务机制实现。（提示：用户从购物车中下单购买商品时，需要完成订单表，订单详情表相应的数据插入操作，将该商品从购物车中删除，同时，更新商品的已售数量以及库存量）
  2. DROP PROCEDURE if exists proc_order;
  3. CREATE PROCEDURE proc_order(u_id int )
  4. begin
  6. -- 声明变量，存储游标数据
  7. DECLARE n,o_id,g_id,c_num,quantity int; 
  8. DECLARE O_code varchar(20); -- 订单编码
  9. DECLARE price decimal(10,2) ; -- 商品价格
  11. -- 定义游标，获取该用户选购的商品
  12. DECLARE cur CURSOR FOR SELECT gid,cnum from cart where uid = u_id;
  14. -- 异常处理
  15. DECLARE EXIT Handler FOR 1064 ROLLBACK;
  16. -- 商品数量
  17. SELECT count(*) into n from cart where uid = u_id;
  18. -- 打开游标
  19. OPEN cur;
  20. -- 开启事务
  21. START transaction;
  22. -- 新增订单
  23. SET o_code = concat('O',DATE_FORMAT(now(),'%y%m%d%h%i%s'),1);
  24. -- 
  25. insert orders(uid,ocode)
  26. VALUES(U_id,o_code);
  28. -- 获取新增订单的ID
  29. SET o_id= LAST_INSERT_ID();        
  30. -- 该用户在购物车的所有商品
  31. REPEAT
  32. FETCH cur into g_id,c_num;
  33. -- 获取商品库存数量
  34. SELECT g.gquantity,g.gprice into quantity ,price
  35. from goods g where  gid = g_id;        
  36. -- 比较对应商品的购买数量和库存量关系，如果大于库存，就以库存销售
  37. IF quantity < c_num THEN
  38. SET c_num = quantity;          
  39. END IF;
  40. -- 在订单详情中添加一行记录
  41. insert ordersitem(oid,gid,inum)
  42. VALUES(O_id,g_id,c_num);
  43. -- 更新goods表中的库存和销量
  44. UPDATE goods g
  45. SET g.gquantity = g.gquantity - c_num,
  46. g.gsale_qty = g.gsale_qty + c_num
  47. WHERE g.gid = g_id;    
  48. -- 更新orders表中订单金额
  49. UPDATE orders o
  50. SET o.oamount = o.oamount + price*c_num
  51. WHERE oid = o_id;
  52. -- 删除购物车中对应的纪录
  53. delete from cart WHERE uid= u_id and gid=g_id    ;    
  54. --
  55. SET n = n-1;
  56. UNTIL n = 0             
  57. END repeat;
  58. UPDATE orders o
  59. SET o.ocode = CONCAT(o.ocode,LPAD(oid,2,'0'))
  60. WHERE oid = o_id;
  61. commit;
  62. -- 关闭游标
  63. CLOSE cur;
  64. end;

使用游标操作改写存储过程：

-- 2.在网上商城数据库中，创建存储过程，实现用户从购物车中，下单购买商品业务。使用事务机制实现。
-- （提示：用户从购物车中下单购买商品时，需要完成订单表，订单详情表相应的数据插入操作，将该商品从购物车中删除，同时，更新商品的已售数量以及库存量）
DROP PROCEDURE if EXISTS proc_pay_cart;
CREATE PROCEDURE proc_pay_cart(u_id int)
BEGIN
DECLARE r1,r2,r3,r4,r5 INT;
DECLARE o_id ,g_id,c_num INT;
DECLARE o_amount ,money decimal(20,2) ;
DECLARE otime datetime;
-- 声明游标
DECLARE cur CURSOR FOR 
  SELECT gid,cnum FROM cart WHERE uid = u_id;
-- 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done=1;# 游标访问数据终止
-- 
DECLARE EXIT HANDLER FOR 1093,1048 ROLLBACK;
-- 打开游标
OPEN cur;
START TRANSACTION ;  # 开始事务
-- 1. orders表新增订单 DESC orders;
INSERT orders(uid,ocode)VALUES(u_id,'');
SET r1=ROW_COUNT();
SET o_id = LAST_INSERT_ID();
-- 循环
SET @done=0 ,o_amount=0;
WHILE @done=0 DO
    -- 读取游标数据到变量
  FETCH cur INTO g_id,c_num;
    if @done=0 THEN
        -- 2. ordersitem表新增订单详情 desc ordersitem
        INSERT ordersitem(oid,gid,inum) VALUES(o_id,g_id,c_num);
        SET r2=ROW_COUNT();
        -- 3. goods表修改商品的库存和销售量 desc goods
        UPDATE goods 
        SET gquantity = gquantity-c_num,gsale_qty=gsale_qty+c_num
        WHERE gid=g_id;
        SET r3=ROW_COUNT();
        -- 4. cart表删除对应商品 desc cart
        DELETE FROM cart WHERE uid=u_id AND gid=g_id;
        SET r4=ROW_COUNT();
        SELECT gprice*c_num FROM goods WHERE gid=g_id into money; # 当前商品价格
        SET o_amount=o_amount + money; # 累计计算订单金额
    END IF;
    -- 用户购物车所有行处理完，循环终止
END WHILE;
-- 5. 完善orders对应的金额 desc orders
SELECT ordertime FROM orders WHERE oid = o_id into otime;

UPDATE orders o
SET oamount=o_amount,
    ocode=CONCAT('O',DATE_FORMAT(otime,'%y%m%d%h%i%s'),1,LPAD(oid,2,0))
WHERE oid = o_id;

SET r5=ROW_COUNT();
-- 以上操作都成功，提交
IF r1>0 and r2>0 and r3>0 and r4>0 and r5>0 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;-- 否则，回滚
-- 关闭游标
CLOSE cur;
end;

-- CALL proc_pay_cart(7);

三、课堂小结：

1.事务处理就是为了实现一组SQL操作作为一个整体来执行，保证数据的一致性和完整性。

2.在事务处理过程中，默认的隔离级别repeatable read足够预防某些意外事件的发生，保证数据安全，同时也能实现并发操作。

3.事件的四大特性ACID。