SQL Server – Conhecendo e desativando o AutoCommit

SQL Server – Conhecendo e desativando o AutoCommit
5 (100%) 2 votos

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.

É isso aí, pessoal!
Espero que tenham gostado desse post e até a próxima.

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

3 Comments

  1. 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?

    Cristiano Schmitt

Deixe uma resposta