Babelfish 中的事务隔离级别 - Amazon Aurora
事务隔离级别概述设置事务隔离级别启用或禁用事务隔离级别Babelfish 与 SQL Server 隔离级别之间的区别
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

Babelfish 中的事务隔离级别

Babelfish 支持事务隔离级别 READ UNCOMMITTED、READ COMMITTED 和 SNAPSHOT。从 Babelfish 3.4 版本开始，支持额外的隔离级别 REPEATABLE READ 和 SERIALIZABLE。PostgreSQL 中相应隔离级别的行为支持 Babelfish 中的所有隔离级别。SQL Server 和 Babelfish 使用不同的底层机制来实现事务隔离级别（阻止并发访问、事务持有的锁、错误处理等）。而且，不同工作负载的并发访问方式可能存在一些细微差异。有关此 PostgreSQL 行为的更多信息，请参阅 Transaction Isolation。

事务隔离级别概述

最初的 SQL Server 事务隔离级别基于保守锁，其中只存在一个数据副本，查询在访问行等资源之前必须锁定这些资源。后来，引入了 Read Committed 隔离级别的变体。这使得行版本的使用能够在使用非阻止访问的读取器和写入器之间提供更好的并发性。此外，还提供了一个名为 Snapshot 的新隔离级别。它也使用行版本来提供比 REPEATABLE READ 隔离级别更好的并发性，方法是避免对读取数据使用共享锁，这些锁一直保留直至事务结束。

与 SQL Server 不同，Babelfish 中的所有事务隔离级别都基于乐观锁（MVCC）。无论底层数据的当前状态如何，每个事务都可以在语句（READ COMMITTED）的开头或事务（REPEATABLE READ、SERIALIZABLE）的开头看到数据的快照。因此，Babelfish 中并发事务的执行行为可能与 SQL Server 不同。

例如，设想有一个隔离级别为 SERIALIZABLE 的事务，该事务最初在 SQL Server 中被阻止，但后来成功了。由于与读取或更新相同行的并发事务存在序列化冲突，该事务最终可能会在 Babelfish 中失败。在某些情况下，与 SQL Server 相比，在 Babelfish 中执行多个并发事务可能会产生不同的最终结果。对于使用隔离级别的应用程序，应针对并发场景进行全面测试。

SQL Server 中的隔离级别 Babelfish 隔离级别 PostgreSQL 隔离级别 注释

READ UNCOMMITTED

READ UNCOMMITTED

READ UNCOMMITTED

在 Babelfish/PostgreSQL 中，Read Uncommitted 与 Read Committed 相同

READ COMMITTED

READ COMMITTED

READ COMMITTED

SQL Server Read Committed 基于保守锁，Babelfish Read Committed 基于快照（MVCC）。

READ COMMITTED SNAPSHOT

READ COMMITTED

READ COMMITTED

两者都基于快照（MVCC），但并不完全相同。

SNAPSHOT

SNAPSHOT

REPEATABLE READ

完全相同。

REPEATABLE READ

REPEATABLE READ

REPEATABLE READ

SQL Server Repeatable Read 基于保守锁，Babelfish Repeatable Read 基于快照（MVCC）。

可序列化

可序列化

可序列化

SQL Server Serializable 是保守隔离，Babelfish Serializable 基于快照（MVCC）。
注意

目前不支持表提示，其行为是通过使用 Babelfish 预定义的备用方案 escape_hatch_table_hints 来控制的。

设置事务隔离级别

使用以下命令设置事务隔离级别：


SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }

启用或禁用事务隔离级别

在 Babelfish 中，事务隔离级别 REPEATABLE READ 和 SERIALIZABLE 默认处于禁用状态，您必须使用 sp_babelfish_configure 将 babelfishpg_tsql.isolation_level_serializable 或 babelfishpg_tsql.isolation_level_repeatable_read 备用方案设置为 pg_isolation 来显式启用它们。有关更多信息，请参阅使用转义孵化管理 Babelfish 错误处理。

以下是通过设置各自的备用方案来启用或禁用在当前会话中使用 REPEATABLE READ 和 SERIALIZABLE 的示例。（可选）包括 server 参数，用于为当前会话以及所有后续新会话设置备用方案。

仅在当前会话中启用 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'

在当前会话和所有后续新会话中启用 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation', 'server'

在当前会话和后续新会话中禁用 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ。

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'off', 'server'

仅在当前会话中启用 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'

在当前会话和所有后续新会话中启用 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation', 'server'

在当前会话和后续新会话中禁用 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE。

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'off', 'server'

