Níveis de isolamento de transações no Babelfish - Amazon Aurora
Visão geral dos níveis de isolamento de transaçõesConfigurar os níveis de isolamento de transaçõesHabilitar ou desabilitar os níveis de isolamento de transaçõesDiferenças entre os níveis de isolamento do Babelfish e do SQL Server

Níveis de isolamento de transações no Babelfish

O Babelfish é compatível com os níveis de isolamento de transações READ UNCOMMITTED, READ COMMITTED e SNAPSHOT. A partir da versão 3.4 do Babelfish, os níveis de isolamento adicionais REPEATABLE READ e SERIALIZABLE são compatíveis. Todos os níveis de isolamento no Babelfish são aceitos com o comportamento dos níveis de isolamento correspondentes no PostgreSQL. O SQL Server e o Babelfish usam mecanismos subjacentes diferentes para implementar níveis de isolamento de transações (bloqueio de acesso simultâneo, bloqueios mantidos por transações, tratamento de erros, etc.). E há algumas diferenças sutis na forma como o acesso simultâneo pode funcionar para diferentes workloads. Para ter mais informações sobre esse comportamento do PostgreSQL, consulte Transaction Isolation.

Visão geral dos níveis de isolamento de transações

Os níveis originais de isolamento de transações do SQL Server são baseados no bloqueio pessimista, em que existe apenas uma cópia dos dados e as consultas devem bloquear recursos, como linhas, antes de acessá-los. Posteriormente, foi introduzida uma variação do nível de isolamento de leitura confirmada. Isso permite o uso de versões em linha para oferecer melhor simultaneidade entre leitores e gravadores usando acesso sem bloqueio. Além disso, um novo nível de isolamento chamado Snapshot está disponível. Ele também usa versões de linha para oferecer melhor simultaneidade do que o nível de isolamento REPEATABLE READ, evitando bloqueios compartilhados nos dados de leitura que são mantidos até o final da transação.

Ao contrário do SQL Server, todos os níveis de isolamento de transações no Babelfish são baseados no bloqueio positivo (MVCC). Cada transação vê um snapshot dos dados no início da declaração (READ COMMITTED) ou no início da transação (REPEATABLE READ, SERIALIZABLE), independentemente do estado atual dos dados subjacentes. Portanto, o comportamento de execução de transações simultâneas no Babelfish pode ser diferente do comportamento no SQL Server.

Por exemplo, pense em uma transação com nível de isolamento SERIALIZABLE que inicialmente é bloqueada no SQL Server, mas é bem-sucedida posteriormente. Ela pode acabar falhando no Babelfish devido a um conflito de serialização com uma transação simultânea que lê ou atualiza as mesmas linhas. Também pode haver casos em que a execução de várias transações simultâneas produza um resultado final diferente no Babelfish em comparação ao SQL Server. As aplicações que usam níveis de isolamento devem ser testados de forma completa para cenários de concorrência.

Níveis de isolamento no SQL Server Nível de isolamento do Babelfish Nível de isolamento do PostgreSQL Comentários

READ UNCOMMITTED

READ UNCOMMITTED

READ UNCOMMITTED

Read Uncommited é o mesmo que Read Committed no Babelfish/PostgreSQL.

READ COMMITTED

READ COMMITTED

READ COMMITTED

O Read Committed do SQL Server é baseado em bloqueio pessimista, o Read Committed do Babelfish é baseado em snapshot (MVCC).

READ COMMITTED SNAPSHOT

READ COMMITTED

READ COMMITTED

Ambos se baseiam em snapshots (MVCC), mas não são exatamente iguais.

SNAPSHOT

SNAPSHOT

REPEATABLE READ

Exatamente o mesmo.

REPEATABLE READ

REPEATABLE READ

REPEATABLE READ

O Repeatable Read do SQL Server se baseia em bloqueio pessimista, o Read Repeatable do Babelfish é baseado em snapshots (MVCC).

SERIALIZÁVEL

SERIALIZÁVEL

SERIALIZÁVEL

O Serializable do SQL Server é um isolamento pessimista, o Serializable do Babelfish é baseado em snapshots (MVCC).
nota

