Postgresql 简明教程

PostgreSQL - TRANSACTIONS

事务是对数据库执行的一个工作单元。事务是按照逻辑顺序完成的工作单元或序列，无论是由用户手动完成还是由某种数据库程序自动完成。

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

事务是将一个或多个更改传播到数据库的过程。例如，创建记录、更新记录、或从表中删除记录，即正在对表进行事务。控制事务以确保数据完整性和处理数据库错误非常重要。

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record, updating a record, or deleting a record from the table, then you are performing transaction on the table. It is important to control transactions to ensure data integrity and to handle database errors.

实际上，你会把许多 PostgreSQL 查询组合成一个组，然后一起执行它们作为事务的一部分。

Practically, you will club many PostgreSQL queries into a group and you will execute all of them together as a part of a transaction.

Properties of Transactions

事务具有以下四个标准属性，通常用首字母缩写词 ACID 提到 -

Transactions have the following four standard properties, usually referred to by the acronym ACID −

  1. Atomicity − Ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.

  2. Consistency − Ensures that the database properly changes states upon a successfully committed transaction.

  3. Isolation − Enables transactions to operate independently of and transparent to each other.

  4. Durability − Ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control

以下命令用于控制事务 −

The following commands are used to control transactions −

  1. BEGIN TRANSACTION − To start a transaction.

  2. COMMIT − To save the changes, alternatively you can use END TRANSACTION command.

  3. ROLLBACK − To rollback the changes.

事务控制命令仅用于 DML 命令 INSERT、UPDATE 和 DELETE。它们不能用于创建表或删除表，因为这些操作会自动提交到数据库中。

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only. They cannot be used while creating tables or dropping them because these operations are automatically committed in the database.

The BEGIN TRANSACTION Command

可以使用 BEGIN TRANSACTION 或简单的 BEGIN 命令开始事务。此类事务通常会持续到遇到下一个 COMMIT 或 ROLLBACK 命令。但是如果数据库关闭或出现错误，事务也会进行 ROLLBACK。

Transactions can be started using BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. But a transaction will also ROLLBACK if the database is closed or if an error occurs.

以下是开始事务的简单语法 −

The following is the simple syntax to start a transaction −

BEGIN;

or

BEGIN TRANSACTION;

The COMMIT Command

COMMIT 命令是用于将由事务调用的更改保存到数据库的事务命令。

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.

COMMIT 命令将自上一个 COMMIT 或 ROLLBACK 命令以来的所有事务保存到数据库中。

The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.

COMMIT 命令的语法如下 −

The syntax for COMMIT command is as follows −

COMMIT;

or

END TRANSACTION;

The ROLLBACK Command

ROLLBACK 命令是用于撤销尚未保存到数据库的事务的事务命令。

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

ROLLBACK 命令只能用来撤销自上次发出 COMMIT 或 ROLLBACK 命令以来的事务。

The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

ROLLBACK 命令的语法如下 −

The syntax for ROLLBACK command is as follows −

ROLLBACK;

Example

考虑 COMPANY 表有以下记录 −

Consider the COMPANY table is having the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

现在，我们开始一个事务并从表中删除年龄 = 25 的记录，最后我们使用 ROLLBACK 命令撤消所有更改。

Now, let us start a transaction and delete records from the table having age = 25 and finally we use ROLLBACK command to undo all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

如果您将检查 COMPANY 表，它仍将有以下记录 −

If you will check COMPANY table is still having the following records −

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

现在，我们开始另一个事务并从表中删除年龄 = 25 的记录，最后我们使用 COMMIT 命令提交所有更改。

Now, let us start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

如果您将检查 COMPANY 表，它仍在有以下记录 −

If you will check the COMPANY table, it still has the following records −

 id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)