MySQL管理事务处理

目录

1、事务处理是什么

2、控制事务处理

（1）事务的开始和结束

（2）回滚事务

（3）使用COMMIT 

（4）使用保留点

（5）结合存储过程的完整事务例子

3、小结

博主用的是mysql8 DBMS，附上示例资料：

百度网盘链接: https://pan.baidu.com/s/1XaWi3Y7hpXbs_uHq2cPI6Q

提取码: fpnx

1、事务处理是什么

使用事务处理（transaction processing），通过确保成批的 SQL 操作要么完全执行，要么完全不执行，来维护数据库的完整性。

关系数据库把数据存储在多个表中，使数据更容易操纵、维护和重用。设计良好的数据库模式表之间都是关联的。

Orders 表就是一个很好的例子。订单存储在 Orders 和OrderItems 两个表中：Orders 存储实际的订单，OrderItems 存储订购的各项物品。这两个表使用称为主键（参阅第 1 课）的唯一 ID 互相关联，又与包含客户和产品信息的其他表相关联。

给系统添加订单的过程如下：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；

(2) 检索顾客的 ID；

(3) 在 Orders 表添加一行，它与顾客 ID 相关联；

(4) 检索 Orders 表中赋予的新订单 ID；

(5) 为订购的每个物品在 OrderItems 表中添加一行，通过检索出来的 ID把它与 Orders 表关联（并且通过产品 ID 与 Products 表关联）。

如果故障发生在添加顾客之后，添加 Orders 表之前，则不会有什么问题。某些顾客没有订单是完全合法的。重新执行此过程时，所插入的顾客记录将被检索和使用。可以有效地从出故障的地方开始执行此过程。

但是，如果故障发生在插入 Orders 行之后，添加 OrderItems 行之前，怎么办？现在，数据库中有一个空订单。

更糟的是，如果系统在添加 OrderItems 行之时出现故障，怎么办？结果是数据库中存在不完整的订单，而你还不知道。

如何解决这种问题？这就需要使用事务处理了。事务处理是一种机制，用来管理必须成批执行的 SQL 操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

下面是关于事务处理需要知道的几个术语：

事务（transaction）指一组 SQL 语句；

回退（rollback）指撤销指定 SQL 语句的过程；

提交（commit）指将未存储的 SQL 语句结果写入数据库表；

保留点（savepoint）指事务处理中设置的临时占位符（placeholder），可以对它发布回退（与回退整个事务处理不同）。

 再看这个例子，这次我们说明这一过程是如何工作的：

(1) 检查数据库中是否存在相应的顾客，如果不存在，添加他；

(2) 提交顾客信息；

(3) 检索顾客的 ID；

2、控制事务处理

（1）事务的开始和结束

START TRANSACTION;

......

......

COMMIT;

-- 事务的开始结束结构，只有提交之后才会真正对表进行更改
-- 最后的 COMMIT 语句仅在不出错时写出更改。如果第一条 UPDATE 起作用，但第二条失败，则 UPDATE 不会提交。
START TRANSACTION;

UPDATE customers
SET cust_name = 'zhangsan'
WHERE cust_id = 10012;

UPDATE customers
SET cust_name = 'lisi'
WHERE cust_id = 10013;

COMMIT;

（2）回滚事务

回滚：回到事务开始前的状态，也就是结束掉本次事务的生命周期
ROLLBACK;

-- 回滚事务

START TRANSACTION;
-- 删除某行
DELETE FROM customers
WHERE cust_id = 10012;

-- 删除后已经查找不到该ID了
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

-- 回滚：回到事务开始前的状态，也就是结束掉本次事务的生命周期
ROLLBACK;

-- 回滚后可查到该id
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

COMMIT;

（3）使用COMMIT 

一般的 SQL 语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交（implicit commit），即提交（写或保存）操作是自动进行的。

