Olá pessoal,
Como vocês estão ?
Neste post eu vou falar sobre uma configuração a nível de sessão que muitas pessoas não conhecem e, em muitos casos, podem ser de grande ajuda para evitar perda acidental de dados, que é o Autocommit.
Em alguns outros SGBD’s (Oracle, Postgree, etc) isso não é ativado por padrão, o que acaba gerando uma estranheza pelos DEV’s e DBA’s que vieram dessas tecnologias. Sempre que falamos de alterações de dados em Produção, devemos ter em mente o uso de transações. Isso é especialmente útil e seguro, pois, em caso de algum erro no processo de atualização de dados, podemos desfazer todas as alterações realizadas com um simples comando ROLLBACK. Caso você não esteja utilizando uma transação e fez vários INSERT’s, UPDATE’s e DELETE’s, você terá um grande trabalho para desfazer essas alterações, sem contar o tempo em que a base ficará com as informações incorretas.
De acordo com a documentação oficial do SQL Server, o modo padrão de tratamento de transações do SQL Server é o Autocommit, ou seja, quando você executa um UPDATE, INSERT ou DELETE sem iniciar uma transação explícita, essas operações são commitadas automaticamente.
Quando você inicia uma transação, o SQL Server ativa o modo de transação implícita, fazendo com que todas as operações de DML sejam encapsuladas nessa transação. Uma vez que você executar o COMMIT ou ROLLBACK, o motor do SQL desativa o modo de transação implícita e retorna para o modo Autocommit.
Gostaria também de ressaltar que meu objetivo nesse post não é que você desative o Autocommit apenas porque leu aqui. Você precisa entender o que é o Autocommit e entender se realmente faz sentido manter ativado ou não. Existem muitos casos em que o DBA pode preferir continuar com esse comportamento (que é padrão) do SQL Server, enquanto em outros casos, principalmente se você vem do Oracle ou Postgree, em que você pode querer desativar o Autocommit. Isso depende do seu perfil e da sua forma de trabalhar.
Transações e Erros de Compilação e Execução
Muitas vezes quando estamos executando vários comandos em lote, nos deparamos com uma mensagem de erro no meio dos scripts e ficamos sem saber o que foi executado e o que não foi, porque em alguns casos nada é executado e em outros, tudo que está antes da linha com erro foi executado. Vou demonstrar porque isso ocorre.
Erros de Compilação
Toda vez que você vai executar um comando no SQL Server, o motor do SQL executa uma pré-validação dos comandos que você executa, realizando algumas validações no comando para evitar que ele gere um processamento desnecessário e no final dos comandos ele gere um erro que poderia ter sido evitado. Uma dessas validações, é a de sintaxe dos comandos.
Quando há um erro de sintaxe em um dos comandos, a execução é interrompida antes de executar qualquer instrução SQL. Ou seja, quando há erros de sintaxe, nada é executado.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [Teste]; GO IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch CREATE TABLE TestBatch ( ColA INT PRIMARY KEY, ColB CHAR(3) ); GO INSERT INTO TestBatch VALUES (1, 'aaa'); INSERT INTO TestBatch VALUES (2, 'bbb'); INSERT INTO TestBatch VALUSE (3, 'ccc'); -- Erro de sintaxe. GO SELECT * FROM TestBatch; -- Não retorna linhas. GO |
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ‘VALUSE’.
Erros de Execução
Diferente dos erros de compilação, que são pré-validados antes de executar os comandos no banco, os erros de execução não são validados antes de iniciar o processamento, uma vez que eles demandariam um processamento muito grande para conseguir validar essas informações. Imagine o SQL Server ter que validar se existe chave duplicada em um batch de 10.000 INSERT’s antes de processar o comando. Seria quase o mesmo processamento da própria operação de INSERT.
Neste caso, quando há um erro de execução, os comandos são executados e commitados até a linha que gera o erro.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [Teste]; GO IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch CREATE TABLE TestBatch ( ColA INT PRIMARY KEY, ColB CHAR(3) ); GO INSERT INTO TestBatch VALUES (1, 'aaa'); INSERT INTO TestBatch VALUES (2, 'bbb'); INSERT INTO TestBatch VALUES (1, 'ccc'); -- Erro de chave duplicada. GO SELECT * FROM TestBatch; -- Retorna as linhas 1 e 2. GO |
Msg 2627, Level 14, State 1, Line 13
Violation of PRIMARY KEY constraint ‘PK__TestBatc__A259EE44E0B6221D’. Cannot insert duplicate key in object ‘dbo.TestBatch’. The duplicate key value is (1).
The statement has been terminated.
Um outro exemplo que gera um erro de execução, é quando o objeto não existe. O SQL Server não tem como validar um lote de 10.000 INSERT’s se cada objeto existe na base ou não, pois iria consumir muito processamento, uma vez que durante a execução essa verificação já é feita.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [Teste]; GO IF (OBJECT_ID('TestBatch') IS NOT NULL) DROP TABLE TestBatch CREATE TABLE TestBatch ( ColA INT PRIMARY KEY, ColB CHAR(3) ); GO INSERT INTO TestBatch VALUES (1, 'aaa'); INSERT INTO TestBatch VALUES (2, 'bbb'); INSERT INTO TestBatch2 VALUES (3, 'ccc'); -- Erro de objeto inexistente. GO SELECT * FROM TestBatch; -- Retorna as linhas 1 e 2. GO |
Msg 208, Level 16, State 1, Line 13
Invalid object name ‘TestBatch2’.
Desativando o Autocommit no SQL Server
Agora que você já entendeu como funciona o Autocommit e suas regras de validação, vou demonstrar como desativar o Autocommit no SQL Server, fazendo com que seja necessário executar o COMMIT ou ROLLBACK ao final de todo o batch de comandos DML no SQL Server para que as informações sejam commitadas no banco.
Como desativar o Autocommit a nível de sessão
Para desativar o Autocommit a nível de sessão e fazer com que seja necessário executar o COMMIT ou ROLLBACK ao final dos seus comandos para que eles sejam realmente aplicados no banco, basta executar o comando abaixo:
|
1 2 |
SET IMPLICIT_TRANSACTIONS ON GO |
Lembre-se que isso vale apenas para a sessão atual. Se você abrir uma nova janela de query, essa opção não está ativada e você precisará executar esse comando sempre que for abrir uma nova janela.
Como desativar o Autocommit no SQL Server Management Studio (SSMS)
Para desativar o Autocommit no SQL Server Management Studio (SSMS) automaticamente, ou seja, sempre que você abrir uma nova query o modo IMPLICIT_TRANSATIONS for ativado, basta seguir os passos abaixo:
Abra o menu “Tools” do SQL Server Management Studio e selecione a opção “Options…”
Na tela de opções, navegue na categoria “Query Execution” > “SQL Server” > “ANSI” e marque o check “IMPLICIT_TRANSACTIONS”
Após marcar essa opção e clicar em “OK”, sempre que você abrir uma nova janela de query (New Query Window), a opção IMPLICIT_TRANSACTIONS estará ativada por padrão (você pode desativar a nível de sessão, utilizando SET IMPLICIT_TRANSACTIONS OFF).
Vale lembrar que essa alteração é aplicada somente nas novas janelas que serão abertas. As janelas que já estão abertas não são afetadas.
And that's it, folks!
Espero que tenham gostado desse post e até a próxima.
Portuguese 
English
Thanks Dirceu. This article is extrelly important, simple to understand.
obrigado mais uma vez
Muito interessante, obrigado pelas informações.
Dirceu, tenho uma dúvida do meu ambiente, usamos “IMPLICIT_TRANSACTIONS” no studio.
as vezes algumas sessões o auto commit fica ativado mesmo com a opção no studio(“IMPLICIT_TRANSACTIONS”) marcada, você já viu isso e pode me dar alguma dica?
Bom dia, Dirceu!
Como você conseguiu alterar a cor da barra de ferramentas do Management Studio? E possível fazer isso no 2012?
Bruno, bom dia.
No sql 2012 não, só no management studio 2016 em diante.