Isolamento de Transações no BD

Introdução

Transações em Bancos de Dados permitem encapsular um conjunto de instruções que devem ocorrer para garantir as propriedades ACID (Atomicidade, Consistência, Isolamento e Durabilidade).

A SQL-92 expõe 3 fenômenos que podem ocorrer durante a execução de blocos de consultas concorrentes (em paralelo). Para cada fenômeno, existe um tipo de isolamento de transação que é capaz de solucioná-lo. Mas observe que "solucionar", neste caso, não significa que todas as consultas das transações irão ocorrer. Significa que o bloco de instruções será executado por completo, ou será abortado automaticamente, através de um rollback implícito.

Os 3 fenômenos

• P1 ("Dirty read" ou "Leitura Suja"): A transação T1 modifica uma linha. Então a transação T2 lê esta linha antes de T1 efetuar o COMMIT. Então se T1 executa um ROLLBACK, T2 terá lido uma linha que não foi persistida, e podemos dizer que ela nunca existiu no banco.
• P2 ("Non-repeatable read" ou "Leitura não repetida"): A transação T1 lê uma linha. Então a transação T2 modifica ou apaga esta linha e efetua um COMMIT. Se T1 solicitar novamente a leitura da mesma linha, ele receberá um resultado diferente.
• P3 ("Phantom" ou "Fantasma"): A transação T1 lê um conjunto de N linhas que satisfazem uma condição de busca. Então a transação T2 executa instruções SQL que geram uma ou mais linhas que satisfazem a mesma condição de busca usada por T1. Se T1 repetir a mesma consulta inicial, irá obter uma coleção diferente de linhas.

As soluções

A SQL-92 define quatro tipos de isolamento transacional e os respectivos fenômenos solucionados:

Tipo de Isolamento	Fenômenos Solucionados
	P1	P2	P3
READ UNCOMMITTED	N	N	N
READ COMMITTED	S	N	N
REPEATABLE READ	S	S	N
SERIALIZABLE	S	S	S

Casos Práticos

Apesar de nem todos programadores conhecerem os tipos de isolamento de transações, eles estão presentes em vários casos bastante comuns. Vejamos alguns deles.

Listas com paginação normalmente realizam duas consultas para montar uma das páginas com um conjunto limitado de itens. Uma delas é para contar quantos registros existem no Banco de Dados (para saber quantas páginas serão necessárias) e a outra é para efetivamente consultar os registros a serem apresentados na página (uma fatia da lista). Nem todos sabem que, nestas duas consultas, existe a possibilidade de ocorrência do fenômeno P3 (Phantom), ou seja, o script executa a primeira consulta, obtendo a quantidade de registros sob determinada condição e, antes de executar a segunda consulta, outro script insere um novo registro ou apaga um dos registros contemplados pela condição da lista. Neste caso, a segunda consulta pode obter um resultado inválido e a paginação pode, inclusive, ficar com número incorreto de páginas. Usando o isolamento Serializable, o segundo script seria impedido de realizar mudanças na tabela que foi consultada pela primeira transação e só seria permitido quando a transação do primeiro script terminasse. Caso a transação demorasse muito, alguns SGBD podem optar por abortar a consulta. Cabe ao programador verificar se cada consulta foi executada ou abortada, antes de continuar o procedimento do script.

Outro caso comum é para geração de protocolos sequenciais. O script precisa primeiro consultar qual é o último protocolo, determinar o próximo, e realizar a inserção do novo registro com o protocolo gerado. Neste caso, podem ocorrer os três fenômenos. Usando Serializable, a tabela que foi lida para obter o último protocolo não poderá ser modificada até que se acabe a transação. Caso dois scripts leiam o mesmo último protocolo, o SGBD não permitirá que as duas transações se concretizem, apenas uma.

Exemplos

Em MySQL e PostgreSQL, para definir um tipo de isolamento transacional, use "SET TRANSACTION ISOLATION LEVEL ...", conforme o exemplo:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Os nomes dos níveis de isolamento são iguais aos definidos na SQL-92.

A diferença entre o MySQL e o PostgreSQL é o ponto onde ele deve ser chamado. No MySQL, o tipo de isolamento é definido antes de começar a transação (antes do "START TRANSACTION". Já no PostgreSQL, o tipo de isolamento é definido logo após a abertura da transação.

Atenção: nem todos os bancos de dados dão suporte a transações e, consequentemente, aos níveis de isolamentos transacionais. O MySQL, por exemplo, só oferece este recurso em tabelas do tipo InnoDB. Para criar uma tabela InnoDB no MySQL, use a sintaxe:

CREATE TABLE nome_tabela (
...
)
ENGINE=InnoDB;

Observações

Embora o nível de isolamento Serializable resolva todos os fenômenos, ele tem um problema em relação à performance. Ele causa o travamento temporário de algumas tabelas, de acordo com o tipo de operações feitas sobre elas. Isso significa que operações concorrentes podem precisar aguardar o fim da transação para efetuarem suas operações. Ou seja, procure entender bem o possível fenômeno dentro de um bloco transacional para evitar perdas desnecessárias de performance.