artigo SQL Magazine 04 - Transações em SQL Server

Em ambientes multi-usuários existem operações que precisam ser serializadas, ou seja, colocadas em fila para que sejam executadas “uma a uma”, não permitindo tarefas em paralelo. Essas operações são amparadas por um conceito muito forte em bancos de dados relacionais denominado Transação,cujo objetivo é agrupar uma seqüência de comandos que precisam ser tratados como um bloco único e indivisível, para que se mantenham a integridade e a consistência dos dados. Veja o código abaixo:

Neste exemplo, desejamos que as colunas Cidade e UF sejam alteradas ao mesmo tempo. Caso algum erro aconteça, nenhum dos campos será modificado. A transação age da mesma forma, com a diferença de conter várias instruções SQL. Se um dos comandos não for executado, todo o bloco envolvido na transação será cancelado.

Um exemplo é uma operação bancária de transferência de fundos, onde movimenta-se dinheiro de uma conta corrente para uma conta poupança. As duas operações devem ser garantidas como uma só. Se o depósito na poupança falhar, o saque na conta corrente deve ser desconsiderado, garantindo a integridade do processo.

A transação inclui o conceito de bloqueio (lock). No momento em que ela é executada, os dados alterados permanecem visíveis somente para o usuário que a originou, ficando bloqueados para as outras conexões. Sua liberação, ou seja, a possibilidade desses dados tornarem-se novamente visíveis para outros usuários, ocorre com o término da transação. É possível determinar mecanismos de bloqueios mais avançados, como veremos mais adiante.

NOTA: O comportamento padrão de bloqueios estabelece que comandos de alteração de dados (INSERT/UPDATE/DELETE) obtêm bloqueios exclusivos, sendo liberados com o término da transação. Já comandos SELECT possuem bloqueios que terminam com a execução do próprio comando, não aguardando o desfecho da transação.

No SQL Server, existem três classes de transação:

• Transação Auto-Comitada: é padrão no banco de dados da Microsoft. Define que os comandos SQL de manipulação representam transações individuais que são automaticamente efetivadas após sua execução. Veja um exemplo na listagem 1. Na maior parte dos casos esse modelo é falho por não considerar os dois passos como um bloco único.

Listagem 1: Transação auto-comitada

• Transação Explícita: o início e o fim da transação devem ser claramente delimitados. O início é determinado através do comando begin tran. A finalização é efetuada com o comando commit, para confirmar as alterações, ou com o comando rollback, para cancelar todo o bloco. A listagem 2 contém uma transação explícita envolvendo o débito na conta corrente e o crédito na conta poupança.

Listagem 2.Transação Explícita

• Transação Implícita: Nesse modelo uma transação é iniciada automaticamente, sem o uso do comando begin tran. No entanto, o fim do bloco precisa de um commit ou rollback. A transação implícita está disponível apenas para um conjunto restrito de comandos, listados na tabela 1. Observe que as instruções SQL são agrupadas na mesma transação até que um commit ou rollback seja encontrado. Por não se tratar do modelo padrão no SQL Server, o modo implícito deve ser ativado através do comando Set Implicit_Transactions ON. A listagem 3 exemplifica uma transação implícita.

Tabela 1.Comandos que abrem transações quando implicit_transactions está ligado

Listagem 3. Transação implícita

Aninhamento de Transações

Transações podem ser incluídas uma dentro da outra. Por exemplo, uma transação pode conter uma chamada a um procedimento armazenado que abre uma nova transação. Nesse caso, teremos dois blocos de comandos que precisarão ser comitados para concluir corretamente o processo.

Para verificar se existem transações pendentes em uma sessão, utilize a variável de ambiente @@Trancount. Sempre que uma transação é aberta, essa variável, cujo escopo é local na sessão, é incrementada em uma unidade. Quando a transação é fechada seu valor é decrementado em um. Um valor igual a zero significa ausência de transações abertas, já um valor igual a dois indica duas transações pendentes na mesma sessão.

