Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)

Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),

A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.

Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.

Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN TRANSACTION … COMMIT/ROLLBACK, porém, a realidade é outra.

Na rotina corrida de desenvolvedores que normalmente acumulam a função de DBA raramente se tem este cuidado. Pior, em alguns casos, o encarregado de executar o script é o velho conhecido estagiário.

Aqui podem surgir questões como:

  1. É só não usar a senha do SYSADMIN.
  2. Por que o estagiário vai ter acesso ao banco de produção?
  3. Se o processo de desenvolvimento for seguido corretamente este risco é anulado.
  4. Não dar acesso ao SSMS para pessoas indevidas.

Realmente se estas e outras precauções forem seguidas não haverá muitos riscos. Por outro lado, qualquer recurso adicional é  bem vindo e o propósito deste post é dar condições para quem usa o SSMS evitar acidentes de percurso.

Desativando o auto commit

Para fazer isto é necessário configurar o SSMS para usar transações implícitas, ou seja, sendo obrigatório executar o commit ou rollback.

A configuração deve ser feita através do menu Tools > Options (Ferramentas > Opções). Na janela que se abre devem ser selecionadas as opções Query Execution > SQL Server > ANSI. Na janela o campo SET IMPLICIT TRANSACTIONS deverá ser marcado.

SSMS1

Esta opção está disponível tanto para o SQL Server 2012 como para o 2008 e também pode ser executada via script embora, para manter a configuração no SSMS tenha de ser feita via interface gráfica.

Resultado da configuração

Após a configuração, as consultas novas (é recomendável reiniciar a aplicação), passarão a exigir o commit ou rollback. Considere o seguinte exemplo, baseado no banco de dados AdventureWorks2012 que é um banco de dados de exemplo disponibilizado pela Microsoft para testes.

Executando o seguinte SELECT na tabela de pessoas (Person.Person) :

select top 10 p.FirstName, p.LastName
from person.Person p

Trará o seguinte resultado:


SSMS2


Agora ao executar uma atualização na coluna LastName:


update person.person set lastname='Silva'

O resultado será o seguinte:


SSMS3


Opa… se executar o select anterior:


SSMS4


Agora, se as transações estiverem implícitas (com auto commit off), ao fechar o SSMS a seguinte mensagem será dada:


SSMS5


O que quer dizer que o SSMS detectou que há transações pendentes. No caso, o update faz parte destas transactions pois, mesmo que várias sejam executadas, nenhuma delas será enviada de fato ao banco incluído aí SELECTs. Se clicar em No todas serão desfeitas e o banco retorna a situação original.


Verifique estas configurações sempre que estiver usando a ferramenta SSMS e lembre de inserir o commit nos scripts que serão enviados ao banco sempre depois que as operações forem confirmadas e estiverem corretas.


Até a próxima.