As dicas da tabela não são aceitas atualmente e o comportamento é controlado com o uso do hatch de escape predefinido do Babelfish escape_hatch_table_hints.

Configurar os níveis de isolamento de transações

Use o comando a seguir para definir o nível de isolamento de transações:


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

Habilitar ou desabilitar os níveis de isolamento de transações

Os níveis de isolamento de transações REPEATABLE READ e SERIALIZABLE estão desabilitados por padrão no Babelfish e você deve habilitá-los explicitamente definindo o hatch de escape babelfishpg_tsql.isolation_level_serializable ou babelfishpg_tsql.isolation_level_repeatable_read como pg_isolation usando sp_babelfish_configure. Para obter mais informações, consulte Gerenciar o tratamento de erros do Babelfish com hatches de escape.

Veja exemplos para habilitar ou desabilitar o uso de REPEATABLE READ e SERIALIZABLE na sessão atual definindo os respectivos hatches de escape. Opcionalmente, inclua um parâmetro server para definir o hatch de escape para a sessão atual, bem como para todas as novas sessões subsequentes.

Como habilitar o uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ somente na sessão atual. 

EXECUTE sp_babelfish_configure 'isolation_level_repeatable_read', 'pg_isolation'

Como habilitar o uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ na sessão atual e em todas as novas sessões subsequentes. 

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

Como desabilitar o uso de SET TRANSACTION ISOLATION LEVEL REPEATABLE READ na sessão atual e nas novas sessões subsequentes. 

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

Como habilitar o uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE somente na sessão atual. 

EXECUTE sp_babelfish_configure 'isolation_level_serializable', 'pg_isolation'

Como habilitar o uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE na sessão atual e em todas as novas sessões subsequentes. 

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

Como desabilitar o uso de SET TRANSACTION ISOLATION LEVEL SERIALIZABLE na sessão atual e nas novas sessões subsequentes. 

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

Diferenças entre os níveis de isolamento do Babelfish e do SQL Server

Veja alguns exemplos das nuances na forma como o SQL Server e o Babelfish implementam os níveis de isolamento ANSI.

nota

  • Os níveis de isolamento Repeatable Read e Snapshot são os mesmos no Babelfish.

  • O nível de isolamento Read Uncommitted e Read Committed são os mesmos no Babelfish.

O exemplo a seguir mostra como criar a tabela base para todos os exemplos mencionados abaixo:

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);

READ UNCOMMITTED DO BABELFISH VERSUS READ UNCOMMITTED ISOLATION LEVEL DO SQL SERVER

LEITURAS SUJAS NO SQL SERVER
Transação 1 Transação 2 Read Uncommitted do SQL Server Read Uncommitted do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE employee SET age=0;

Atualização bem-sucedida.

Atualização bem-sucedida.

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

Inserção bem-sucedida.

Inserção bem-sucedida.

SELECT * FROM employee;

A Transação 1 pode ver as alterações não confirmadas da Transação 2.

O mesmo que Read Committed em Babelfish. Alterações não confirmadas da Transação 2 não são visíveis na Transação 1.

COMMIT

SELECT * FROM employee;

Vê as alterações confirmadas pela Transação 2.

Vê as alterações confirmadas pela Transação 2.

READ COMMITTED DO BABELFISH VERSUS READ COMMITTED ISOLATION LEVEL DO SQL SERVER

BLOQUEIO DE LEITURA E GRAVAÇÃO
Transação 1 Transação 2 Read Committed do SQL Server Read Committed do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT * FROM employee;

UPDATE employee SET age=100 WHERE id = 1;

Atualização bem-sucedida.

Atualização bem-sucedida.

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

Etapa bloqueada até que a Transação 2 seja confirmada.

As alterações da transação 2 ainda não estão visíveis. Updates row with id=3.

COMMIT

A transação 2 foi confirmada com êxito. A Transação 1 agora está desbloqueada e vê a atualização da Transação 2.

A transação 2 foi confirmada com êxito.

SELECT * FROM employee;

A transação 1 atualiza a linha com id = 1.

A transação 1 atualiza a linha com id = 3.

