Sistemas de Informação
Redes de Computadores
Análise e Desenvolvimento de Sistemas
Administração de Banco de Dados
1º Semestre – 2011
Pedro Antonio Galvão Junior
E-mail: [email protected] Fone: 9531-7555
Versão 1.11.04 – Abril/2011.
TRABALHANDO COM
TRANSAÇÕES NO SQL SERVER
INTRODUÇÃO
• 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 sequência
de comandos que precisam ser tratados como um bloco único e invisível, para
que se mantenham a integridade e a consistência dos dados.
INTRODUÇÃO
• O SQL Server fornece três maneiras de tratar transações, que
podemos definir dentro de uma conexão. Essas três maneiras são:
– Transações de autoconfirmação;
– Transações explícitas; e
– Transações implícitas.
TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
• O modo de confirmação automática é o modo padrão de gerenciamento de
transações do Mecanismo de banco de dados do SQL Server.
• Toda instrução Transact-SQL é confirmada ou revertida quando concluída. Se
uma instrução for concluída com sucesso, será confirmada; se encontrar
qualquer erro, será revertida.
• Uma conexão para uma instância do Mecanismo de Banco de Dados opera em
modo de confirmação automática sempre que esse modo padrão não for
substituído por transações explícitas ou implícitas.
TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
•
O modo de confirmação automática também é o modo padrão para ADO, OLE DB, ODBC e
DB-Library.
•
Uma conexão a uma instância do Mecanismo de Banco de Dados opera em modo de
confirmação automática até que uma instrução BEGIN TRANSACTION inicie uma
transação explícita, ou que uma transação implícita seja definida como ativada.
•
Quando a transação explícita é confirmada ou revertida, ou quando o modo de transação
implícito é desativado, a conexão retorna ao modo de confirmação automática.
TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
•
Quando ON, SET IMPLICIT_TRANSACTIONS define a conexão como modo de transação
implícita.
•
Quando OFF, ele retorna a conexão para o modo de transação de confirmação
automática.
•
No modo de confirmação automática, às vezes parece que uma instância do Mecanismo
de Banco de Dados reverteu um lote inteiro, em vez de apenas uma instrução SQL. Isto
acontece se o erro encontrado for um erro de compilação, não um erro em tempo de
execução.
TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
• Um erro de compilação impede o Mecanismo de Banco de Dados de criar um
plano de execução, assim nada no lote é executado.
• Embora pareça que todas as instruções antes daquela que gerou o erro tenham
sido revertidas, o erro impediu que tudo no lote fosse executado.
PRÁTICA 1 - TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
•
Para ilustrar e demonstrar como o SQL Server trabalha com Transações de
AutoConfirmação, vamos realizar a Prática 1. Em um banco de dados qualquer, crie a
seguinte tabela:
CREATE TABLE OBJETO
(
ID INT NOT NULL PRIMARY KEY,
COLUNA1 VARCHAR(20) NOT NULL,
COLUNA2 VARCHAR(20) NULL
);
PRÁTICA 1 - TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
•
Com a tabela criada, vamos às transações, realizandos os 3 inserts abaixo:
– INSERT INTO OBJETO VALUES (1, 'Primeira coluna', 'Primeira coluna');
– INSERT INTO OBJETO VALUES (2, null, 'Segunda coluna');
– INSERT INTO OBJETO VALUES (3, 'Terceira coluna', 'Terceira coluna');
•
Após a execução, recebemos a seguinte mensagem:
PRÁTICA 1 - TRANSAÇÕES DE
AUTOCONFIRMAÇÃO
•
A mensagem é apresentada porque a COLUNA1 da tabela OBJETO não aceita valor NULL.
•
Agora vamos executar um SELECT simples na tabela OBJETO. Poderemos verificar que os
inserts 1 e 3 foram executados.
•
Isso significa que quando o SQL Server usa transações de autoconfirmação, cada instrução
é uma transação por si só.
•
Quando uma instrução produz um erro (como no insert 2), automaticamente a instrução
é revertida, senão ela é confirmada.
TRANSAÇÕES EXPLÍCITAS
•
Uma transação explícita é aquela para a qual você define o início e término da transação
explicitamente. O modo de transação explícito permanece apenas no período de duração
da transação.
•
Quando a transação terminar, a conexão volta ao modo de transação em que estava
antes de a transação explícita ser iniciada, implícito ou modo de confirmação automática.
•
Os aplicativos DB-Library e scripts Transact-SQL usam as instruções BEGIN TRANSACTION,
COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION ou ROLLBACK
WORK Transact-SQL para definir transações explícitas. BEGIN TRANSACTION marca o
ponto inicial de uma transação explícita para uma conexão.
TRANSAÇÕES EXPLÍCITAS
•
COMMIT TRANSACTION ou COMMIT WORK são usadas para encerrar uma transação com
êxito se nenhum erro for encontrado.
•
ROLLBACK TRANSACTION ou ROLLBACK WORK são usados para apagar uma transação na
qual são encontrados erros.
•
Todos os dados modificados pela transação retornam ao estado em que estavam no início
da transação. Os recursos mantidos pela transação são liberados.
TRANSAÇÕES EXPLÍCITAS
•
Nesse tipo, o desenvolvedor define onde a transação é inicializada e onde ela é finalizada
ou revertida.
•
Para isso, utilizaremos as seguintes instruções:
– Begin Transaction: para iniciar uma transação;
– Commit Transaction: para confirmar uma transação;
– Rollback Transaction: para reverter uma transação.
• A palavra TRANSACTION pode ser abreviada para TRAN.
PRÁTICA 2 - TRANSAÇÕES
EXPLÍCITAS
•
Vamos utilizar o código da seguinte forma:
BEGIN TRAN
INSERT INTO OBJETO VALUES (1, 'Primeira coluna', 'Primeira coluna');
INSERT INTO OBJETO VALUES (2, null, 'Segunda coluna');
INSERT INTO OBJETO VALUES (3, 'Terceira coluna', 'Terceira coluna');
COMMIT TRAN
PRÁTICA 2 - TRANSAÇÕES
EXPLÍCITAS
• Vamos executar o código, e o que acontece?
• Você irá receber a mesma mensagem de erro do exemplo anterior e, ao executar
uma consulta na tabela OBJETO, irá obter o mesmo resultado.
• Então, o que mudou?
• Lembre-se: é responsabilidade do desenvolver determinar se a transação deve
ser revertida. No exemplo acima, não fizemos nenhum tratamento para que a
instrução fosse revertida no caso de algum erro.
PRÁTICA 2 - TRANSAÇÕES
EXPLÍCITAS
•
Melhorando nossa rotina:
– Exclua todos os registro da tabela OBJETO utilizando o comando Truncate Table
OBJETO. Vamos alterar nosso exemplo conforme o arquivo em anexo:
– Exemplo - Trabalhando com Transações Explícitas.sql
•
Nesse exemplo estamos utilizando a rotina de tratamento de erro TRY e CATCH. Execute a
rotina acima e veja que nenhum registro será incluído na tabela OBJETO, ou seja, a
reversão da instrução foi executada com sucesso.
•
Para melhorar nosso tratamento de erro, adicionei ao CATCH uma consulta que traga
mais informações sobre o erro e uma mensagem de erro.
TRANSAÇÕES IMPLÍCITAS
• Quando uma conexão operar em modo de transação implícita, a instância do
Mecanismo de banco de dados do SQL Server iniciará automaticamente uma
nova transação depois que a transação atual for confirmada ou revertida.
• Você não faz nada para determinar o início de uma transação; apenas confirma
ou reverte cada uma das transações.
• O modo de transação implícita gera uma cadeia contínua de transações.
TRANSAÇÕES IMPLÍCITAS
• Após a configuração do modo de transação implícita em uma conexão, a
instância do Mecanismo de Banco de Dados iniciará automaticamente a
transação ao executar pela primeira vez cada uma destas instruções:
ALTER TABLE
INSERT
CREATE
OPEN
DELETE
REVOKE
DROP
SELECT
FETCH
TRUNCATE TABLE
GRANT
UPDATE
TRANSAÇÕES IMPLÍCITAS
•
A transação permanecerá em vigor até que você emita uma instrução COMMIT ou
ROLLBACK.
•
Após a confirmação ou reversão da primeira transação, a instância do Mecanismo de
Banco de Dados iniciará automaticamente uma nova transação da próxima vez que
qualquer instrução for executada pela conexão.
•
A instância continuará a gerar a cadeia de transações implícitas até que modo de
transação implícita seja desativado.
•
O modo de transação implícita é definido tanto pelo uso da instrução SET do Transact-SQL
como por meio de funções e métodos API de banco de dados.
PRÁTICA 3 - TRANSAÇÕES
IMPLÍCITAS
•
Observação: O modo de transação implícita no SQL Server inicia uma transação se
nenhuma já tiver sido inicializada, mas o COMMIT ou o ROLLBACK deve ser definido pelo
desenvolvedor.
•
Para usar esse tipo de transação, precisamos ativá-la no SQL Server usando o código
abaixo:
– SET IMPLICIT_TRANSACTIONS ON;
•
Agora execute a instrução abaixo para verificar quantas transações em aberto existem:
– SELECT @@TRANCOUNT;
PRÁTICA 3 - TRANSAÇÕES
IMPLÍCITAS
•
Crie uma tabela simples e execute o @@TRANCOUNT novamente:
– CREATE TABLE TESTE (ID INT PRIMARY KEY);
– SELECT @@TRANCOUNT;
•
O resultado agora é 1, uma transação foi inicializada. E se executarmos um insert simples
nessa tabela?
– INSERT INTO TESTE VALUES (10);
– SELECT * FROM TESTE; SELECT @@TRANCOUNT;
PRÁTICA 3 - TRANSAÇÕES
IMPLÍCITAS
•
A instrução insert foi executada, porém continuamos a ter uma única transação. Para
finalizar, vamos executar um ROLLBACK:
– ROLLBACK TRAN;
– SELECT @@TRANCOUNT;
•
Pronto, nossa transação foi revertida. Se você quiser confirmar, execute um SELECT na
tabela TESTE. O que aconteceu?
•
Uma mensagem de erro informando que a tabela não existe é exibida, ou seja, nosso
ROLLBACK reverteu tudo, desde o insert à criação da tabela.
PRÁTICA 3 - TRANSAÇÕES
IMPLÍCITAS
•
Para finalizar, vamos desativar as transações implícitas:
– SET IMPLICIT_TRANSACTIONS OFF;