Babelfish 与 SQL Server 隔离级别之间的区别

以下是一些关于 SQL Server 和 Babelfish 如何实现 ANSI 隔离级别的细微差别的示例。

注意

  • 隔离级别 Repeatable Read 和 Snapshot 在 Babelfish 中是相同的。

  • 隔离级别 Read Uncommitted 和 Read Committed 在 Babelfish 中是相同的。

以下示例显示了如何为下面提到的所有示例创建基表：

CREATE TABLE employee (
    id sys.INT NOT NULL PRIMARY KEY,
    name sys.VARCHAR(255)NOT NULL,
    age sys.INT NOT NULL
);
INSERT INTO employee (id, name, age) VALUES (1, 'A', 10);
INSERT INTO employee (id, name, age) VALUES (2, 'B', 20);
INSERT INTO employee (id, name, age) VALUES (3, 'C', 30);

BABELFISH READ UNCOMMITTED 与 SQL SERVER READ UNCOMMITTED 隔离级别

SQL SERVER 中的脏读
事务 1 事务 2 SQL Server Read Uncommitted Babelfish Read Uncommitted

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE employee SET age=0;

更新成功。

更新成功。

INSERT INTO employee VALUES (4, 'D', 40);

插入成功。

插入成功。

SELECT * FROM employee;

事务 1 可以看到事务 2 中未提交的更改。

与 Babelfish 中的 Read Committed 相同。事务 2 中未提交的更改对事务 1 不可见。

COMMIT

SELECT * FROM employee;

看到事务 2 提交的更改。

看到事务 2 提交的更改。

BABELFISH READ COMMITTED 与 SQL SERVER READ COMMITTED 隔离级别

读取 - 写入阻止
事务 1 事务 2 SQL Server Read Committed Babelfish Read Committed

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM employee;

UPDATE employee SET age=100 WHERE id = 1;

更新成功。

更新成功。

UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);

在事务 2 提交之前，步骤被阻止。

事务 2 的更改尚不可见。更新 id=3 的行。

COMMIT

事务 2 成功提交。事务 1 现已解除阻止，并且可以看到事务 2 的更新。

事务 2 成功提交。

SELECT * FROM employee;

事务 1 更新 id = 1 的行。

事务 1 更新 id = 3 的行。

BABELFISH READ COMMITTED 与 SQL SERVER READ COMMITTED SNAPSHOT 隔离级别

对新插入的行的阻止行为
事务 1 事务 2 SQL Server Read Committed Snapshot Babelfish Read Committed

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

INSERT INTO employee VALUES (4, 'D', 40);

UPDATE employee SET age = 99;

在事务 1 提交之前，步骤会被阻止。插入的行被事务 1 锁定。

更新了三行。新插入的行尚不可见。

COMMIT

提交成功。事务 2 现已解除阻止。

提交成功。

SELECT * FROM employee;

所有 4 行都具有 age=99。

id = 4 的行具有年龄值 40，因为在更新查询期间，事务 2 看不到该行。其它行更新为 age=99。

BABELFISH REPEATABLE READ 与 SQL SERVER REPEATABLE READ 隔离级别

读取/写入阻止行为
事务 1 事务 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

UPDATE employee SET name='A_TXN1' WHERE id=1;

SELECT * FROM employee WHERE id != 1;

SELECT * FROM employee;

在事务 1 提交之前，事务 2 将被阻止。

事务 2 正常进行。

COMMIT

SELECT * FROM employee;

事务 1 中的更新可见。

事务 1 中的更新不可见。

COMMIT

SELECT * FROM employee;

看到事务 1 中的更新。

看到事务 1 中的更新。
写入/写入阻止行为
事务 1 事务 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE employee SET name='A_TXN1' WHERE id=1;

UPDATE employee SET name='A_TXN2' WHERE id=1;

事务 2 已阻止。

事务 2 已阻止。

COMMIT

提交成功且事务 2 已解除阻止。

提交成功，事务 2 失败并出现错误，原因是由于并发更新而导致无法序列化访问。

COMMIT

提交成功。

事务 2 已中止。

SELECT * FROM employee;

id=1 的行具有 name='A_TX2'。

id=1 的行具有 name='A_TX1'。
幻读
事务 1 事务 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

INSERT INTO employee VALUES (4, 'NewRowName', 20);

事务 2 在没有任何阻止的情况下继续进行。

事务 2 在没有任何阻止的情况下继续进行。

SELECT * FROM employee;

新插入的行可见。

新插入的行可见。

COMMIT

SELECT * FROM employee;

事务 2 插入的新行可见。

事务 2 插入的新行不可见。

