【16.0】MySQL进阶知识之事务

【一】什么是事务

• 开启一个事务可以包含多条语句，这些语句要么同时成功，要么都不成功

• 事务是指一系列相关操作的集合，这些操作被视为一个不可分割的工作单元。

• 事务的目标是确保在多个操作中的每一个都要么全部成功执行，要么全部失败回滚。

  • 即事务的原子性

【二】事务的四大特性(ACID)

【1】原子性（Atomicity）

• 事务被视为一个原子操作，不可再分割。
• 要么所有的操作都成功执行，要么所有的操作都会被回滚到事务开始前的状态，确保数据的一致性。

【2】一致性（Consistency）

• 事务执行前后，数据库应保持一致的状态。
• 在事务开始之前和结束之后，数据库必须满足所有的完整性约束，如数据类型、关系等。

【3】隔离性（Isolation）

• 事务的执行结果对其他并发执行的事务是隔离的。
• 即一个事务的执行不应受到其他事务的干扰，各个事务之间应该相互独立工作，从而避免数据的不一致性。

【4】持久性（Durability）

• 也叫永久性

• 一旦事务被提交，其结果应该永久保存在数据库中，并且可以被系统故障恢复。

• 即使系统发生宕机或崩溃，事务提交后的更改也应该是永久性的。

【三】事务的作用

• 在操作多条数据的时候，可能会出现某几条操作不成功的情况

【1】数据一致性

• 事务可以确保数据库的一致性。
• 在一个事务中，要么所有的操作都成功执行，要么全部回滚，保证了数据的完整性和一致性。
• 例如，在一个转账操作中，如果转出账户扣款成功而转入账户未能成功接收资金，事务可以将操作全部回滚，以确保资金的一致性。

【2】并发控制

• 事务提供了并发控制机制，确保多个并发执行的事务不会相互干扰，并避免数据的混乱和冲突。
• 通过隔离级别的设置，事务可以控制不同事务之间的可见性和影响范围，保证并发执行时的数据一致性和隔离性。

【3】故障恢复

• 事务的持久性特性确保了在事务提交后，即使系统发生故障或崩溃，提交的结果仍然可以被恢复。
• 数据库管理系统通过使用日志文件等机制，将事务的操作记录下来，以便在需要时进行恢复和重放。

【4】高效运行

• 通过组织多个操作为一个事务，可以减少与数据库交互的次数，从而提高数据库的操作效率和性能。事务可以减少磁盘I/O、锁的竞争等操作开销，提高数据库的并发处理能力。

【5】数据完整性和安全性

• 事务可以保护数据的完整性和安全性。
• 通过在事务中定义一些条件和约束，可以确保数据的有效性和准确性。
• 例如，在一个银行系统中，事务可以检查账户余额是否足够以及转账金额是否合法，从而保证数据的安全性和正确性。

【6】用户提交订单操作示例

• 检查库存：
  • 系统需要检查所需商品的库存是否足够。
  • 如果库存不足，系统会提示用户库存不足，无法完成订单。
• 扣减库存：
  • 如果库存充足，系统会将所购商品对应的库存数量减少。
• 生成订单：
  • 系统会生成一个新的订单，包括订单号、商品信息、购买数量、价格等相关信息。
• 计算总价：
  • 根据订单中的商品信息和购买数量，系统会计算出订单的总价格。
• 更新用户账户：
  • 根据用户选择的支付方式，在扣除相应金额后，系统会更新用户账户余额或积分。
• 生成支付信息：
  • 系统会生成相应的支付信息，以便用户完成支付。
• 通知物流部门：
  • 系统会通知物流部门准备配送相关商品。
• 发送订单确认邮件/短信：
  • 系统会向用户发送订单确认的邮件或短信，包括订单详细信息、配送信息等。
• 监控商品配送：
  • 系统会跟踪订单的配送情况，并向用户提供订单状态更新。
• 完成订单：
  • 当用户收到商品并确认满意后，订单状态会被更新为“已完成”。

【四】如何使用事务

【1】事务相关的关键字

# 开启事务
start transaction;

# 回滚(回到事务执行之前的操作)
rollback;

# 二次确认(确认之后无法回滚)
commit;

• 事务（Transaction）是数据库管理系统（DBMS）中的一种机制，用于保证数据库操作的一致性和可靠性。
• 当多个数据库操作需要作为一个整体进行提交或回滚时，可以使用事务来实现。

【2】使用事务的基本步骤

（1）开始事务

• 使用数据库提供的特定语句或命令来开始一个事务。

