Postgresql 中文操作指南

SET TRANSACTION

SET TRANSACTION —设置当前事务的特性

SET TRANSACTION — set the characteristics of the current transaction

Synopsis

SET TRANSACTION transaction_mode [, ...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

Description

SET TRANSACTION 命令设置了当前事务的特性。它对任何后续事务均无影响。 SET SESSION CHARACTERISTICS 设置会话后续事务的默认事务特性。这些默认值可通过 SET TRANSACTION 针对单个事务进行重写。

The SET TRANSACTION command sets the characteristics of the current transaction. It has no effect on any subsequent transactions. SET SESSION CHARACTERISTICS sets the default transaction characteristics for subsequent transactions of a session. These defaults can be overridden by SET TRANSACTION for an individual transaction.

可用的事务特性是事务隔离级别、事务访问模式（读/写或只读）和可延迟模式。此外，可以选中快照，但仅适用于当前事务，不能作为会话默认值。

The available transaction characteristics are the transaction isolation level, the transaction access mode (read/write or read-only), and the deferrable mode. In addition, a snapshot can be selected, though only for the current transaction, not as a session default.

事务的隔离级别决定了当其他事务同时运行时，该事务可以看到哪些数据：

The isolation level of a transaction determines what data the transaction can see when other transactions are running concurrently:

  • READ COMMITTED

    • A statement can only see rows committed before it began. This is the default.

  • REPEATABLE READ

    • All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.

  • SERIALIZABLE

    • All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.

SQL 标准定义了附加级别 READ UNCOMMITTED 。在 PostgreSQL 中， READ UNCOMMITTED 被视为 READ COMMITTED 。

The SQL standard defines one additional level, READ UNCOMMITTED. In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED.

在事务的第一个查询或数据修改语句（ SELECT 、 INSERT 、 DELETE 、 UPDATE 、 MERGE 、 FETCH 或 COPY ）已执行后，事务的隔离级别不能更改。请参阅 Chapter 13 以了解更多有关事务隔离和并发控制的信息。

The transaction isolation level cannot be changed after the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, FETCH, or COPY) of a transaction has been executed. See Chapter 13 for more information about transaction isolation and concurrency control.

事务访问模式确定事务是读/写还是只读。读/写是默认模式。当事务处于只读模式时，禁止以下 SQL 命令： INSERT 、 UPDATE 、 DELETE 、 MERGE 和 COPY FROM （如果它们要写入的表不是临时表）；所有 CREATE 、 ALTER 和 DROP 命令； COMMENT 、 GRANT 、 REVOKE 、 TRUNCATE ；以及 EXPLAIN ANALYZE 和 EXECUTE ，如果它们要执行的命令在所列命令中。这是一个高级只读概念，它不会阻止所有写入到磁盘。

The transaction access mode determines whether the transaction is read/write or read-only. Read/write is the default. When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, MERGE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk.

除非事务同时是 SERIALIZABLE 和 READ ONLY ，否则 DEFERRABLE 事务属性没有任何效果。当事务选中所有这三个属性时，事务可能会在第一次获取其快照时阻塞，之后它能够在没有 SERIALIZABLE 事务的正常开销的情况下运行，并且没有任何风险导致序列化失败或被其取消。此模式非常适合长时间运行的报告或备份。

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups.

SET TRANSACTION SNAPSHOT 命令允许新事务与现有事务运行相同的 snapshot 。预先存在的交易必须使用 pg_export_snapshot 函数导出了其快照（参见 Section 9.27.5 ）。该函数返回一个快照标识符，必须将其提供给 SET TRANSACTION SNAPSHOT 以指定要导入哪个快照。标识符必须在此命令中写为字符串文字，例如 '00000003-0000001B-1' 。 SET TRANSACTION SNAPSHOT 只能在事务的开始执行，在事务的第一个查询或数据修改语句 ( SELECT 、 INSERT 、 DELETE 、 UPDATE 、 MERGE 、 FETCH 或 COPY ) 之前执行。此外，事务必须已经设置为 SERIALIZABLE 或 REPEATABLE READ 隔离级别（否则，快照将立即被丢弃，因为 READ COMMITTED 模式为每个命令采用一个新快照）。如果导入事务使用 SERIALIZABLE 隔离级别，则导出快照的事务也必须使用该隔离级别。此外，非只读可序列化事务无法从只读事务导入快照。