À medida que transações são aninhadas, a complexidade do código aumenta. Como as transações mais internas dependem das mais externas para serem efetivadas, em determinadas situações pode-se perder o controle de quantos commits devem ser aplicados para efetivar a transação principal. Portanto, é recomendável manter até dois níveis de aninhamento (@@Trancount=2).

Vejamos algumas regras gerais no uso de transações aninhadas:

1 ) Todo comando begin tran deve ser finalizado com um commit ou rollback;

2 ) O commit/rollback mais externo prevalece sobre os internos. Um rollback na camada mais externa gera um efeito de cascata, invalidando os commits anteriormente efetuados; já um commit mais xterno confirma também os commits internos;

3 ) Um rollback em uma transação aninhada zera @@Trancount, ou seja, age na transação atual e nas de nível superior (listagem 4).

Listagem 4.rollback

4 ) Um commit em uma transação aninhada depende do commit mais externo para que seja realmente efetivado. De fato, o commit mais interno apenas decrementa @@Trancount (listagem 5).

Listagem 5. Commit

5) Para efetuar um rollback parcial é necessário o uso de savepoints, que são marcas definidas nas transações. Um rollback direcionado para um savepoint não altera o valor de @@Trancount; após sua execução devemos fechar a transação com um commit ou rollback. Um savepoint é criado com o comando Save Tran > e o comando rollback parcial deve fornecer o nome do savepoint para retorno. Veja um exemplo na listagem 6.

NOTA: No SQL Server não existe commit parcial.

Listagem 6. Savepoint

6) Indicar o nome de uma transação como argumento do comando commit não tem efeito prático no SQL Server. O commit age sempre sobre a transação corrente. Se esse commit for o mais externo, efetivará todos os commits aninhados.

7) Nomes de transação explicitados no rollback não apresentam efeito, a menos que se utilize savepoints.

8) Trabalhar com transações aninhadas não requer configuração específica no SQL Server 2000, que reconhece naturalmente esse tipo de operação.

Níveis de isolamento

O acesso simultâneo de dois ou mais usuários a um mesmo registro deve ser impedido, visto que essa situação pode quebrar a consistência dos dados. O mecanismo utilizado no SQL Server para evitar esse cenário é conhecido como bloqueio (lock).

Bloqueios são acionados automaticamente durante a transação. Eles diferem quanto à durabilidade (se persistem até o término da transação), tipo (exclusivos ou compartilhados) e abrangência (por linha, página, tabela ou grupo de linhas). Uma das variáveis que define o comportamento de um bloqueio é o nível de isolamento da transação, que são quatro:

• Read UnCommited: Permite a leitura de dados não comitados, desrespeitando os bloqueios exclusivos mantidos nas transações. Sob determinadas circunstâncias, esse nível de isolamento traz um ganho considerável de performance. A adoção desse método deve ser criteriosamente avaliada, já que infringe uma regra básica: fornecer leituras consistentes. Esse modo também é conhecido por dirty read.
• Read Commited: é o nível de isolamento padrão adotado no SQL Server. Significa que somente dados já comitados podem ser lidos. Se uma aplicação tentar recuperar registros que estão em atualização por outra sessão, ela terá que aguardar o término da transação concorrente.
  Nesse nível de isolamento, os registros manipulados por comandos INSERT, UPDATE e DELETE permanecem bloqueados para outras sessões até que a transação responsável seja concluída. Já o comando SELECT impõe um tipo de bloqueio conhecido por shared lock, que libera os dados logo após a leitura das respectivas páginas. Portanto, comandos SELECT bloqueiam recursos somente durante a sua própria execução, e não durante o tempo em que a transação estiver ativa. Para alterar esse padrão, é necessário estabelecer um nível superior de isolamento.
