Module 06 · Bonus Module: Advanced SQL for Data Engineers

Hands-on Lab: Committing and Rolling Back a Transaction动手实验：提交和回滚事务

Estimated time needed: 20 minutes
预计所需时间：20 分钟

A transaction is simply a sequence of operations performed using one or more SQL statements as a single logical unit of work. A database transaction must be ACID (Atomic, Consistent, Isolated and Durable). The effects of all the SQL statements in a transaction can either be applied to the database using the COMMIT command or undone from the database using the ROLLBACK command.
一项事务简单地来说就是使用一个或多个 SQL 语句作为单个逻辑工作单元执行的一系列操作。数据库事务必须满足 ACID（原子性、一致性、隔离性和持久性）特性。事务中的所有 SQL 语句的效果要么通过 COMMIT 命令应用到数据库，要么通过 ROLLBACK 命令从数据库中撤销。

In this lab, you will learn some commonly used TCL (Transaction Control Language) commands of SQL through the creation of a stored procedure routine. You will learn about COMMIT, which is used to permanently save the changes done in the transactions in a table, and about ROLLBACK, which is used to undo the transactions that have not been saved in a table. ROLLBACK can only be used to undo the changes in the current unit of work.
在本实验中，你将通过创建存储过程例程来学习 SQL 中常用的 TCL（事务控制语言）命令。你将学习 COMMIT，它用于永久保存表中事务所做的更改，以及 ROLLBACK，它用于撤销表中未保存的事务。ROLLBACK 只能用于撤销当前工作单元中的更改。

Objectives 目标

After completing this lab, you will be able to:
完成此实验后，您将能够：

Permanently save the changes done in a transaction
永久保存事务中的更改
Undo the transaction that has not been saved
撤销未保存的事务

Software Used in this Lab 本实验中使用的软件

MySQL is a Relational Database Management System (RDBMS) designed to efficiently store, manipulate, and retrieve data.
MySQL 是一个关系型数据库管理系统（RDBMS），旨在高效地存储、操作和检索数据。

To complete this lab you will utilize MySQL relational database service available as part of IBM Skills Network Labs (SN Labs) Cloud IDE. SN Labs is a virtual lab environment used in this course.
完成此实验，您将使用 IBM 技能网络实验室 (SN Labs) 云集成开发环境 (Cloud IDE) 中提供的 MySQL 关系型数据库服务。SN Labs 是本课程中使用的虚拟实验室环境。

Database Used in this Lab 本次实验使用的数据库

Mysql_learners database has been used in this lab.
本实验使用了 Mysql_learners 数据库。

Data Used in this Lab 本实验使用的数据

The data used in this lab is internal data. You will be working on the BankAccounts and ShoeShop tables.
本实验使用的数据为内部数据。您将处理 BankAccounts 和 ShoeShop 表。

This lab requires you to have the BankAccounts and ShoeShop tables populated with sample data. Download the BankAccounts-CREATE.sql and ShoeShop-CREATE.sql scripts below, and load them to the phpMyAdmin console. The scripts will create new tables called BankAccounts and ShoeShop and populate them with the sample data required for this lab.
本实验要求您在 BankAccounts 和 ShoeShop 表中填充示例数据。下载下方的 BankAccounts-CREATE.sql 和 ShoeShop-CREATE.sql 脚本，并将其加载到 phpMyAdmin 控制台。这些脚本将创建名为 BankAccounts 和 ShoeShop 的新表，并填充本实验所需的示例数据。

Sample Exercise 示例练习

Example of committing and rolling back a transaction.
提交和回滚事务的示例。

Scenario: Rose is buying a pair of boots from ShoeShop. So we have to update Rose's balance as well as the ShoeShop balance in the BankAccounts table. Then we also have to update Boots stock in the ShoeShop table. After Boots, let's also attempt to buy Rose a pair of Trainers.
场景：Rose 正从 ShoeShop 购买一双靴子。因此，我们必须更新 Rose 的余额以及 ShoeShop 表中的 BankAccounts 余额。然后，我们还需要更新 ShoeShop 表中的靴子库存。之后，我们尝试为 Rose 购买一双训练鞋。

Once the tables are ready, create a stored procedure routine named TRANSACTION_ROSE that includes TCL commands like COMMIT and ROLLBACK.
一旦表准备好了，创建一个名为 TRANSACTION_ROSE 的存储过程例程，该例程包含 TCL 命令，如 COMMIT 和 ROLLBACK。
Now develop the routine based on the given scenario to execute a transaction.
现在根据给定的场景开发例程，以执行事务。
To create the stored procedure routine on MySQL, copy the code below and paste it to the textarea of the SQL page. Click Go.
要在 MySQL 上创建存储过程，请复制下面的代码并将其粘贴到 SQL 页面的文本区域中。点击 Go。

DELIMITER //

CREATE PROCEDURE TRANSACTION_ROSE()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    START TRANSACTION;
    UPDATE BankAccounts
    SET Balance = Balance-200
    WHERE AccountName = 'Rose';

    UPDATE BankAccounts
    SET Balance = Balance+200
    WHERE AccountName = 'Shoe Shop';

    UPDATE ShoeShop
    SET Stock = Stock-1
    WHERE Product = 'Boots';

    UPDATE BankAccounts
    SET Balance = Balance-300
    WHERE AccountName = 'Rose';

    COMMIT;
