TRANSACTION ISOLATION LEVEL ...

Em termos de níveis de isolamento transacional SQL-92, o padrão InnoDB é REPEATABLE READ. A partir da versão 4.0.5, InnoDB oferece todos os níveis de isolamento transacional diferentes descritos pelo padrão SQL-92. Você pode definir o nível de isolamento padrão para todas as conexões na seção [mysqld] do my.cnf:

transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
 | REPEATABLE-READ | SERIALIZABLE}

Um usuário pode alterar o nível de isolamento de um única seção ou todas as próximas seções com a instrução SQL SET TRANSACTION. Sua sintaxe é a sseguinte:

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
 {READ UNCOMMITTED | READ COMMITTED
 | REPEATABLE READ | SERIALIZABLE}

Note que não há hífens no nome dos níveis na sintaxe SQL.

O comportamento padrão é definir o nível de isolamento para a próxima transação (não iniciada). Se você especificar a palavra chave GLOBAL na instrução acima, ela determinará o nível de isolamento globalmente para todas as novas conexões criadas a partir deste ponto (mas não conexão exitentes). Você precisa do privilégio SUPER para fazer isto. Usar a palavra chave SESSION difine a transação padrão para todas as transações realizadas futuramente na conexão atual. Qualquer cliente é livre para alterar o nível de isolamento da sessão (mesmo no meio de uma transação), ou o nível de isolamento para a próxima transação.

Você pode consultar o nível de isolamento da transação global ou da sessão com:

SELECT @@global.tx_isolation;
SELECT @@tx_isolation;

Nos travamentos de registro, InnoDB usa o chamado bloqueio de chave seguinte (next-key locking). Isto significa que além dos registros de índices, o InnoDB também pode bloquear a lacuna antes de um registro de índice para bloquear inserções por outros usuários imediatamente antes do registro de índice. Um bloqueio de chave seguinte significa um bloqueio que trava um registro de índice e a lacuna antes dele. O bloqueio de lacuna significa um bloqueio que só trava a lacuna antes do registro de índice.

Uma descrição detalhada de cada nível de isolamento em InnoDB:

• READ UNCOMMITTED Também é chamada dirty read: SELECTs sem bloqueio são realizados de forma a não procurar por uma possível versão mais nova de um registro; assim as leituras não são 'consistentes' sob este nível de isolamento; de outra forma este nível funciona como READ COMMITTED.
• READ COMMITTED Nível de isolamento parecido com o Oracle. Todas as instruções SELECT ... FOR UPDATE e SELECT ... LOCK IN SHARE MODE só travam o registro de índice, não a lacuna antes dele e assim permite livre inserção de novos registros próximo ao registro travado. Mas ainda no tipo de faixa UPDATE e DELETE, o InnoDB deve definir lock da chave seguinte ou da lacuna e bloquear inserções feitas por outros usuários nas lacunas cobertas pela faixa. Istó é necessário já que deve se bloquear linhas fantasmas para a replicação e recuperação no MariaDB funcionar. Leituras consistentes (Consistent reads) comportam como no Oracle: cada leitura consistente, mesmo dentro da mesma transação, configura e lê a sua própria cópia recente.
• REPEATABLE READ Este é o nível de isolamento padrão do InnoDB. SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, e DELETE que utilizam um índice único com uma condição de busca única, travam apenas o registro de índice encontrado, e não a lacuna antes dele. De outra forma estas operações empregam travamento de registro seguinte, bloqueando a faixa de índice varrida com trava de chave seguinte ou de lacuna e bloqueando novas inserções feitas por outros usuários. Em leituras consistentes (consistent reads) existe uma diferença importante do nível de isolmento anterior: neste nível todas as leituras consistentes dentro da mesma transação lêem o mesma cópia estabelacido pela primeira leitura. Esta conversão significa que se você executa diversas SELECTs dentro da mesma transação, elas também são consistentes entre elas.
• SERIALIZABLE Este nível é como o anterior, mas todos os SELECTs são convertidos implicitamente para SELECT ... LOCK IN SHARE MODE.

Retornar