READ COMMITTED DO BABELFISH VERSUS READ COMMITTED SNAPSHOT ISOLATION LEVEL DO SQL SERVER

BLOCKING BEHAVIOUR ON NEW INSERTED ROWS
Transação 1 Transação 2 Read Committed Snapshot do SQL Server Read Committed do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

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

UPDATE employee SET age = 99;

Etapa bloqueada até que a Transação 1 seja confirmada. A linha inserida está bloqueada pela transação 1.

Três linhas atualizadas. A linha recém-inserida ainda não está visível.

COMMIT

Confirmação bem-sucedida. A transação 2 agora está desbloqueada.

Confirmação bem-sucedida.

SELECT * FROM employee;

Todas as quatro linhas têm idade = 99.

A linha com id = 4 tem o valor de idade 40, pois não estava visível para a transação 2 durante a consulta de atualização. Outras linhas são atualizadas para idade = 99.

REPEATABLE READ DO BABELFISH VERSUS REPEATABLE READ ISOLATION LEVEL DO SQL SERVER

COMPORTAMENTO DE BLOQUEIO DE LEITURA/GRAVAÇÃO
Transação 1 Transação 2 Repeatable Read do SQL Server Repeatable Read do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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;

A transação 2 permanece bloqueada até que a Transação 1 seja confirmada.

A transação 2 prossegue normalmente.

COMMIT

SELECT * FROM employee;

A atualização da Transação 1 está visível.

A atualização da Transação 1 não está visível.

COMMIT

SELECT * FROM employee;

vê a atualização da Transação 1.

vê a atualização da Transação 1.
COMPORTAMENTO DE BLOQUEIO DE GRAVAÇÃO/GRAVAÇÃO
Transação 1 Transação 2 Repeatable Read do SQL Server Repeatable Read do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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;

Transação 2 bloqueada.

Transação 2 bloqueada.

COMMIT

A confirmação foi bem-sucedida e a transação 2 foi desbloqueada.

A confirmação foi bem-sucedida e a transação 2 falha com erro, não foi possível serializar o acesso devido à atualização simultânea.

COMMIT

Confirmação bem-sucedida.

A transação 2 já foi cancelada.

SELECT * FROM employee;

Row with id=1 has name='A_TX2'.

Row with id=1 has name='A_TX1'.
PHANTOM READ
Transação 1 Transação 2 Repeatable Read do SQL Server Repeatable Read do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT * FROM employee;

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

A transação 2 prossegue sem nenhum bloqueio.

A transação 2 prossegue sem nenhum bloqueio.

SELECT * FROM employee;

A linha recém-inserida está visível.

A linha recém-inserida está visível.

COMMIT

SELECT * FROM employee;

A nova linha inserida pela transação 2 está visível.

A nova linha inserida pela transação 2 não está visível.

COMMIT

SELECT * FROM employee;

A linha recém-inserida está visível.

A linha recém-inserida está visível.
DIFFERENT FINAL RESULTS
Transação 1 Transação 2 Repeatable Read do SQL Server Repeatable Read do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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);

A transação 1 atualiza a linha com id 1.

A transação 1 atualiza a linha com id 1.

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

A transação 2 está bloqueada porque a declaração SELECT tenta ler as linhas bloqueadas pela consulta UPDATE na transação 1.

A transação 2 prossegue sem nenhum bloqueio, pois a leitura nunca é bloqueada, a declaração SELECT é executada e, finalmente, a linha com id = 3 é atualizada, pois as alterações da transação 1 ainda não estão visíveis.

SELECT * FROM employee;

Essa etapa é executada após a confirmação da transação 1. A linha com id = 1 é atualizada pela transação 2 na etapa anterior e está visível aqui.

A linha com o id = 3 é atualizada pela Transação 2.

COMMIT

A transação 2 agora está desbloqueada.

Confirmação bem-sucedida.

COMMIT

SELECT * FROM employee;

As duas transações executam a atualização na linha com id = 1.

Linhas diferentes são atualizadas pelas transações 1 e 2.

SERIALIZABLE DO BABELFISH VERSUS SERIALIZABLE ISOLATION LEVEL DO SQL SERVER

