SQL 基础学习管理事务处理(十八) sql中事务

事务处理

它确保数据库在面对复杂操作时保持数据的一致性和完整性。事务是指一个或多个数据库操作的序列，这些操作作为一个单一的工作单元执行，它们要么完全执行成功，要么完全执行失败

举例说明下原理：

三张表：order是存储实际订单，order_items 存储订购的各项物品，customers 存储下订单的客户，order和order_items表中主键的唯一id互相关联
现在我们实现下添加订单的过程：

1、检查数据库中是否存在相应的顾客，不存在执行添加步骤

2、检索顾客的ID

3、在orders表中添加一行，他与顾客的id相互关联

4、检索orders中赋予新订单

5、为订购的每个物品在order_items表中添加一行，通过检索处的id把它与order表关联起来。

如果在这个过程中数据库出现故障（如磁盘空间、安全限制、表锁等），这个过程就无法完成，数据库中的数据就会出现问题。

如果故障发生在添加顾客之后，添加orders表之前，这个时候是没什么问题的，因为顾客没有订单是完全合法的

如果发生在插入数据orders表之后，添加order_items表数据之前，这样就会出现一个空订单，

如果故障出现在order_items添加过程中，这样可能出现一个不完成订单，但你还不知道，这个时候就会出现线上事故了。

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

我们回顾下上面的例子：
1、检查数据库中是否存在相应的顾客，不存在执行添加步骤

2、提交信息

3、检索顾客的ID

4、在orders表中添加一行

5、如果向orders表添加时出现故障，回撤

6、检索orders表中新订单的id

7、对于订购的每项商品，添加新行到order_items表

8、如果order_items表添加出现故障，则回退所有添加的orders表新行和order_items表的新行

这就是事务处理的实用案例，下面是一些术语：

1. 事务（Transaction）：一组数据库操作，作为一个单元进行执行。要么全部操作成功，要么全部回滚，保证数据库的一致性和完整性。
2. 原子性（Atomicity）：事务的原子性要求事务中的所有操作要么全部成功执行，要么全部失败回滚，不允许部分操作成功部分操作失败。
3. 一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。事务执行的结果必须符合预期的约束条件。
4. 隔离性（Isolation）：事务的隔离性要求并发执行的事务之间相互隔离，一个事务的执行不应该受到其他事务的干扰。
5. 持久性（Durability）：事务一旦提交，其结果应该永久保存在数据库中，即使系统发生故障也不会丢失。
6. 提交（Commit）：事务成功执行后，将事务的结果永久保存到数据库中。
7. 回滚（Rollback）：事务执行过程中出现错误或者事务被取消时，将事务的操作全部撤销，回到事务开始之前的状态。
8. 回滚点（Savepoint）：在事务执行过程中，可以设定一个回滚点，用于标记事务执行到某个特定的位置，可以在后续的操作中回滚到该点。
9. 脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。
10. 不可重复读（Non-repeatable Read）：一个事务多次读取同一数据，在事务执行过程中，数据发生了变化。
11. 幻读（Phantom Read）：一个事务在多次查询中，结果集的行数发生了变化，即使数据本身没有发生变化。

这些术语描述了SQL事务处理的基本概念和要求，有助于确保数据库操作的正确性和一致性

事务处理中是使用了的语句，但是不能进行回退的

CREATE或者DROP

控制事务处理

管理事务的关键在于将sql语句组分解为逻辑块，并明确规定数据合适应该回退，何时不该回退的

BEGIN; -- 或者 START TRANSACTION;

-- 执行一系列的数据库操作

COMMIT; -- 提交事务

--回滚事务    这里回滚有个条件 只有未被提交的事务，才能回滚，注意下这里因为现在部分DBMS的事务已经被隐式地提交
BEGIN; -- 或者 START TRANSACTION;

-- 执行一系列的数据库操作

ROLLBACK; -- 回滚事务

使用ROLLBACK

DELETE FROM copycust;
ROLLBACK; 
这样调用ROLLBACK不会生效，因为DELETE 会隐式进行提交
需要下面运行方式
BEGIN;
DELETE FROM copycust;
ROLLBACK; 

使用COMMIT

--此处不用的DBMS和数据的不同语法可能存在差异 目前是用mysql数据库
START TRANSACTION;
DELETE FROM copycust WHERE cust_id = 1000000001;
DELETE FROM copycust WHERE cust_id = 1000001;
COMMIT;

或者 SQL Server
BENGIN TRANSACTION;
DELETE FROM copycust WHERE cust_id = 1000000001;
DELETE FROM copycust WHERE cust_id = 1000001;
COMMIT;

这里在START TRANSACTION; 开始执行事务，里面的DELETE FROM copycust WHERE cust_id = 1000000001;DELETE FROM copycust WHERE cust_id = 1000001;这俩条语句全部执行成功后，最后COMMIT才会提交，如果这俩条语句有任何一条语句不成功都不会走commit提交

使用保留点

简单的ROLLBACK和COMMIT语句，就可以写入或者撤销整个事务。相对复杂的事务我们可能需要部分提交部分撤回的操作。

例如文章开头描述的，如果发生错误，只需要返回到添加orders行之前就可以了，不需要回退customers表。这里就需要事务处理块中的合适位置放置占位符（保留点），可以回退到这个位置。

保留点

MySql,MariaDB中的写法
SAVEPOINT delete1(唯一名称)

ROLLBACK TO delete1;
SQL Server 写法
SAVE TRANSACTION delete1(唯一名称)

ROLLBACK TRANSACTION delete1 -- 回滚

下面是MySql的例子（文章开头处的描述）

CREATE PROCEDURE add_customer_and_order1()
BEGIN
	DECLARE error_occurred INT DEFAULT 0;
	
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
	    SET error_occurred = 1;
	END;
	
	START TRANSACTION;
	
	INSERT INTO customers(cust_id,cust_name)
	VALUES(1000000011,"TOYS em");
 	 
	SAVEPOINT addcustomerpoint;  -- 打保留点

	INSERT INTO orders (order_num,order_date,cust_id)
	VALUES(20100,"2001/1/12",1000000011);
	IF error_occurred = 1 THEN
		ROLLBACK TO addcustomerpoint;
	ELSE
		INSERT INTO orderitems (order_num,order_item,prod_id,quantity,item_price)
		VALUES(20100,2,'BR03',100,10.99);
		IF error_occurred = 1 THEN
			ROLLBACK TO addcustomerpoint;
		ELSE
			COMMIT;
		END IF; -- 添加缺失的 END IF
    END IF; -- 添加缺失的 END IF
END; -- 添加缺失的 END

CALL add_customer_and_order1()

这条事务执行了4条插入语句，在初始customers表之后进行了保留点标记，之后如果发生错误就进行回退操作，后续的是所有错误都会照成回退到SAVEPOINT addcustomerpoint; 之前的内容，最后发布COMMIT保留数据。