END //

DELIMITER ;

Let's now check if the transaction can successfully be committed or not. Copy the code below in a new blank script and paste it to the textarea of the SQL page. Click Go
现在让我们检查事务是否能够成功提交。在一个新的空白脚本中复制下面的代码，并将其粘贴到 SQL 页面的文本区域中。点击 Go。

CALL TRANSACTION_ROSE;

SELECT * FROM BankAccounts;

SELECT * FROM ShoeShop;

Observe that the transaction has been executed. But when we observe the tables, no changes have permanently been saved through COMMIT. All the possible changes happened might have been undone through ROLLBACK since the whole transaction fails due to the failure of a SQL statement or more. Let's go through the possible reason behind the failure of the transaction and how COMMIT - ROLLBACK works on a stored procedure:
观察到事务已经执行。但是当我们查看表时，由于 COMMIT，没有永久保存任何更改。由于一个或多个 SQL 语句失败导致整个事务失败，所有可能发生的更改可能通过 ROLLBACK 被撤销。让我们探讨事务失败的可能原因以及 COMMIT - ROLLBACK 在存储过程中的工作原理：

The first three UPDATEs should run successfully. Both the balance of Rose and ShoeShop should have been updated in the BankAccounts table. The current balance of Rose should stand at 300 - 200 (price of a pair of Boots) = 100. The current balance of ShoeShop should stand at 124,200 + 200 = 124,400. The stock of Boots should also be updated in the ShoeShop table after the successful purchase for Rose, 11 - 1 = 10.
前三个 UPDATE 语句应该能够成功执行。Rose 和 ShoeShop 的余额都应该在 BankAccounts 表中更新。Rose 的当前余额应为 300 - 200（靴子的价格）= 100。ShoeShop 的当前余额应为 124,200 + 200 = 124,400。在 Rose 成功购买靴子后，ShoeShop 表中的靴子库存也应更新，11 - 1 = 10。
The last UPDATE statement tries to buy Rose a pair of Trainers, but her balance becomes insufficient (Current balance of Rose: 100 < Price of Trainers: 300) after buying a pair of Boots. So, the last UPDATE statement fails. Since the whole transaction fails if any of the SQL statements fail, the transaction won't be committed.
最后一个 UPDATE 语句试图为 Rose 购买一双运动鞋，但在购买了一双靴子后，她的余额不足（Rose 的当前余额：100 < 运动鞋的价格：300）。因此，最后一个 UPDATE 语句失败。由于如果任何 SQL 语句失败，整个事务都会失败，所以事务不会被提交。

Practice exercise 练习练习

Now let's practice an exercise on committing and rolling back a transaction.
现在让我们练习一个关于提交和回滚事务的练习。

Create a stored procedure TRANSACTION_JAMES to execute a transaction based on the following scenario: First buy James 4 pairs of Trainers from ShoeShop. Update his balance as well as the balance of ShoeShop. Also, update the stock of Trainers at ShoeShop. Then attempt to buy James a pair of Brogues from ShoeShop. If any of the UPDATE statements fail, the whole transaction fails. You will roll back the transaction. Commit the transaction only if the whole transaction is successful.
创建一个存储过程 TRANSACTION_JAMES，根据以下场景执行事务：首先从 ShoeShop 为 James 购买 4 双运动鞋。更新他的余额以及 ShoeShop 的余额。同时，更新 ShoeShop 中运动鞋的库存。然后尝试从 ShoeShop 为 James 购买一双牛津鞋。如果任何 UPDATE 语句失败，整个事务将失败。您将回滚事务。只有当整个事务成功时，才提交事务。

Hint 提示

Use the previous code from Task A Step 2 and modify it. Take 1200 (4 x 300) from James's balance and add 1200 to the ShoeShop balance. Take 4 trainers out of the stock in ShoeShop. Then take 150 from James's balance.
使用任务 A 步骤 2 中的先前代码并进行修改。从 James 的余额中扣除 1200（4 x 300），并将 1200 添加到 ShoeShop 的余额。从 ShoeShop 的库存中取出 4 双运动鞋。然后从 James 的余额中扣除 150。

Solution 解决方案

DELIMITER //
CREATE PROCEDURE TRANSACTION_JAMES()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
UPDATE BankAccounts
SET Balance = Balance-1200
WHERE AccountName = 'James';
UPDATE BankAccounts
SET Balance = Balance+1200
WHERE AccountName = 'Shoe Shop';
UPDATE ShoeShop
SET Stock = Stock-4
WHERE Product = 'Trainers';
UPDATE BankAccounts
SET Balance = Balance-150
WHERE AccountName = 'James';
COMMIT;
END //
DELIMITER ;

Conclusion 结论

Congratulations! You have completed this lab, and you are ready for the next topic.
恭喜！您已完成此实验，现在可以进入下一个主题。

You are now able to:
现在您可以：

Write a stored procedure to record a transaction in multiple tables
编写一个存储过程以在多个表中记录一个事务
Understand the difference between permanent and rollable transactions
理解永久事务和可回滚事务之间的区别
Perform a transaction 执行一个事务
Perform a rollback 执行回滚

Author(s) 作者

Abhishek Gagneja 阿比什克·甘格贾

Lakshmi Holla 拉克希米·霍拉

Malika Singla