BLOQUEIOS DE INTERVALO NO SQL SERVER
Transação 1 Transação 2 Serializable do SQL Server Serializable do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

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

A transação 2 permanece bloqueada até que a Transação 1 seja confirmada.

A transação 2 prossegue sem nenhum bloqueio.

SELECT * FROM employee;

COMMIT

A transação 1 foi confirmada com êxito. A transação 2 agora está desbloqueada.

A transação 1 foi confirmada com êxito.

COMMIT

SELECT * FROM employee;

A linha recém-inserida está visível.

A linha recém-inserida está visível.
DIFFERENT FINAL RESULTS
Transação 1 Transação 2 Serializable do SQL Server Serializable do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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;

A transação 1 permanece bloqueada até que a Transação 2 seja confirmada.

A transação 1 prossegue sem nenhum bloqueio.

COMMIT

A transação 2 foi confirmada com êxito. A transação 1 agora está desbloqueada.

A transação 2 foi confirmada com êxito.

COMMIT

SELECT * FROM employee;

A linha recém-inserida é visível com valor de idade = 99.

A linha recém-inserida é visível com valor de idade = 40.
INSERT INTO TABLE WITH UNIQUE CONSTRAINT
Transação 1 Transação 2 Serializable do SQL Server Serializable do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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);

A transação 1 permanece bloqueada até que a Transação 2 seja confirmada.

A transação 1 permanece bloqueada até que a Transação 2 seja confirmada.

COMMIT

A transação 2 foi confirmada com êxito. A transação 1 agora está desbloqueada.

A transação 2 foi confirmada com êxito. A transação 1 cancelada com erro de valor de chave duplicado viola a restrição exclusiva.

COMMIT

A transação 1 foi confirmada com êxito.

As confirmações da transação 1 falham, pois não foi possível serializar o acesso devido às dependências de leitura/gravação entre as transações.

SELECT * FROM employee;

row (5, 'E', 50) is inserted.

Existem apenas quatro linhas.

No Babelfish, transações simultâneas executadas com o nível de isolamento serializável falharão com erro de anomalia de serialização se a execução dessas transações for inconsistente com todas as possíveis execuções seriais (uma por vez) dessas transações.

SERIALIZATION ANOMALY
Transação 1 Transação 2 Serializable do SQL Server Serializable do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SET TRANSACTION ISOLATION LEVEL SERILAIZABLE;

SELECT * FROM employee;

UPDATE employee SET age=5 WHERE age=10;

SELECT * FROM employee;

A transação 2 permanece bloqueada até que a Transação 1 seja confirmada.

A transação 2 prossegue sem nenhum bloqueio.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

A transação 1 foi confirmada com êxito.

A transação 1 é confirmada primeiro e pode ser confirmada com êxito.

COMMIT

A transação 2 foi confirmada com êxito.

A confirmação da transação 2 falha com erro de serialização, toda a transação foi revertida. Repita a transação 2.

SELECT * FROM employee;

As alterações das duas transações estão visíveis.

A transação 2 foi revertida. Somente as alterações da transação 1 são observadas.

No Babelfish, a anomalia de serialização só será possível se todas as transações simultâneas estiverem sendo executadas no nível de isolamento SERIALIZABLE. Por exemplo, vamos considerar o exemplo acima, mas definir a transação 2 como Nível de Isolamento REPEATABLE READ.

Transação 1 Transação 2 Níveis de isolamento no SQL Server Nível de isolamento do Babelfish

INICIAR TRANSAÇÃO

INICIAR TRANSAÇÃO

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;

A transação 2 permanece bloqueada até que a transação 1 seja confirmada.

A transação 2 prossegue sem nenhum bloqueio.

UPDATE employee SET age=35 WHERE age=30;

COMMIT

A transação 1 foi confirmada com êxito.

A transação 1 foi confirmada com êxito.

COMMIT

A transação 2 foi confirmada com êxito.

A transação 2 foi confirmada com êxito.

SELECT * FROM employee;

As alterações das duas transações estão visíveis.

As alterações das duas transações estão visíveis.