• Repeatable Read: Impede que registros em leitura sejam atualizados por outra transação. Imagine uma aplicação que calcula a média aritmética dos alunos de uma turma, somando todas as notas individuais e dividindo pelo número de alunos existentes. Se durante o processamento da média outra sessão alterar algumas notas individuais, o resultado final não vai corresponder à soma atual das notas. Repeatable Read estende o nível de isolamento padrão, garantindo o bloqueio das linhas lidas com SELECT até o desfecho da transação. No exemplo, esse isolamento impediria que as notas fossem alteradas durante o cálculo da média.
• Serializable: esse modelo, além de incorporar Repeatable Read, bloqueia a inserção de registros enquanto a transação estiver aberta. No exemplo anterior, além de não permitir a alteração das notas durante o cálculo da média, a inclusão de um novo estudante também seria bloqueada.

Algumas dicas úteis:

1 ) Os níveis de isolamento possuem uma escala ascendente de abrangência de bloqueios (figura 1).

Figura 1: Níveis de isolamento

Serializable

Repeatable Read com restrições

Repeatable Read de inserção no range lido

Read Commited com

Read Committed bloqueio de linhas lidas

Leitura somente

Read UnCommitted de dados comitados

permite diry-reads, Escala crescente

não fornece isolamento de isolamento

2 ) O nível de isolamento padrão (Read Committed) se enquadra melhor na maior parte dos casos. Vale a pena reavaliar o banco de dados antes de submeter as transações a níveis mais intensos de bloqueios, pois estruturas desnormalizadas estão sujeitas a bloqueios mais demorados. No exemplo anterior, o problema seria minimizado se a média geral fosse gravada em uma tabela distinta, com apenas um registro para toda a turma. Reduzindo o overhead gerado pela replicação da mesma informação para todos os alunos diminui-se consideravelmente o tempo da transação e, conseqüentemente, a possibilidade de acontecer algum cadastramento no momento do cálculo.

3 ) Os níveis de isolamento podem ser definidos no cliente ou no servidor. A sintaxe completa para definição de isolamento com ODBC ou OLEDB pode ser encontrada em http://msdn.microsoft.com/library/default.asp?url= /library/en-us/acdata/ac_8_md_06_3q0j.asp. No servidor, temos o comando set transaction level:

Esse comando não é necessário quando trabalhamos com o nível de isolamento default (Read Committed). O comando DBCC UserOptions mostra o nível de isolamento corrente.

No ADO, o nível de isolamento é definido na propriedade IsolationLevel do objeto de conexão. Os valores possíveis são adXactReadUncommitted, adXactReadCommitted, adXact RepeatebleRead e adXactSerializable.

BOX.Transações aninhadas com Stored Procedures

2 ) Observe a mensagem abaixo:

… Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = %ld, current count = %ld.

Esse é o erro 266, que ocorre no uso de transações aninhadas. Podem ser duas as possíveis causas:

• Você tentou fechar uma transação que foi aberta fora da stored procedure;
• Você abriu uma transação na stored procedure, mas está tentando fazer seu fechamento (com commit ou rollback) fora da rotina.

Nesses casos o valor de @@Trancount no início da procedure deve ser o mesmo em seu término. Se você iniciar uma procedure com o valor de @@Trancount igual a 1, terá que finalizá-la com o mesmo valor.

A listagem abaixo gera a exceção 266:

... pela alternativa listada a seguir

Alternativa : troque o rollback efetuado na procedure stp_Teste pelo comando RaisError. Cheque então o valor de retorno da sp logo após sua execução, realizando o rollback condicionalmente no mesmo nível da transação principal.

Vale a pena ressaltar uma observação: se no exemplo anterior - que originou o erro 266 - o rollback tivesse sido executado dentro de uma trigger a excessão NÃO teria acontecido, haja visto que comandos rollback em triggers finalizam o batch que contém o comando que disparou a trigger. Observe que no exemplo abaixo a linha ... print ‘Nao ira passar por aqui’ .. não é executada.

Conclusão

Na próxima edição continuaremos com o assunto, enfatizando o gerenciamento de bloqueios e aplicação de diretivas (conhecidas porhints) em comandos Transact SQL. Até a próxima!