PostgreSQL 教程: 事务

九月 3, 2023

摘要:在本教程中,您将学习如何使用BEGINCOMMITROLLBACK语句处理 PostgreSQL 事务。

什么是数据库事务

数据库事务是由一个或多个操作组成的单个工作单元。

事务的一个典型例子是从一个账户到另一个账户的银行转账。一笔完整的交易必须保证发送方和接收方账户之间的余额。这意味着如果发送方账户转账X金额,接收方就会收到X金额,不多也不少。

PostgreSQL 事务是原子的、一致的、隔离的和持久的。这些属性通常称为 ACID:

  • 原子性保证事务以全有或全无的方式完成。
  • 一致性确保写入数据库的数据更改必须有效并遵循预定义的规则。
  • 隔离性决定了事务完整性如何对其他事务可见。
  • 持久性确保已提交的事务将永久存储在数据库中。

设置样例表

让我们创建一个新表,名为accounts,用于演示:

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR(100) NOT NULL,
    balance DEC(15,2) NOT NULL,
    PRIMARY KEY(id)
);

开始事务

当您执行以下INSERT语句时:

INSERT INTO accounts(name,balance)
VALUES('Bob',10000);

PostgreSQL 立即将新行插入accounts表中。在这种情况下,您不知道事务何时开始,也无法拦截修改或回滚。

要启动事务,请使用以下语句:

BEGIN TRANSACTION;

或者

BEGIN WORK;

要不就:

BEGIN;

例如,以下语句启动一个新事务并向accounts表中插入一个新帐户:

BEGIN;

INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

从当前会话中,通过查询accounts表可以看到变化:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

PostgreSQL Transaction - from current session

但是,如果您启动一个新会话并执行上面的查询,您将看不到更改。

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

PostgreSQL Transaction - from another session

提交事务

要使更改对其他会话(或用户)可见,您需要使用COMMIT WORK语句提交事务,如下:

COMMIT WORK;

或者

COMMIT TRANSACTION;

或者简单地:

COMMIT;

以下COMMIT语句将 Alice 的帐户插入accounts表中:

COMMIT;

从其他会话中,您可以通过查询accounts表来查看更改:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

PostgreSQL Transaction - commit

执行COMMIT语句后,PostgreSQL 还保证如果发生崩溃,更改将是持久的。

把它们放在一起。

-- start a transaction
BEGIN;

-- insert a new row into the accounts table
INSERT INTO accounts(name,balance)
VALUES('Alice',10000);

-- commit the change (or roll it back later)
COMMIT;

PostgreSQL COMMIT: 银行账户转账示例

在本次演示中,我们将向您展示如何将 1000 美元从 Bob 的账户转入 Alice 的账户。我们将使用两个会话来查看每个操作的变化。

在第一个会话中,启动一个新事务:

BEGIN;

并从 ID 为 1 的 Bob 账户中减去 1000 美元:

UPDATE accounts 
SET balance = balance - 1000
WHERE id = 1;

在第二个会话中,检查两个帐户的帐户余额:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

输出:

PostgreSQL Transaction - from second session

正如您所看到的,更改在其他会话中不可见。

接下来,将相同的金额(1000 美元)添加到 Alice 的帐户中:

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

在我们提交之前,第二个会话也看不到此更改:

COMMIT;

现在,您可以从任何会话查看更改:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

PostgreSQL Transaction - from second session after commit

把它们放在一起。

-- start a transaction
BEGIN;

-- deduct 1000 from account 1
UPDATE accounts 
SET balance = balance - 1000
WHERE id = 1;

-- add 1000 to account 2
UPDATE accounts
SET balance = balance + 1000
WHERE id = 2; 

-- select the data from accounts
SELECT id, name, balance
FROM accounts;

-- commit the transaction
COMMIT;

回滚事务

要回滚或撤消当前事务的更改,可以使用以下任一语句:

ROLLBACK WORK;

或者

ROLLBACK TRANSACTION;

或者用简短的方式:

ROLLBACK;

假设,您想将 1500 美元从 Bob 的账户转入 Alice 的账户。然而,您不小心将钱汇到了 Jack 的帐户而不是 Alice 的帐户。并且您想回滚整个事务。

首先,将 Jack 的帐户添加到accounts表中:

INSERT INTO accounts(name, balance)
VALUES('Jack',0);

接下来,从 Bob 的帐户中减去金额:

BEGIN;

UPDATE accounts 
SET balance = balance - 1500
WHERE id = 1;

然后,将相同的金额添加到 Alice 的帐户中:

UPDATE accounts
SET balance = balance + 1500
WHERE id = 3;

然而,Alice 的帐户 ID 为 2。所以这是一个错误。

要撤消更改,请执行以下ROLLBACK语句:

ROLLBACK;

最后,查看所有账户的余额:

SELECT 
    id,
    name,
    balance
FROM 
    accounts;

PostgreSQL Transaction - Rollback example

正如输出中清楚显示的那样,帐户余额与交易前相同。

把它们放在一起。

-- begin the transaction
BEGIN;

-- deduct the amount from the account 1
UPDATE accounts 
SET balance = balance - 1500
WHERE id = 1;

-- add the amount from the account 3 (instead of 2)
UPDATE accounts
SET balance = balance + 1500
WHERE id = 3; 

-- roll back the transaction
ROLLBACK;

在本教程中,您学习了如何通过BEGINCOMMITROLLBACK语句操作 PostgreSQL 事务。