ImQi1云笔记

外观

事务机制

事务修改的概念

事务是一组相互依赖的操作单元的集合，用来保存对数据库的正确修改，保证数据的完整性，如果事务的某个单元操作失败，将取消本次事务的全部操作。如银行交易，A给B转账，需要执行两次操作：A的银行资金减少，B的银行资金增加，若B没有转账成功，则取消A的本次扣除，即事务回滚到扣除之前，即A中没少，B中没多，事务的特征如下：

  • 原子性：每个事务是一个不可分割的整体，只有所有的操作都执行成功，事务才能够提交，否则此次事务失败，所有成功执行的操作单元都需撤销，数据库回到此次事务之前的状态。

  • 一致性：在执行一次事务后，关系数据的完整性和业务逻辑的一致性不能被破坏。假如A和B转账结束后，资金总额是不能改变的。

  • 隔离性：在并发环境中，一个事务所做的修改必须与其他事务所做的修改相隔离。例如一个事务查看的数据必须是其他并发事务不能查修改之前或修改完毕的数据，不能是修改中的数据。

  • 持久性：事务结束后，对数据的修改是永久保存的，即使数据故障导致数据库系统重启，数据依然是修改后的状态。

事务机制的重要性

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库中只有一个账户表tb_account，保存着两张借记卡账户A和B，并且要求这两张借记卡都不能透支。下面通过具体的代码演示一下没有事务机制的话会有什么后果。

  1. 创建银行数据库bank，在下面创建数据表tb_account，代码如下。

    create database bank;
use bank;
create table tb_account (
id int unsigned not null auto_increment primary key,
    name varchar(50) not null,
    balance float(8,2) unsigned default 0
);
    运行结果
    运行结果

  2. 向银行数据表中插入两条记录，具体代码如下。

    insert into tb_account (name,balance) values
('A',1000),('B',0);
select * from tb_account;
    运行结果
    运行结果

  3. 创建模拟转账的存储过程，代码如下。

    delimiter //
create procedure proc_transfer(in name_from varchar(50),in name_to varchar(50),in money int) reads sql data
begin
update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
end
//
    运行结果
    运行结果

  4. 调用刚刚创建的存储过程，实现A向B转账七百元，代码如下。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

  5. 再次尝试转账700元，代码如下。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

    可以看到，由于A的账户不足700元，所以扣款失败，但是B账户还是收到了700元，这是因为在扣除A账户的700元时发生了异常，我们应该撤销B账户得到的700元，并没有，这并不符合我们的预期，所以事务就可以解决这一问题。在有了事务之后，我们就可以将扣款和转账的语句看成一个整体，若扣款失败，则目标账户的资金不能增加，或者扣除已经增加不该增加的金额。

关闭MySQL自动提交

MySQL默认开启自动提交，也就是说，MySQL会自动地把每一个语句都看成一个事务，在执行完该语句后执行提交操作。

查看MySQL的自动提交是否开启

查看MySQL的自动提交是否关闭有以下几种方法。

使用SHOW VARIABLES语句，语法如下。

SHOW VARIABLES LIKE 'autocommit';
运行结果
运行结果

在Value字段值为ON或1时表示自动提交开启，值为OFF或0时表示自动提交结束。

关闭自动提交有以下两种情况：

  1. 在当前连接中，可以通过将AUTOCOMMIT变量设置为0，来禁用自动提交功能，代码如下。

    set autocommit = 0;
show variables like 'autocommit';
    运行结果
    运行结果

    系统变量autocommit是会话变量，即在一个命令行窗口将autocommit设置为0，另一个命令行窗口的autocommit的值还是1。

    当autocommit设置为0时，所有的SQL语句都是在一个事务中，直到显式地执行提交或者回滚时该事务才结束，并且同时开启另一个新事务。

  2. 当使用START TRANSACTION命令时可以隐式地关闭自动提交功能，该方法不会改变autocommit变量的值。

事务回滚

在上面的转账过程中，我们添加当转账用户的余额不足时撤销目标用户增加金额的操作，即进行事务回滚，步骤如下。

  1. 关闭MySQL的自动提交功能。
set autocommit = 0;

  1. 接着《事务机制的重要性》的例子，之前的。

    select * from tb_account;
    运行结果
    运行结果

  2. 接着进行一次转账并查看存款。

    call proc_transfer('A','B',700);
select * from tb_account;
    运行结果
    运行结果

可以看到在转账之后，A的账户扣款失败，但是B账户仍然多了700元，现在我们重新打开一个命令行窗口，并查看这两个账户的存款。

运行结果
运行结果

