Isolation Levels
O SQL Server disponibiliza quatro níveis distintos de isolamento (“isolation levels”) entre transações:
* READ UNCOMMITED
* READ COMMITED
* REPEATABLE READ
* SERIALIZABLE
O nível de isolamento é configurado para cada transação e define o seu grau de isolamento em relação às modificações efectuadas por outras transações sobre os recursos do servidor. Por outras palavras, o nível de isolamento define que efeitos secundários de concorrência são permitidos (aceitáveis) para essa transação.
Cada nível de isolamento controla de forma distinta:
* Que locks são adquiridos pela transação em operações de leitura.
* A duração dos locks de leitura.
* Se uma operação de leitura sobre registos modificados por outra transação:
o É ou não bloqueada até que o lock exclusivo seja libertado.
o Obtém a versão gravada desses registos no momento em que foi iniciada.
o Obtém a versão (ainda) não gravada desses registos.
É importante notar que o nível de isolamento não afecta o tipo de locks obtidos em operações de modificação de dados. Sempre que for realizada uma operação desse tipo (INSERT/UPDATE/DELETE) a transação obterá um lock exclusivo sobre os recursos modificados e manterá esse lock até que termine (COMMIT ou ROLLBACK).
Isolation Levels vs Performance
Um nível de isolamento inferior aumenta a concorrência do sistema, permitindo que mais utilizadores acedam aos mesmos dados à custa da possibilidade de ocorrerem mais (e mais graves) efeitos de concorrência (exemplo: dirty reads). Por outro lado, um nível de isolamento superior reduz a possibilidade de ocorrerem conflitos, diminuindo a concorrência entre utilizadores e, consequentemente, o desempenho geral da base de dados.
A escolha do nível adequado de isolamento depende, portanto, do tipo de operações realizadas pelas várias transações, da performance pretendida no acesso aos dados para leitura e da segurança pretendida em termos de integridade dos dados.
READ UNCOMMITED
Neste modo, as operações de leitura não respeitam a existência ou não de locks exclusivos sobre os recursos acedidos. Isto significa que podem ser lidos dados alterados por transações ainda não terminadas. Trata-se do nível de isolamento menos restritivo e, por isso, mais adequado para sistemas analíticos que requeiram um rápido acesso aos dados, com um nível de alterações dos dados muito reduzido (ou nulo).
Exemplo:
Problemas de concorrência:
* Dirty Read: Sim
* Nonrepeatable Read: Sim
* Phantom Read: Sim
READ COMMITED
Este é o nível de isolamento por defeito do SQL Server.
Nenhuma transação conseguirá obter um shared lock (de leitura) sobre recursos que tenham sido alterados (exclusive lock) por transações ainda não terminadas.
Os locks exclusivos têm a mesma duração da transação. Os locks de leitura duram apenas o tempo necessário para completar a operação (não até ao fim da transação).
Exemplo:
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Sim
* Phantom Read: Sim
REPEATABLE READ
Este modo difere do anterior porque os locks de leitura são mantidos até ao final da transação. Isto significa que nenhuma transação pode alterar um recurso que tenha sido lido por uma transação não terminada. Por isso impede “nonrepeatable reads”. No entanto, os “phantom reads” não são impedidos porque a nova transação pode ainda inserir registos que afectem a leitura da primeira transação.
Exemplo:
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Não
* Phantom Read: Sim
SERIALIZABLE
Este é o nível mais restritivo e corresponde, como o nome indica, a serializar as transações que acedem aos mesmos recursos. Como nenhuma transação pode efectuar qualquer tipo de operação de modificação sobre dados que estejam a ser consultados por transações activas, elimina os “phantom reads”. Note-se, no entanto que, por isso mesmo, tem um efeito muito negativo no nível de concorrência do servidor.
Exemplo:
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Não
* Phantom Read: Não
SET TRANSATION ISOLATION LEVEL
Para alterar o nível de isolamento de uma transação deve ser executado o comando SET TRANSACTION ISOLATION LEVEL. As variações são as seguintes:
* SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
* SET TRANSACTION ISOLATION LEVEL READ COMMITTED
* SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
* SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Conclusões
Desta discussão a principal conclusão a retirar é que o nível de isolamento adequado a cada sistema (ou mesmo a cada transação) depende essencialmente do tipo de operações realizadas.
Quando o factor mais importante for a velocidade de leitura de dados (exemplos: sistemas analíticos e serviços de encomendas online), havendo poucas alterações (muito esporádicas) dos dados e, desde que a exactidão dos resultados não seja um factor crítico, é recomendável um nível de isolamento baixo (READ UNCOMMITED).
Em sistemas mais transaccionais (exemplo: ERP) será mais adequado um nível superior, reduzindo a ocorrência de conflitos e incorrecções nos dados, à custa do desempenho das operações de leitura.
O nível de isolamento por default – READ COMMITED – será bastante equilibrado na maioria dos casos. Mas é muito importante que as transações sejam bem desenhadas, reduzindo a sua duração ao mínimo possível, reduzindo as operações de alteração efectuadas e garantindo que quaisquer leituras necessárias para efectuar as posteriores actualizações estão os mais próximas possível.
O SQL Server não oferece, portanto, “almoços grátis” também a este nível.
O SQL Server disponibiliza quatro níveis distintos de isolamento (“isolation levels”) entre transações:
* READ UNCOMMITED
* READ COMMITED
* REPEATABLE READ
* SERIALIZABLE
O nível de isolamento é configurado para cada transação e define o seu grau de isolamento em relação às modificações efectuadas por outras transações sobre os recursos do servidor. Por outras palavras, o nível de isolamento define que efeitos secundários de concorrência são permitidos (aceitáveis) para essa transação.
Cada nível de isolamento controla de forma distinta:
* Que locks são adquiridos pela transação em operações de leitura.
* A duração dos locks de leitura.
* Se uma operação de leitura sobre registos modificados por outra transação:
o É ou não bloqueada até que o lock exclusivo seja libertado.
o Obtém a versão gravada desses registos no momento em que foi iniciada.
o Obtém a versão (ainda) não gravada desses registos.
É importante notar que o nível de isolamento não afecta o tipo de locks obtidos em operações de modificação de dados. Sempre que for realizada uma operação desse tipo (INSERT/UPDATE/DELETE) a transação obterá um lock exclusivo sobre os recursos modificados e manterá esse lock até que termine (COMMIT ou ROLLBACK).
Isolation Levels vs Performance
Um nível de isolamento inferior aumenta a concorrência do sistema, permitindo que mais utilizadores acedam aos mesmos dados à custa da possibilidade de ocorrerem mais (e mais graves) efeitos de concorrência (exemplo: dirty reads). Por outro lado, um nível de isolamento superior reduz a possibilidade de ocorrerem conflitos, diminuindo a concorrência entre utilizadores e, consequentemente, o desempenho geral da base de dados.
A escolha do nível adequado de isolamento depende, portanto, do tipo de operações realizadas pelas várias transações, da performance pretendida no acesso aos dados para leitura e da segurança pretendida em termos de integridade dos dados.
READ UNCOMMITED
Neste modo, as operações de leitura não respeitam a existência ou não de locks exclusivos sobre os recursos acedidos. Isto significa que podem ser lidos dados alterados por transações ainda não terminadas. Trata-se do nível de isolamento menos restritivo e, por isso, mais adequado para sistemas analíticos que requeiram um rápido acesso aos dados, com um nível de alterações dos dados muito reduzido (ou nulo).
Exemplo:
| transação 1 | transação 2 | Valores |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 2 |
| BEGIN TRAN | (Idle) | |
| UPDATE TA SET B = 3 | (Idle) | TA(B) = 3 |
| (...) | SELECT B FROM TA | X = 3 |
| ROLLBACK | (...) | TA(B) = 2 |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 2 |
| BEGIN TRAN | (Idle) | |
| UPDATE TA SET B = 3 | (Idle) | TA(B) = 3 |
| (...) | SELECT B FROM TA | X = 3 |
| ROLLBACK | (...) | TA(B) = 2 |
Problemas de concorrência:
* Dirty Read: Sim
* Nonrepeatable Read: Sim
* Phantom Read: Sim
READ COMMITED
Este é o nível de isolamento por defeito do SQL Server.
Nenhuma transação conseguirá obter um shared lock (de leitura) sobre recursos que tenham sido alterados (exclusive lock) por transações ainda não terminadas.
Os locks exclusivos têm a mesma duração da transação. Os locks de leitura duram apenas o tempo necessário para completar a operação (não até ao fim da transação).
Exemplo:
| transação 1 | transação 2 | Valores |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 2 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT B FROM TA | X = 2 |
| BEGIN TRAN | (...) | |
| UPDATE TA SET B = 3 | (...) | TA(B) = 3 |
| (...) | SELECT B FROM TA | |
| (...) | (transação bloqueada) | |
| COMMIT | (...) | TA(B) = 3 |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 2 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT B FROM TA | X = 2 |
| BEGIN TRAN | (...) | |
| UPDATE TA SET B = 3 | (...) | TA(B) = 3 |
| (...) | SELECT B FROM TA | |
| (...) | (transação bloqueada) | |
| COMMIT | (...) | TA(B) = 3 |
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Sim
* Phantom Read: Sim
REPEATABLE READ
Este modo difere do anterior porque os locks de leitura são mantidos até ao final da transação. Isto significa que nenhuma transação pode alterar um recurso que tenha sido lido por uma transação não terminada. Por isso impede “nonrepeatable reads”. No entanto, os “phantom reads” não são impedidos porque a nova transação pode ainda inserir registos que afectem a leitura da primeira transação.
Exemplo:
| transação 1 | transação 2 | Valores |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 3 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT B FROM TA | X = 3 |
| BEGIN TRAN | (...) | |
| UPDATE TA SET B = 4 | (...) | |
| (transação bloqueada) | COMMIT | |
| COMMIT | (...) | TA(B) = 4 |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | TA(B) = 3 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT B FROM TA | X = 3 |
| BEGIN TRAN | (...) | |
| UPDATE TA SET B = 4 | (...) | |
| (transação bloqueada) | COMMIT | |
| COMMIT | (...) | TA(B) = 4 |
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Não
* Phantom Read: Sim
SERIALIZABLE
Este é o nível mais restritivo e corresponde, como o nome indica, a serializar as transações que acedem aos mesmos recursos. Como nenhuma transação pode efectuar qualquer tipo de operação de modificação sobre dados que estejam a ser consultados por transações activas, elimina os “phantom reads”. Note-se, no entanto que, por isso mesmo, tem um efeito muito negativo no nível de concorrência do servidor.
Exemplo:
| transação 1 | transação 2 | Valores |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | N = 1 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT COUNT(*) FROM TA | N = 1 |
| BEGIN TRAN | (...) | |
| INSERT INTO TA ... | (...) | |
| (transação bloqueada) | SELECT COUNT(*) FROM TA | N = 1 |
| (...) | COMMIT | |
| COMMIT | (Idle) | N = 2 |
|---------------------------|---------------------------|-----------|
| (Idle) | (Idle) | N = 1 |
| (Idle) | BEGIN TRAN | |
| (Idle) | SELECT COUNT(*) FROM TA | N = 1 |
| BEGIN TRAN | (...) | |
| INSERT INTO TA ... | (...) | |
| (transação bloqueada) | SELECT COUNT(*) FROM TA | N = 1 |
| (...) | COMMIT | |
| COMMIT | (Idle) | N = 2 |
Problemas de concorrência:
* Dirty Read: Não
* Nonrepeatable Read: Não
* Phantom Read: Não
SET TRANSATION ISOLATION LEVEL
Para alterar o nível de isolamento de uma transação deve ser executado o comando SET TRANSACTION ISOLATION LEVEL. As variações são as seguintes:
* SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
* SET TRANSACTION ISOLATION LEVEL READ COMMITTED
* SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
* SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Conclusões
Desta discussão a principal conclusão a retirar é que o nível de isolamento adequado a cada sistema (ou mesmo a cada transação) depende essencialmente do tipo de operações realizadas.
Quando o factor mais importante for a velocidade de leitura de dados (exemplos: sistemas analíticos e serviços de encomendas online), havendo poucas alterações (muito esporádicas) dos dados e, desde que a exactidão dos resultados não seja um factor crítico, é recomendável um nível de isolamento baixo (READ UNCOMMITED).
Em sistemas mais transaccionais (exemplo: ERP) será mais adequado um nível superior, reduzindo a ocorrência de conflitos e incorrecções nos dados, à custa do desempenho das operações de leitura.
O nível de isolamento por default – READ COMMITED – será bastante equilibrado na maioria dos casos. Mas é muito importante que as transações sejam bem desenhadas, reduzindo a sua duração ao mínimo possível, reduzindo as operações de alteração efectuadas e garantindo que quaisquer leituras necessárias para efectuar as posteriores actualizações estão os mais próximas possível.
O SQL Server não oferece, portanto, “almoços grátis” também a este nível.
No comments:
Post a Comment