深入理解MySQL事务：原理、应用与实践(上)

事务的应用场景说明

在实际生活中，事务在处理复杂的业务逻辑时显得尤为重要。以下是两个典型的应用场景，展示了事务如何确保数据的一致性和完整性。

场景一：淘宝购物中的资金流转问题

假设你在淘宝上购买了一把阿米洛的键盘，当你完成支付后，资金已经从你的支付宝账户中扣除。然而，就在此时，淘宝的转账系统突然宕机，导致淘宝平台并未实际收到这笔款项。这种情况下，虽然你的账户显示资金已减少，但卖家却未收到付款，因此不会发货。这种不一致的状态显然是不合理的，因为作为买家，你已经支付了款项，却无法收到商品。

场景二：付款后的发货与撤销问题

另一种情况是，当你完成支付后，卖家看到付款成功的提示，随即发货。然而，如果你有权限撤销这笔交易，那么你的账户资金将不会减少，但卖家却已经发货。这种情况下，卖家将面临货物已发出但未收到款项的风险，同样会导致数据的不一致。

事务解决方案

为了解决上述问题，数据库中的事务机制提供了一个有效的解决方案。事务可以将一组SQL操作视为一个整体，要么全部执行成功，要么全部回滚，从而确保数据的一致性。具体操作如下：

START TRANSACTION;

-- 执行一组SQL语句，例如：
-- 1. 从买家账户扣除款项
-- 2. 将款项转入卖家账户
-- 3. 更新订单状态为“已付款”

IF (所有操作正常完成) THEN
    COMMIT;  -- 提交事务，所有操作生效
ELSE
    ROLLBACK;  -- 回滚事务，撤销所有操作
END IF;

通过这种方式，事务确保了在任何异常情况下，数据都能保持一致，避免了买家和卖家之间的资金和货物不匹配的问题。

什么是事务

在实际的业务开发中，某些业务操作可能需要多次访问数据库，涉及多条SQL语句的执行。为了确保这些操作的整体性和一致性，我们需要将这些多次访问数据库的操作视为一个不可分割的整体，即事务。事务的核心思想是：要么所有SQL语句全部执行成功，要么在任何一条SQL语句失败时，进行事务的回滚，使得所有SQL语句全部执行失败。

简而言之，事务是指逻辑上的一组操作，这些操作的各个单元要么全部成功，要么全部失败。事务的作用在于保证在一个事务中，多次操作数据库表中的数据时，要么所有操作都成功，要么所有操作都失败，从而确保数据的一致性和完整性。

我的理解是，事务就是将多条SQL语句组合在一起，共同完成某个特定的功能，确保这些操作作为一个整体被执行，从而避免数据的不一致性。

MySQL中事务的提交

1.自动提交

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL中默认是自动提交事务的:即执行一条sql语句提交一次事务

通过以下命令可以查看当前autocommit模式：

show variables like '%commit%';

实现:a给b转账100元,演示提交事务和回滚事务

-- 创建数据库（如果数据库不存在的话）
-- 这条命令会检查是否存在名为 'db_transaction' 的数据库，如果不存在则创建它。
CREATE DATABASE IF NOT EXISTS db_transaction;

-- 使用数据库
-- 这条命令选择 'db_transaction' 数据库作为接下来操作的目标数据库。
USE db_transaction;

-- 创建账户表
-- 这条命令创建一个名为 'tb_account' 的表，用于存储账户信息。
-- 表中包含三个字段：
-- id: 主键，自动递增，用于唯一标识每个记录。
-- name: 存储账户名称，不允许为空。
-- money: 存储账户余额，数据类型为双精度浮点数，总长度为7位，其中小数点后占2位。
CREATE TABLE tb_account(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自动递增
	`name` VARCHAR(8) NOT NULL, -- 账户名，最大长度为8个字符，不允许为空
	money DOUBLE(7,2) -- 账户余额，双精度浮点数，总共7位，其中小数点后2位
);

-- 插入初始数据
-- 向 'tb_account' 表中插入两条记录，分别代表两个账户及其初始余额。
INSERT INTO tb_account(`name`,money) VALUES
('a',1000), -- 插入账户名为 'a'，余额为 1000 的记录
('b',1000); -- 插入账户名为 'b'，余额为 1000 的记录

-- 查询所有记录
-- 这条命令用于查询 'tb_account' 表中的所有记录，显示所有账户的信息。
SELECT * FROM tb_account;

1.1 atuocommit-ON 不执行 start transaction

atuocommit-ON 不执行 start transaction,直接执行修改数据库的语句,那么此时MySQL就会自动提交事务

a-100,b+100

UPDATE tb_account SET money=money-100 WHERE `name` = 'a';
UPDATE tb_account SET money=money+100 WHERE `name` = 'b';

可以看到a账户中的money=900 b账户中的money=1100

1.2 atuocommit-ON 执行 start transaction

atuocommit-ON 执行 start transaction,然后再执行修改数据库的语句：

UPDATE tb_account SET money=money-100 WHERE `name` = 'a';
UPDATE tb_account SET money=money+100 WHERE `name` = 'b';

当 autocommit 设置为 ON（这是MySQL的默认设置），每一个单独的SQL语句都会被视为一个独立的事务，并且在语句执行完毕后立即提交。这意味着，如果没有显式地开始一个事务（使用 START TRANSACTION 或 BEGIN），那么每个语句都会自动提交其更改。

然而，一旦使用了 START TRANSACTION 或 BEGIN 显式地开始了事务，即使 autocommit 是 ON，MySQL 也会暂停自动提交机制，直到事务结束（通过 COMMIT 提交或 ROLLBACK 回滚）。在这个事务期间，所有的更改都不会被提交到数据库，直到显式地调用 COMMIT 命令。