可见在新命令行窗口中并没有实际改变这两个变量的值，这是因为关闭了MySQL的自动提交后，若不手动提交，那么UPDATE操作将只会影响内存中的临时记录，并没有真正写入数据库文件。所以在当前命令行窗口执行SELECT语句时，获得的是临时记录，并不是实际数据表中的记录。此时的结果取决于接下来执行的操作，若执行ROLLBACK操作，那么就放弃当前的修改，若执行COMMIT，那么做出的修改会永久存到数据表文件中。

之后我们执行事务回滚操作，事务回滚操作的代码如下。

rollback;
select * from tb_account;
运行结果
运行结果

事务提交

当关闭自动提交之后，数据库开发人员可以根据需要提交更新操作，否则更新的结果不会提交到数据库文件中，成为数据库永久的组成部分。

显式提交

关闭自动提交功能之后，可以使用COMMIT命令显式提交更新语句，如上一节最后若把回滚语句换成提交语句COMMIT，就会变成以下结果。

运行结果
运行结果

此时该条记录就会永久地保存到数据库文件中，此时若再次打开一个新的命令行窗口并执行查看语句，会得到和上图相同的结果。

隐式提交

关闭自动提交后，若没有手动提交更新操作或者执行回滚操作，则当执行下表的命令时也将执行提交操作。

BEGINSET AUTOCOMMIT = 1LOCK TABLES
START TRANSACTIONCREATE DATABASE/TABLE/INDEX/PROCEDUREUNLOCK TABLES
TRUNCATE TABLEALTER DATABASE/TABLE/INDEX/PROCEDURE
RENAME TABLEDROP DATABASE/TABLE/INDEX/PROCEDURE

MySQL中的事务

在MySQL中，应用START TRANSACTION命令来标记一个事务的开始，语法如下。

start transaction;

通常START TRANSACTION后面跟着的是组成事务的SQL语句，并且要在所有语句都成功执行后执行提交事务COMMIT语句，下面通过一个具体实例演示事务的使用。

首先看一下A账户和B账户的账户余额，如下图，实现转账过程并调用的代码步骤如下。

运行结果
运行结果

  1. 创建存储过程，在存储过程中添加一个事务，实现从一个账户向另一个账户转账的过程，代码如下。

    delimiter //
create procedure prog_tran_account(in name_from varchar(50),in name_to varchar(50),in money int)
modifies sql data -- 修改数据库内数据
begin
    declare exit handler for sqlexception rollback;  -- 若发生数据库异常则回滚
    start transaction;
    update tb_account set balance = balance + money where name = name_to;
    update tb_account set balance = balance - money where name = name_from;
    commit;
end
//
    运行结果
    运行结果

  2. 尝试从A账户向B账户转账1200元，代码如下。可以看到转账失败了，但是A和B账户的资金总额没变，因为进行了事务回滚。

    call prog_tran_account('A','B',1200);
select * from tb_account;
    运行结果
    运行结果

  3. 这次我们尝试从A账户向B账户转账800元，代码如下。可以看到这次转账成功了。

    call prog_tran_account('A','B',800);
select * from tb_account;
    运行结果
    运行结果

回退点

默认情况下若回滚事务，所有执行成功的SQL操作都会撤销.若不想全部撤销，而只需要撤回一部分，就可以通过设置回退点，然后退回到回退点实现，创建回退点、退回该点的语法如下。

SAVEPOINT 回退点名;
ROLLBACK TO SAVEPOINT 回退点名;

如我们在转账之前先创建一个C账户，然后从A向B转账1200元（假设A的余额有1000元），代码如下。从结果可以看到，虽然转账失败了，但是C账户成功创建了。

delimiter //
create procedure prog_savepoint_account(in name_from varchar(50),in name_to varchar(50),in money int) modifies sql data
begin
    declare continue handler for sqlexception
        begin
            rollback to savepoint A;
            commit;
        end;
    start transaction;
    start transaction;
    insert into tb_account (name,balance) values ('C',0);
    savepoint A;
    update tb_account set balance = balance + money where name = name_to;
update tb_account set balance = balance - money where name = name_from;
    commit;
end //
delimiter ;
call prog_savepoint_account('A','B',1200);
select * from tb_account;
运行结果
运行结果

数据库管理系统采用锁的机制来管理事务。当多个事务同时修改同一数据时，只允许持有锁的事务修改该数据，其他事务只能“排队等待”，直到前一个事务释放其拥有的锁。下面对MySQL中提供的锁机制进行详细介绍。

许可证：署名 4.0 国际 (CC-BY-4.0)

2026 © 棋