COMMIT

SELECT * FROM employee;

新插入的行可见。

新插入的行可见。
不同的最终结果
事务 1 事务 2 SQL Server Repeatable Read Babelfish Repeatable Read

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

UPDATE employee SET age = 100 WHERE age IN (SELECT MIN(age) FROM employee);

事务 1 更新 id 为 1 的行。

事务 1 更新 id 为 1 的行。

UPDATE employee SET age = 0 WHERE age IN (SELECT MAX(age) FROM employee);

由于 SELECT 语句尝试读取事务 1 中由 UPDATE 查询锁定的行，因此事务 2 被阻止。

事务 2 在没有任何阻止的情况下继续进行，因为读取从不会被阻止，SELECT 语句执行，最后更新了 id = 3 的行，因为事务 1 更改尚不可见。

SELECT * FROM employee;

此步骤在事务 1 提交后执行。id = 1 的行在上一步中由事务 2 更新，此处可见。

id = 3 的行由事务 2 更新。

COMMIT

事务 2 现已解除阻止。

提交成功。

COMMIT

SELECT * FROM employee;

两个事务都对 id = 1 的行执行更新。

事务 1 和 2 更新不同的行。

BABELFISH SERIALIZABLE 与 SQL SERVER SERIALIZABLE 隔离级别

SQL SERVER 中的范围锁定
事务 1 事务 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

INSERT INTO employee VALUES (4, 'D', 35);

在事务 1 提交之前，事务 2 将被阻止。

事务 2 在没有任何阻止的情况下继续进行。

SELECT * FROM employee;

COMMIT

事务 1 成功提交。事务 2 现已解除阻止。

事务 1 成功提交。

COMMIT

SELECT * FROM employee;

新插入的行可见。

新插入的行可见。
不同的最终结果
事务 1 事务 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

UPDATE employee SET age =99 WHERE id = 4;

在事务 2 提交之前，事务 1 将被阻止。

事务 1 在没有任何阻止的情况下继续进行。

COMMIT

事务 2 成功提交。事务 1 现已解除阻止。

事务 2 成功提交。

COMMIT

SELECT * FROM employee;

可以看到新插入的年龄值 = 99 的行。

可以看到新插入的年龄值 = 40 的行。
使用唯一约束插入到表
事务 1 事务 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

INSERT INTO employee VALUES (4, 'D', 40);

INSERT INTO employee VALUES ((SELECT MAX(id)+1 FROM employee), 'E', 50);

在事务 2 提交之前，事务 1 将被阻止。

在事务 2 提交之前，事务 1 将被阻止。

COMMIT

事务 2 成功提交。事务 1 现已解除阻止。

事务 2 成功提交。事务 1 中止，出现错误，原因是重复键值违反了唯一约束。

COMMIT

事务 1 成功提交。

事务 1 提交失败，原因是由于事务之间的读/写依赖关系，无法序列化访问。

SELECT * FROM employee;

插入行 (5, 'E', 50)。

仅存在 4 行。

在 Babelfish 中，如果这些事务的执行与这些事务的所有可能的串行（一次一个）执行不一致，则以隔离级别 Serializable 运行的并发事务将失败，并出现序列化异常错误。

序列化异常
事务 1 事务 2 SQL Server Serializable Babelfish Serializable

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

在事务 1 提交之前，事务 2 将被阻止。

事务 2 在没有任何阻止的情况下继续进行。

UPDATE employee SET age=35 WHERE age=30;

COMMIT

事务 1 成功提交。

事务 1 首先提交，并且能够成功提交。

COMMIT

事务 2 成功提交。

事务 2 提交失败并出现序列化错误，整个事务已回滚。重试事务 2。

SELECT * FROM employee;

这两个事务的更改都是可见的。

事务 2 已回滚。只能看到事务 1 更改。

在 Babelfish 中，只有当所有并发事务都以 SERIALIZABLE 隔离级别执行时，才可能出现序列化异常。例如，让我们以上面的示例为例，但将事务 2 改为隔离级别 REPEATABLE READ。

事务 1 事务 2 SQL Server 隔离级别 Babelfish 隔离级别

BEGIN TRANSACTION

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

在事务 1 提交之前，事务 2 将被阻止。

事务 2 在没有任何阻止的情况下继续进行。

UPDATE employee SET age=35 WHERE age=30;

COMMIT

事务 1 成功提交。

事务 1 成功提交。

COMMIT

事务 2 成功提交。

事务 2 成功提交。

SELECT * FROM employee;

这两个事务的更改都是可见的。

这两个事务的更改都是可见的。