在事务处理块中，提交不会隐式进行。不过，不同 DBMS 的做法有所不同。有的 DBMS 按隐式提交处理事务端，有的则不这样。进行明确的提交，使用 COMMIT 语句。

COMMIT;       -- 提交本次事务的操作

（4）使用保留点

使用简单的 ROLLBACK 和 COMMIT 语句，就可以写入或撤销整个事务。但是，只对简单的事务才能这样做，复杂的事务可能需要部分提交或回退。

例如前面描述的添加订单的过程就是一个事务。如果发生错误，只需要返回到添加 Orders 行之前即可。不需要回退到 Customers 表（如果存在的话）。

要支持回退部分事务，必须在事务处理块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。在 SQL 中，这些占位符称为保留点。在MySQL 中创建占位符，可使用 SAVEPOINT 语句。

每个保留点都要取能够标识它的唯一名字。

创建保留点：SAVEPOINT name;

回滚到指定保留点：ROLLBACK TO (SAVEPOINT) name;

-- 使用保留点

START TRANSACTION;
SAVEPOINT start0;
-- 删除某行
DELETE FROM customers
WHERE cust_id = 10012;
SAVEPOINT delete1;

-- 删除后已经查找不到该ID了
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

-- 回滚：回到 start0 时的状态，并不是回到start0开始执行语句哦
ROLLBACK TO SAVEPOINT start0;
-- 回滚到start0 即可查看信息
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

-- 由于现在已经是start0 时的状态，这个时候并没有delete1这个保留点，所以会提示不存在
ROLLBACK TO delete1;
SELECT cust_id
FROM customers
WHERE cust_id = 10012;

COMMIT;

（5）结合存储过程的完整事务例子

-- 一个完整事务例子--MySQL中的条件控制只能在存储过程（函数）中使用
DELIMITER //
CREATE PROCEDURE CompleteOrderTransaction(IN custName CHAR(50))
BEGIN
    DECLARE exit_handler INT DEFAULT FALSE;
		DECLARE custId INT DEFAULT NULL;
		DECLARE orderNum INT DEFAULT NULL;

    START TRANSACTION;

		SELECT cust_id INTO custId
		FROM Customers
		WHERE cust_name = custName;
		
		IF(custId IS NULL) THEN
			INSERT INTO Customers(cust_name)  -- 插入顾客名
			VALUES(custName);
			SET custId = LAST_INSERT_ID();
		END IF;
		
		SELECT custId;
    SAVEPOINT StartOrder;      -- 保留点，即将开始创建订单

    INSERT INTO Orders(order_date, cust_id)         -- 插入订单
    VALUES(CURRENT_TIMESTAMP, custId);
    IF (ROW_COUNT() < 0) THEN
				SELECT orderNum;
        ROLLBACK TO SAVEPOINT StartOrder;
    END IF;
		
		SET orderNum = LAST_INSERT_ID();
		
    -- 插入订单明细中的第一条
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) 
    VALUES(orderNum, 1, 'ANV01', 100, 5.49);
    IF (ROW_COUNT() < 0) THEN
        ROLLBACK TO SAVEPOINT StartOrder;
    END IF;

    -- 插入订单明细中的第二条
    INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) 
    VALUES(orderNum, 2, 'ANV02', 100, 10.99);
    IF (ROW_COUNT() < 0) THEN
        ROLLBACK TO SAVEPOINT StartOrder;
    END IF;
    
		COMMIT;
END //
DELIMITER ;

CALL CompleteOrderTransaction('zhangsan');

3、小结

1. 事务处理确保成批的SQL语句要么全部执行，要么全部不执行，确保出现问题中断处理时能够回滚
2. 术语：事务transaction、回退rollback、提交commit、保留点savepoint
3. 事务的开始和提交：START TRANSACTION、COMMIT。
4. 事务的回滚：ROLLBACK  -- 回滚到事务开始前状态，直接结束事务生命周期。
5. 保留点：可回滚到指定保留点，即回到保留点出的状态，不会直接结束事务。