# 通常使用以下语句开始一个新的事务。
BEGIN、START TRANSACTION
# 或
BEGIN TRANSACTION

（2）执行事务操作

• 在事务中执行需要的数据库操作，包括插入、更新、删除等操作。
• 这些操作可以是单个SQL语句，也可以是多个SQL语句的组合。

（3）提交事务或回滚事务

• 提交事务：

  • 当所有的操作都执行成功且符合业务逻辑要求时，使用COMMIT语句来提交事务。
  • 提交后，系统会把数据库中的修改永久保存下来。

• 回滚事务：

  • 如果在事务执行过程中发生错误或违反了业务规则，需要撤销当前事务所做的所有更改

  # 使用以下语句来回滚事务。
  ROLLBACK
  

（4）结束事务

• 不论是提交还是回滚事务，都需要通过特定的语句来结束事务。

# 在大部分关系型数据库中，可以使用以下语句来结束事务。
END
# 或
COMMIT

（5）使用事务示例（使用SQL语言）

START TRANSACTION; -- 开始事务

-- 执行事务操作
INSERT INTO 表名 (列1, 列2, 列3) VALUES (值1, 值2, 值3);
UPDATE 表名 SET 列1 = 值1 WHERE 条件;
DELETE FROM 表名 WHERE 条件;

COMMIT; -- 提交事务，将修改永久保存
OR
ROLLBACK; -- 回滚事务，撤销所有修改

END; -- 结束事务

• 值得注意的是，事务不仅仅适用于关系型数据库，很多NoSQL数据库也支持事务操作。

【五】模拟转账过程中的事务(Python)

【1】示例

• 在模拟转账过程中使用事务机制可以确保转账操作的一致性和可靠性。
  • 事务的原子性（Atomicity）特性可以确保转账过程中的数据库操作要么全部成功提交，
  • 要么全部回滚。
• 以下是一个简单的代码演示，使用Python和MySQL数据库来模拟转账过程中的事务机制：

import pymysql


def transfer_funds(sender_id, receiver_id, amount):
    try:
        # 创建数据库连接
        connection = pymysql.connect(
            host="数据库主机名",
            user="用户名",
            password="密码",
            database="数据库名称"
        )

        # 建立游标
        cursor = connection.cursor()
        
        # 开启事务
        cursor.execute('START TRANSACTION;')
        
        # 查询发送者的余额
        cursor.execute("SELECT balance FROM accounts WHERE id = %s", (sender_id,))
        sender_balance = cursor.fetchone()[0]

        # 检查发送者余额是否足够
        if sender_balance < amount:
            return "Insufficient funds"

        # 更新发送者余额
        cursor.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, sender_id))

        # 更新接收者余额
        cursor.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, receiver_id))

        # 提交事务
        connection.commit()

        # 关闭游标和连接
        cursor.close()
        connection.close()

        return "Transfer successful"
    except pymysql.Error as e:
        # 回滚事务
        connection.rollback()

        # 关闭游标和连接
        cursor.close()
        connection.close()

        return str(e)


# 使用示例
result = transfer_funds(1, 2, 100)
print(result)  # 输出："Transfer successful"

• 在上述代码中
  • 首先建立数据库连接，并使用start_transaction()方法开始一个新的事务。
  • 接下来，通过游标执行数据库操作，包括查询发送者的余额、检查余额是否足够、更新发送者和接收者的余额。
  • 如果所有操作成功执行，就调用commit()方法提交事务，并关闭游标和连接。
  • 如果在任意一个操作中出现异常，就调用rollback()方法回滚事务，并关闭游标和连接。
• 拓展
  • 在查询发送者的余额时，可以使用FOR UPDATE语句锁定了该行数据，以确保在事务结束前其他事务不能修改这条数据。
  • 这样可以避免并发情况下的数据一致性问题。

【2】模拟转账功能

（1）创建表

create table user(
	id int primary key auto_increment,
    name varchar(16),
    balance int
);

（2）插入数据

insert into user(name,balance) values
("dream",1000),
("chimeng",1000),
("mengmeng",1000);

（3）事务流程

# 开启事务
start transaction;

# 多条SQL语句
update user set balance=900 where name="dream";
update user set balance=1010 where name="chimeng";
update user set balance=1090 where name="mengmeng";

# 事务回滚(取消交易)
rollback

# 二次确认(确认交易)
commit;

【六】总结

• 当让多条SQL语句保持一致性的时候(要么同时成功，要么同事失败)，可以考虑使用事务