头歌 事务处理

目录

文章目录

第1关：创建账户表-模拟银行转账

第2关：创建存储过程-模拟事务原子性

第3关：创建存储过程-模拟事务并发控制

第1关：创建账户表-模拟银行转账

编程要求

测试说明

第2关：创建存储过程-模拟事务原子性

编程要求

测试说明

第3关：创建存储过程-模拟事务并发控制

编程要求

测试说明

---

文章目录

• 第1关：创建账户表-模拟银行转账

• 第2关：创建存储过程-模拟事务原子性

• 第3关：创建存储过程-模拟事务并发控制

---

第1关：创建账户表-模拟银行转账

本关任务：创建账户表，模拟两个账户间进行转账。

相关知识
数据一致性是指关联数据之间的逻辑关系是否正确和完整，当数据库中的数据从一个状态转变到另一个状态时，要保证数据的一致性。

在银行转账过程中，A账户转账给B账户，至少要执行转出、转入两个动作，这两个动作都正确执行且全部执行完毕，才能完成一次正确的银行转账。若只执行其中一个动作，数据库中存储的数据会发生错误，造成数据不一致的问题。

举例
以下是账户表account的表结构

编程要求

根据提示，在右侧编辑器补充代码。
（1）创建account表，表结构如上文所示；
（2）在account表中添加两条数据。

account_id  account_name  balance  

  202201      张一     1000.00
  202202      王二     1000.00  

测试说明

系统提供测试语句，返回account表结构，显示表数据。

use bank;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
/**********(1)创建account表**********/
 CREATE TABLE account (
  account_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  account_name VARCHAR(20) NOT NULL,
  balance DECIMAL(10,2) DEFAULT '0.00',
  CONSTRAINT ck_1 CHECK ((balance >= 0))
); 
  
/**********(2)插入测试数据**********/
INSERT INTO account(account_id, account_name, balance)
VALUES (202201, "张一",1000.00),
(202202, "王二",1000.00);

 /**********End**********/

---

第2关：创建存储过程-模拟事务原子性

任务描述
本关任务：创建存储过程，在存储过程中开启事务管理，并进行异常处理。

模拟转账
为了完成本关任务，你需要掌握：
1.存储过程的定义；
2.在存储过程中开启事务管理；
3.对SQL进行异常处理。

转账存储过程示例
用于转账功能的存储过程定义如下：

DELIMITER $$
CREATE PROCEDURE p_transfer(IN from_account int,IN to_account int,IN amount DECIMAL(10,2))
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
       -- 转入操作；
       -- 转出操作;
   COMMIT;   
 END$$
DELIMITER ;

存储过程p_transfer设置三个输入参数：from_account 对应转出账户；to_account 对应输入账户, amount表示转账数。
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;出现SQL异常时事务执行回滚操作。当转出账户余额小于0时违反了检查约束，会出现SQL异常。
START TRANSACTION;用于开启事务管理；
COMMIT; 提交事务

编程要求

根据提示，在右侧编辑器补充代码。
（1）根据第1关中 account 表结构，设计存储过程 p_transfer 用于转帐，转帐中进行事务控制，出现SQL异常时能撤消事务，保证帐户数据的一致性；
（2）调用存储过程，从202201账户转600元到202202账户，并查询 account 表中数据；
（3）调用存储过程，从202201账户转600元到202202账户，并查询 account 表中数据。

测试说明

开始你的任务吧，祝你成功！

use bank;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 /**********(1)创建存储过程p_transfer**********/
DELIMITER $$
CREATE PROCEDURE p_transfer(IN from_account int,IN to_account int,IN amount DECIMAL(10,2))
BEGIN
   DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
   START TRANSACTION;
    UPDATE account SET balance=balance+amount WHERE account_id=to_account;
    UPDATE account SET balance=balance-amount WHERE account_id=from_account; 
   COMMIT;   
 END$$
DELIMITER ;
  /**********(2)调用p_transfer进行第1次转账,并查询结果**********/
  CALL p_transfer(202201, 202202, 600.00);
  SELECT * FROM account;
 /**********(3)调用p_transfer进行第2次转账，并查询结果**********/
  CALL p_transfer(202201, 202202, 600.00);
  SELECT * FROM account;
 /**********End**********/

---

第3关：创建存储过程-模拟事务并发控制

任务描述
本关任务：创建存储过程，并进行事务并发控制，防止丢失更新和脏读。

业务描述
若account表中没有定义检查约束，表结构如下：

CREATE TABLE account (
  account_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_name VARCHAR(20) NOT NULL,
  balance DECIMAL(10,2) DEFAULT 0.00
) AUTO_INCREMENT=202201;

则账户余额不足判断放在存储过程中来实现，转账流程如下：

流程图中相关说明：

（1）事务控制

使用START TRANSACTION开启事务。
（2）余额检查   

在更新余额之前，先检查转出账户的余额是否足够。如果余额不足，直接回滚事务并抛出错误信息。
（3）并发控制

使用FOR UPDATE锁定转出和转入账户的行，防止在转账过程中被其他事务修改。

SELECT balance INTO from_balance FROM account WHERE account_id = from_account FOR UPDATE;

（4）错误处理
使用SIGNAL SQLSTATE '45000'抛出自定义错误，方便调用者处理错误情况。
如若转账金额小于0，则抛出错误信息，结束流程。

IF amount <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转账金额必须大于0';
END IF;

说明
SIGNAL 是 SQL 标准中的一个关键字，用于显式地抛出一个异常。
SQLSTATE 是一个标准的错误代码，用于描述 SQL 操作的状态。
45000：这是一个通用的异常错误代码，表示“未分类的异常”。

编程要求

根据提示，在右侧编辑器补充代码。
   根据本关中 account 表结构，按上面提供的流程设计存储过程 p_transfer 实现转帐，转帐中进行事务并发控制和读写控制，防止丢失更新和脏读。

测试说明

测试程序将分三次调用你编写的存储过程进行测试：
（1）调用存储过程，从202201账户转-600元到202202账户，并查询 account 表中数据；
（2）调用存储过程，从202201账户转600元到202202账户，并查询 account 表中数据。
（3）调用存储过程，从202201账户转600元到202202账户，并查询 account 表中数据。
开始你的任务吧，祝你成功！

use bank;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
DELIMITER //
CREATE PROCEDURE p_transfer(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    -- 声明局部变量（from_balance 存放转出账户余额，to_balance 存放转入账户余额
    DECLARE from_balance DECIMAL(10,2); 
    DECLARE to_balance DECIMAL(10,2);

    -- 开始事务
    START TRANSACTION;

    -- 检查转账金额是否大于0，若小于0则输出错误提示信息，结束流程
    IF amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '转账金额必须大于0';
    END IF;

    -- 获取转出账户的余额，锁定该账户不允许修改，防止失败更新和脏读
    SELECT balance INTO from_balance 
    FROM account 
    WHERE account_id = from_account 
    FOR UPDATE; 

    -- 检查转出账户余额是否足够，不足则回滚，提示错误信息
    IF from_balance < amount THEN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足，转账失败';
    END IF;

    -- 获取转入账户的余额，锁定该账户不允许修改，防止失败更新和脏读
    SELECT balance INTO to_balance 
    FROM account 
    WHERE account_id = to_account 
    FOR UPDATE;

    -- 更新转出账户余额
    UPDATE account 
    SET balance = balance - amount 
    WHERE account_id = from_account;

    -- 更新转入账户余额
    UPDATE account 
    SET balance = balance + amount 
    WHERE account_id = to_account;

    -- 提交事务
    COMMIT;
END //

DELIMITER ;


 /**********End**********/

---