什么是 SQL 事务,如何创建 SQL 事务

本文给大家介绍数据库中用来管理数据更新的重要概念——SQL 事务。简单来讲,事务就是需要在同一个处理单元中执行的一系列更新处理的集合。

本文重点

  • 事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务,可以对数据库中的数据更新处理的提交和取消进行管理。

  • 事务处理的终止指令包括 COMMIT(提交处理)和 ROLLBACK(取消处理)两种。

  • DBMS 的事务具有原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)四种特性。通常将这四种特性的首字母结合起来,统称为 ACID 特性。

一、什么是事务

估计有些读者对事务(transaction)这个词并不熟悉,它通常被用于商务贸易或者经济活动中,但是在 RDBMS 中,事务是对表中数据进行更新的单位。简单来讲,事务就是需要在同一个处理单元中执行的一系列更新处理的集合

SQL 如何插入、删除和更新数据 所述,对表进行更新需要使用 INSERTDELETE 或者 UPDATE 三种语句。

但通常情况下,更新处理并不是执行一次就结束了,而是需要执行一系列连续的操作。这时,事务就能体现出它的价值了。

说到事务的例子,请大家思考一下下述情况。

现在,请大家把自己想象为管理 Product(商品)表的程序员或者软件工程师。销售部门的领导对你提出了如下要求。

“某某,经会议讨论,我们决定把 运动 T 恤 的销售单价下调 1000 元,同时把 T 恤衫 的销售单价上浮 1000 元,麻烦你去更新一下数据库。”

由于大家已经学习了更新数据的方法——只需要使用 UPDATE 进行更新就可以了,所以肯定会直接回答“知道了,请您放心吧”。

此时的事务由如下两条更新处理所组成。

  • 更新商品信息的事务

    ① 将 运动 T 恤 的销售单价降低 1000

    1
    2
    3
    
    UPDATE Product
    SET sale_price = sale_price - 1000
    WHERE product_name = '运动T恤';
    

    ② 将 T 恤衫 的销售单价上浮 1000

    1
    2
    3
    
    UPDATE Product
    SET sale_price = sale_price + 1000
    WHERE product_name = 'T恤衫';
    

上述 ① 和 ② 的操作一定要作为同一个处理单元执行。

如果只执行了 ① 的操作而忘记了执行 ② 的操作,或者反过来只执行了 ② 的操作而忘记了执行 ① 的操作,一定会受到领导的严厉批评。

遇到这种需要在同一个处理单元中执行一系列更新操作的情况,一定要使用事务来进行处理。

法则 7

事务是需要在同一个处理单元中执行的一系列更新处理的集合。

一个事务中包含多少个更新处理或者包含哪些处理,在 DBMS 中并没有固定的标准,而是根据用户的要求决定的(例如,运动 T 恤T 恤衫 的销售单价需要同时更新这样的要求,DBMS 是无法了解的)。

二、创建事务

如果想在 DBMS 中创建事务,可以按照如下语法结构编写 SQL 语句。

语法 6 事务的语法

1
2
3
4
5
6
7
8
9
事务开始语句;

      DML语句①;
      DML语句②;
      DML语句③;
         .
         .
         .
事务结束语句(COMMIT或者ROLLBACK;

使用事务开始语句和事务结束语句,将一系列 DML 语句(INSERT/UPDATE/DELETE 语句)括起来,就实现了一个事务处理。

这时需要特别注意的是事务的开始语句 1。实际上,在标准 SQL 中并没有定义事务的开始语句,而是由各个 DBMS 自己来定义的。比较有代表性的语法如下所示。

  • SQL Server、PostgreSQL

    BEGIN TRANSACTION

  • MySQL

    START TRANSACTION

  • Oracle、DB2

例如使用之前的那两个 UPDATE(① 和 ②)创建出的事务如代码清单 21 所示。

代码清单 21 更新商品信息的事务

SQL Server PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
BEGIN TRANSACTION;

    -- 将运动T恤的销售单价降低1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';

    -- 将T恤衫的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤衫';

COMMIT;

MySQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
START TRANSACTION;

    -- 将运动T恤的销售单价降低1000日元
    UPDATE Product
       SET sale_price = sale_price - 1000
     WHERE product_name = '运动T恤';

    -- 将T恤衫的销售单价上浮1000日元
    UPDATE Product
       SET sale_price = sale_price + 1000
     WHERE product_name = 'T恤衫';

COMMIT;

Oracle DB2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
   SET sale_price = sale_price - 1000
 WHERE product_name = '运动T恤';

-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
   SET sale_price = sale_price + 1000
 WHERE product_name = 'T恤衫';

COMMIT;

如上所示,各个 DBMS 事务的开始语句都不尽相同,其中 Oracle 和 DB2 并没有定义特定的开始语句。

可能大家觉得这样的设计很巧妙,其实是因为标准 SQL 中规定了一种悄悄开始事务处理 2 的方法。

因此,即使是经验丰富的工程师也经常会忽略事务处理开始的时间点。大家可以试着通过询问“是否知道某个 DBMS 中事务是什么时候开始的”,来测试学校或者公司前辈的数据库知识。

反之,事务的结束需要用户明确地给出指示。结束事务的指令有如下两种。

  • COMMIT——提交处理

    COMMIT 是提交事务包含的全部更新处理的结束指令(图 3),相当于文件处理中的覆盖保存。一旦提交,就无法恢复到事务开始前的状态了。

    因此,在提交之前一定要确认是否真的需要进行这些更新。

    COMMIT 的流程 = 直线进行

    图 3 COMMIT 的流程 = 直线进行

    万一由于误操作提交了包含错误更新的事务,就只能回到重新建表、重新插入数据这样繁琐的老路上了。

    由于可能会造成数据无法恢复的后果,请大家一定要注意(特别是在执行 DELETE 语句的 COMMIT 时尤其要小心)。

    法则 8

    虽然我们可以不清楚事务开始的时间点,但是在事务结束时一定要仔细进行确认。

  • ROLLBACK——取消处理

    ROLLBACK 是取消事务包含的全部更新处理的结束指令(图 4),相当于文件处理中的放弃保存。一旦回滚,数据库就会恢复到事务开始之前的状态(代码清单 22)。

    通常回滚并不会像提交那样造成大规模的数据损失。

    ROLLBACK 的流程 = 掉头回到起点

    图 4 ROLLBACK 的流程 = 掉头回到起点

    代码清单 22 事务回滚的例子

    SQL Server PostgreSQL

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    
    BEGIN TRANSACTION; ------------------- ①
    
        -- 将运动T恤的销售单价降低1000日元
        UPDATE Product
        SET sale_price = sale_price - 1000
        WHERE product_name = '运动T恤';
    
        -- 将T恤衫的销售单价上浮1000日元
        UPDATE Product
        SET sale_price = sale_price + 1000
        WHERE product_name = 'T恤衫';
    
    ROLLBACK;
    

    特定的 SQL

    至此,我们已经知道各个 DBMS 中关于事务的语法不尽相同。

    代码清单 22 中的语句在 MySQL 中执行时需要将 ① 语句改写为“START TRANSACTION”,而在 Oracle 和 DB2 中执行时则无需 ① 语句(请将其删除),具体请参考上一节的“创建事务”。