SQL Server – Conhecendo e desativando o AutoCommit

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.

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.

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.

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:

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.