The SET TRANSACTION SNAPSHOT command allows a new transaction to run with the same snapshot as an existing transaction. The pre-existing transaction must have exported its snapshot with the pg_export_snapshot function (see Section 9.27.5). That function returns a snapshot identifier, which must be given to SET TRANSACTION SNAPSHOT to specify which snapshot is to be imported. The identifier must be written as a string literal in this command, for example '00000003-0000001B-1'. SET TRANSACTION SNAPSHOT can only be executed at the start of a transaction, before the first query or data-modification statement (SELECT, INSERT, DELETE, UPDATE, MERGE, FETCH, or COPY) of the transaction. Furthermore, the transaction must already be set to SERIALIZABLE or REPEATABLE READ isolation level (otherwise, the snapshot would be discarded immediately, since READ COMMITTED mode takes a new snapshot for each command). If the importing transaction uses SERIALIZABLE isolation level, then the transaction that exported the snapshot must also use that isolation level. Also, a non-read-only serializable transaction cannot import a snapshot from a read-only transaction.

Notes

如果在没有先前的 START TRANSACTION 或 BEGIN 的情况下执行 SET TRANSACTION ，它会发出一个警告，否则没有效果。

If SET TRANSACTION is executed without a prior START TRANSACTION or BEGIN, it emits a warning and otherwise has no effect.

可以通过在 BEGIN 或 START TRANSACTION 中指定所需的 transaction_modes 来取消 SET TRANSACTION 。但该选项不适用于 SET TRANSACTION SNAPSHOT 。

It is possible to dispense with SET TRANSACTION by instead specifying the desired transaction_modes in BEGIN or START TRANSACTION. But that option is not available for SET TRANSACTION SNAPSHOT.

还可以通过配置参数 default_transaction_isolation 、 default_transaction_read_only 和 default_transaction_deferrable 来设置或检查会话默认事务模式。（实际上， SET SESSION CHARACTERISTICS 只是使用 SET 设置这些变量的一个冗长的等价项。）这意味着可以在配置文件中通过 ALTER DATABASE 等等来设置默认值。请参阅 Chapter 20 了解更多信息。

The session default transaction modes can also be set or examined via the configuration parameters default_transaction_isolation, default_transaction_read_only, and default_transaction_deferrable. (In fact SET SESSION CHARACTERISTICS is just a verbose equivalent for setting these variables with SET.) This means the defaults can be set in the configuration file, via ALTER DATABASE, etc. Consult Chapter 20 for more information.

当前事务的模式也可以通过配置参数 transaction_isolation 、 transaction_read_only 和 transaction_deferrable 来设置或检查。设置其中一个参数的作用与相应的 SET TRANSACTION 选项相同，并且可以应用相同的限制。但是，这些参数不能在配置文件中设置，也不能从实时 SQL 以外的任何来源设置。

The current transaction’s modes can similarly be set or examined via the configuration parameters transaction_isolation, transaction_read_only, and transaction_deferrable. Setting one of these parameters acts the same as the corresponding SET TRANSACTION option, with the same restrictions on when it can be done. However, these parameters cannot be set in the configuration file, or from any source other than live SQL.

Examples

要开始一个与已有事务使用相同快照的新事务，首先从现有事务导出快照。这将返回快照标识符，例如：

To begin a new transaction with the same snapshot as an already existing transaction, first export the snapshot from the existing transaction. That will return the snapshot identifier, for example:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
 pg_export_snapshot
---------------------
 00000003-0000001B-1
(1 row)

然后在新建事务的开头在 SET TRANSACTION SNAPSHOT 命令中给出快照标识符：

Then give the snapshot identifier in a SET TRANSACTION SNAPSHOT command at the beginning of the newly opened transaction:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-0000001B-1';

Compatibility

这些命令是在 SQL 标准中定义的，但 DEFERRABLE 事务模式和 SET TRANSACTION SNAPSHOT 表单除外，它们是 PostgreSQL 扩展。

These commands are defined in the SQL standard, except for the DEFERRABLE transaction mode and the SET TRANSACTION SNAPSHOT form, which are PostgreSQL extensions.

SERIALIZABLE 是标准中的默认事务隔离级别。在 PostgreSQL 中，默认值通常是 READ COMMITTED ，但如上所述，您可以更改它。

SERIALIZABLE is the default transaction isolation level in the standard. In PostgreSQL the default is ordinarily READ COMMITTED, but you can change it as mentioned above.

在 SQL 标准中，还有另一个可以用这些命令设置的事务特性：诊断区域的大小。这个概念特定于嵌入式 SQL，因此在 PostgreSQL 服务器中没有实现。

In the SQL standard, there is one other transaction characteristic that can be set with these commands: the size of the diagnostics area. This concept is specific to embedded SQL, and therefore is not implemented in the PostgreSQL server.

SQL 标准要求在连续的 transaction_modes 之间使用逗号，但出于历史原因，PostgreSQL 允许省略逗号。

The SQL standard requires commas between successive transaction_modes, but for historical reasons PostgreSQL allows the commas to be omitted.