文档首页/ 数据仓库服务 GaussDB(DWS)/ SQL语法参考/ SQL语法参考(8.3.0.x)/ 事务管理
更新时间：2024-11-05 GMT+08:00
查看PDF
分享

事务管理

GaussDB(DWS)支持数据库事务ACID属性，提供了事务的读已提交隔离级别和可重复读隔离级别。

  • 客户端显式开启事务或手动关闭Autocommit场景，业务最后必须手动执行Commit提交事务。
  • 针对执行时长超过30分钟的语句，建议进行优化。
  • 尽量避免出现执行时长超过2小时的业务，避免长事务、长持锁等影响。
  • 更多开发设计规范参见总体开发设计规范。

事务的概念

  • 事务指一个操作，由多个步骤组成，要么全部成功，要么全部失败。
  • 数据库事务（TRANSACTION）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成（通常由事务开始与事务结束之间执行的全部数据库操作组成），这些操作要么全部执行，要么全部不执行，是一个不可分割的执行单位。

事务的作用

数据库事务的目的主要是：

  • 为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务的执行过程

当事务被提交给数据库管理系统（DBMS）后，DBMS需要确保该事务中的所有操作都成功完成，并且其结果被永久保存在数据库中。如果事务中有操作没有成功完成，则事务中的所有操作都需要回滚，回到事务执行前的状态；同时，该事务对数据库或者其他事务的执行无影响，所有的事务互不干扰和影响，好像在独立的环境中运行。

事务的属性

事务具有以下四个标准属性，通常根据首字母缩写为ACID。

  • Atomicity（原子性）：事务中的全部操作在数据库中是不可分割的，整个事务中的所有操作要么全部完成，要么全部失败，对于一个事务来说，不能只执行其中的一部分操作。

    比如： A给B转账，A扣除500元 ，B增加500元。整个事务的操作要么全部成功，要么全部失败，不能出现A扣钱，但是B不增加的情况。如果原子性不能保证，就会出现一致性问题。

  • Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的数据必须完全符合所有的预设规则，这包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

    比如：A给B转账，A扣除500元 ，B增加500元，扣除的钱-500与增加的钱+500，相加应该为0。如从A账户转账500元到B账户，不管操作成功与否，A和B的存款总额是不变的。

  • Isolation（隔离性）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

    数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。

  • Durability（持久性）：一旦事务提交，则其所做的修改就会永久保存到数据库中。即使系统故障，已经提交的修改数据也不会丢失。
表1 ACID用途

ACID

属性

用途

Atomicity

原子性

并发控制，故障恢复。

Consistency

一致性

SQL的完整性约束（主键约束、外键约束）。

Isolation

隔离性

并发控制。

Durability

持久性

故障恢复。

常用的并发控制技术有基于锁的并发控制和基于时间戳的并发控制，GaussDB(DWS)数据库针对DDL语句采用两阶段锁技术，而针对DML语句则采用多版本控制技术（Multi-Version Concurrency Control，MVCC）。GaussDB(DWS)数据库的故障恢复采用WAL日志的方式来实现，目前主要支持Redo日志，通过Redo日志和MVCC可以保证事务读写的一致性。

隔离级别

Isolation（隔离性）可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离级别，决定多个事务并发操作同一个对象时的处理方式。

GaussDB(DWS)的事务隔离级别，由GUC参数transaction_isolation或SET TRANSACTION语法设置，支持以下隔离级别，默认为读已提交（read committed）。

  • read committed：读已提交隔离级别，只能读到已经提交的数据，而不会读到未提交的数据。
  • read uncommitted：读未提交隔离级别，GaussDB(DWS)不支持read uncommitted，如果设置了read uncommitted，实际上使用的是read committed。
  • repeatable read：可重复读隔离级别，仅仅能看到事务开始之前提交的数据，不能看到未提交的数据，以及在事务执行期间由其它并发事务提交的修改。
  • serializable：事务可序列化，GaussDB(DWS)不支持serializable，如果设置了serializable，实际上使用的是repeatable read。