2.手动提交

由于先前通过命令查看到MySQL的全局变量“autocommit”的值是ON 即自动提交事务,所以我们要修改“autocommit”的值

2.1设置自动提交的参数为OFF:

set autocommit = 0;  -- 0:OFF  1:ON

注 [自行验证]

1）设置autocommit为off状态，只是临时性的，下次重新连接MySQL服务器，autocommit依然变为on状态。

2）如果设置autocommit为off状态，那么当我们执行一条sql语句，就不会自动提交事务，重新启动客户端，数据并没有改变。

案例演示1：需求：演示手动提交事务，a给b转账100元。

-- 设置自动提交的参数为OFF
set autocommit = 0;

-- 开始事务
start transaction ;

-- 执行一组SQL语句 a-100
update tb_account set money=money-100 where `name` = 'a';

-- 执行一组SQL语句 b+100
update tb_account set money=money+100 where `name` = 'b';

-- 提交事务 (生效)
commit ;

案例演示2：演示回滚事务，a给b转账100元。（失败）

-- 查看当前autocommit模式：
show variables like '%commit%';

-- 设置自动提交的参数为OFF
set autocommit = 0;

update tb_account set money=1000 where `name` = 'a';

update tb_account set money=1000 where `name` = 'b';

select *from tb_account;

-- 开始事务
start transaction ;

-- 执行一组SQL语句 a-100
update tb_account set money=money-100 where `name` = 'a';

-- 执行一组SQL语句 b+100
update tb_account set money=money+100 where `name` = 'b';

-- 提交事务 (生效)
commit ;

-- 回滚
rollback ;

原理说明

1. 一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
2. 如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
3. 如果开启事务，将所有的写操作写到日志文件中。
4. 如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
5. 如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。

事务原理详解

事务是数据库管理系统（DBMS）中用于管理数据一致性和完整性的重要机制。事务的原理涉及多个方面，包括日志文件的使用、事务的提交和回滚等。以下是对事务原理的详细解释：

1. 事务的基本概念

事务是一组SQL语句，它们被视为一个单一的工作单元。事务具有以下四个关键特性，通常被称为ACID特性：

• 原子性（Atomicity）：事务中的所有操作要么全部成功执行，要么全部不执行。如果事务中的任何一部分失败，整个事务都会回滚到初始状态。
• 一致性（Consistency）：事务执行前后，数据库必须从一个一致性状态转移到另一个一致性状态。这意味着事务不能违反数据库的完整性约束。
• 隔离性（Isolation）：事务的执行是相互隔离的，一个事务的中间状态对其他事务是不可见的。这确保了并发事务不会相互干扰。
• 持久性（Durability）：一旦事务提交，其结果是永久性的，即使系统发生故障也不会丢失。

2. 日志文件的作用

在事务处理过程中，日志文件（也称为事务日志或重做日志）扮演着关键角色。日志文件用于记录事务的所有操作，以便在事务提交或回滚时能够恢复数据的一致性。

• 日志文件的创建：当用户登录并开始一个事务时，服务器会创建一个临时日志文件。这个日志文件用于保存用户事务的状态和所有操作。
• 日志文件的内容：日志文件中记录了事务的所有写操作（如 INSERT、UPDATE、DELETE），包括操作的详细信息（如修改前的数据和修改后的数据）。

3. 事务的执行过程

• 开启事务：使用 START TRANSACTION 或 BEGIN 语句开始一个新的事务。
• 写操作：在事务中执行的所有写操作（如 INSERT、UPDATE、DELETE）会被记录到日志文件中，而不是直接写入数据库。
• 提交事务：使用 COMMIT 语句提交事务。提交事务时，日志文件中的所有操作会被应用到数据库中，使修改永久生效。
• 回滚事务：使用 ROLLBACK 语句回滚事务。回滚事务时，日志文件会被清空，所有未提交的修改都会被撤销，数据库恢复到事务开始前的状态。

4. 事务的提交和回滚

• 提交事务：

  • 当用户执行 COMMIT 语句时，日志文件中的所有操作会被应用到数据库中。
  • 数据库会确保所有操作都成功执行，并将修改永久保存。
  • 日志文件中的记录会被清除或归档，以释放空间。

• 回滚事务：

  • 当用户执行 ROLLBACK 语句时，日志文件会被清空，所有未提交的修改都会被撤销。
  • 数据库恢复到事务开始前的状态，确保数据的一致性。

5. 事务操作小结

• 开启事务：使用 START TRANSACTION 或 BEGIN 语句开始一个新的事务。
• 写操作：在事务中执行的所有写操作会被记录到日志文件中。
• 提交事务：使用 COMMIT 语句提交事务，将日志文件中的所有操作应用到数据库中。
• 回滚事务：使用 ROLLBACK 语句回滚事务，清空日志文件，撤销所有未提交的修改。

示例

以下是一个简单的示例，展示了事务的执行过程：

-- 开启事务
START TRANSACTION;

-- 执行写操作
UPDATE customers SET email = '[email protected]' WHERE name = 'John Doe';

-- 提交事务
COMMIT;

在这个示例中，UPDATE 操作会被记录到日志文件中。当执行 COMMIT 时，日志文件中的操作会被应用到数据库中，使修改永久生效。

总结

事务原理涉及日志文件的使用、事务的提交和回滚等关键机制。通过日志文件，事务能够确保数据的一致性和完整性，即使在系统故障的情况下也能恢复到一致的状态。理解事务的原理有助于更好地管理和优化数据库操作，确保数据的可靠性和一致性。

事务操作小结

说出事务原理？
开启事务后,SQL语句会放在临时的日志文件中,如果提交事务,将日志文件中SQL的结果放在数据库中

如果回滚事务则清空日志文件.