事务控制语法

  • 启动事务

    GaussDB(DWS)通过START TRANSACTION和BEGIN语法启动事务，请参考START TRANSACTION和BEGIN。

  • 设置事务

    GaussDB(DWS)通过SET TRANSACTION或者SET LOCAL TRANSACTION语法设置事务，请参考SET TRANSACTION。

  • 提交事务

    GaussDB(DWS)通过COMMIT或者END可完成提交事务的功能，即提交事务的所有操作，请参考COMMIT | END。

  • 回滚事务

    回滚是在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销。请参考ROLLBACK。

    数据库中收到的一次执行请求（不在事务块中），如果含有多条语句，将会被打包成一个事务，如果其中有一个语句失败，那么整个请求都将会被回滚。

  • 其他事务操作
    • SAVEPOINT用于在当前事务里建立一个新的保存点。即在一个事务中标记一个位置并且允许做部分回滚。用户可以回滚在一个保存点之后执行的命令但保留该保存点之前执行的命令。请参考SAVEPOINT。
    • ROLLBACK TO SAVEPOINT回滚事务到一个保存点。隐含地删除所有在该保存点之后建立的保存点。请参考ROLLBACK TO SAVEPOINT。
    • RELEASE SAVEPOINT删除一个事务内的保存点。请参考RELEASE SAVEPOINT。

事务场景示例

某顾客在商店使用电子支付购买100元的物品，当中至少包括两个操作：1. 该顾客的账户减少100元。2. 商店账户（商户）增加100元。支持事务的数据库管理系统就是要确保以上两个操作（整个“事务”）都能完成，或一起取消。

  1. 创建样例数据：
    创建一个简单的用户金额表并向表中插入数据（假设商户和顾客的账户上各有500元）。 
    1
2
3
4
5
    		 
    CREATE TABLE customer_info (
	NAME VARCHAR(32) PRIMARY KEY,
	MONEY INTEGER
);
INSERT INTO customer_info (name, money) VALUES ('buyer', 500), ('shop', 500);

    查看表数据显示商户和顾客各有500元。

    1
    		 
    SELECT * FROM customer_info;

  2. 普通操作（正常模式）。

    模拟正常购买过程，顾客先扣款100元，商户再增加款额100元。

    1
2
3
4
    		 
    UPDATE customer_info SET money = money-100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'buyer');
UPDATE customer_info SET money = money+100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'shop');

SELECT * FROM customer_info;

  3. 恢复初始值。 
    1
2
    		 
    UPDATE customer_info SET money=500;
select * from customer_info;

  4. 普通操作（异常模式）。

    模拟购买过程出现状况，顾客发生扣款100元，商户没有增加款额。

    1. 顾客先扣款100元。 
      1
      		 
      UPDATE customer_info SET money = money-100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'buyer');
    2. 商户发现支付有问题，终止了后续交易。商户增加款操作直接报错，终止执行下面的语句。（仅商户觉得支付有问题） 
      1
      		 
      UPDATE customer_info SET money = money+100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'shop');
    3. 查询结果发现：消费者已经扣款，但商户没增加款额，这里顾客的金额了100元。 
      1
      		 
      SELECT * FROM customer_info;

因此，如果没有事务，一旦SQL语句中间出现异常，整个账户系统的收支就不平衡了。

使用数据库事务，模拟出现异常操作时，进行事务回滚。
  1. 恢复初始值： 
    1
    		 
    UPDATE customer_info SET money=500;
  2. 开启事务后，顾客先扣款100元。 
    1
2
    		 
    BEGIN TRANSACTION;
UPDATE customer_info SET money = money-100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'buyer');
  3. 商户增加款额操作直接报错，终止执行下面的语句。 
    1
    		 
    UPDATE customer_info SET money = money+100 WHERE name IN (SELECT name FROM customer_info WHERE name = 'shop');
  4. 回滚事务，在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销。 
    1
2
    		 
    END TRANSACTION;
ROLLBACK
  5. 查询显示顾客和商户的账户金额仍旧完整一致。即数据库在事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，数据库的完整性没有被破坏。 
    1
    		 
    SELECT * FROM customer_info;

两阶段事务

GaussDB(DWS)属于分布式share-nothing架构，表的数据分布在不同的节点上。客户端的一条或多条语句可能会同时修改多个节点上的数据，这种情况下，会产生分布式事务。GaussDB(DWS)采用两阶段提交事务来保证分布式事务中数据的一致性和事务的原子性。顾名思义，两阶段提交就是将事务提交划分为两个阶段，通常针对的是包含写操作的事务。当写操作将数据写入不同的节点时，需要满足事务的原子性要求，要么全部提交，要么全部回滚。

不支持两阶段的场景如下：

  • 不支持显示的两阶段提交语法PREPARE TRANSACTION。 
    1
2
    		 
    BEGIN;
PREPARE TRANSACTION 'p1';

  • 不支持在两阶段事务中修改系统表的文件映射关系。 
    1
    		 
    REINDEX TABLE pg_class;

  • 不支持在跨节点的事务中提交导出事务快照。 
    1
2
3
4
    		 
    BEGIN;
CREATE TABLE t1(a int);
SELECT pg_export_snapshot();
END;