Hello people,
How are you ?
In this post I will talk about a session-level configuration that many people are unaware of and in many cases can be of great help in preventing accidental data loss, which is Autocommit.
In some other DBMS's (Oracle, Postgree, etc.) this is not activated by default, which ends up generating a strangeness for the DEV's and DBA's that came from these technologies. Whenever we talk about data changes in Production, we must keep in mind the use of transactions. This is especially useful and safe, since, in case of an error in the data update process, we can undo all changes made with a simple ROLLBACK command. In case you are not using a transaction and made several INSERT's, UPDATE's and DELETE's, you will have a great job to undo these changes, not counting the time in which the base will have the incorrect information.
According to official SQL Server documentation, the default transaction handling mode for SQL Server is Autocommit, that is, when you perform an UPDATE, INSERT, or DELETE without starting an explicit transaction, these operations are automatically committed.
When you start a transaction, SQL Server enables implicit transaction mode, causing all DML operations to be encapsulated in that transaction. Once you run COMMIT or ROLLBACK, the SQL engine disables implicit transaction mode and returns to Autocommit mode.
I would also like to point out that my goal in this post is not for you to disable Autocommit just because you read it here. You need to understand what Autocommit is and understand if it really makes sense to keep it enabled or not. There are many cases where the DBA may prefer to continue with this (default) SQL Server behavior, while in other cases, especially if you come from Oracle or Postgree, you may want to disable Autocommit. It depends on your profile and how you work.
Compilation and Execution Errors and Transactions
Many times when we are executing several batch commands, we come across an error message in the middle of the scripts and we don't know what was executed and what wasn't, because in some cases nothing is executed and in others everything before. of the line in error has been executed. I will demonstrate why this occurs.
Compilation Errors
Every time you run a command on SQL Server, the SQL engine performs a pre-validation of the commands you execute, performing some validations on the command to prevent it from generating unnecessary processing and at the end of the commands it generates an error that could have been avoided. One of these validations is the command syntax.
When there is a syntax error in one of the commands, execution stops before executing any SQL statement. That is, when there are syntax errors, nothing is executed.
|
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'.
Execution Errors
Unlike compilation errors, which are pre-validated before executing commands in the database, execution errors are not validated before starting processing, since they would require a lot of processing to be able to validate this information. Imagine SQL Server having to validate that there is a duplicate key in a batch of 10.000 INSERT's before processing the command. It would be almost the same processing as the INSERT operation itself.
In this case, when there is a runtime error, the commands are executed and committed to the line that generates the error.
|
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.
Another example that generates an execution error is when the object does not exist. SQL Server has no way to validate a batch of 10.000 INSERTs if each object exists in the database or not, as it would consume a lot of processing, since during the execution this verification is already done.
|
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'.
Disabling Autocommit on SQL Server
Now that you understand how Autocommit and its validation rules work, I will demonstrate how to disable Autocommit on SQL Server, making it necessary to run COMMIT or ROLLBACK at the end of the entire SQL Server DML batch so information is committed to the bank.
How to disable session-level Autocommit
To disable session-level Autocommit and make it necessary to run COMMIT or ROLLBACK at the end of your commands so that they are actually applied to the database, just run the command below:
|
1 2 |
SET IMPLICIT_TRANSACTIONS ON GO |
Remember that this only applies to the current session. If you open a new query window, this option is not enabled and you will need to execute this command each time you open a new window.
How to disable Autocommit in SQL Server Management Studio (SSMS)
To disable Autocommit in SQL Server Management Studio (SSMS) automatically, that is, whenever you open a new query IMPLICIT_TRANSATIONS mode is enabled, just follow the steps below:
Open the "Tools" menu of SQL Server Management Studio and select the option "Options ..."
On the options screen, navigate to the “Query Execution”> “SQL Server”> “ANSI” category and check the “IMPLICIT_TRANSACTIONS” check
After checking this option and clicking "OK", whenever you open a new query window (New Query Window), the IMPLICIT_TRANSACTIONS option will be enabled by default (you can disable it at the session level, using SET IMPLICIT_TRANSACTIONS OFF).
Remember that this change is only applied to new windows that will open. Windows that are already open are not affected.
That's it folks!
I hope you enjoyed this post and see you next time.
Dirceu, I have a question about my environment, we use “IMPLICIT_TRANSACTIONS” in the studio.
sometimes some sessions the auto commit is activated even with the option in the studio (“IMPLICIT_TRANSACTIONS”) checked, have you seen this and can you give me any tips?
Good morning, Dirceu!
How did you manage to change the color of the Management Studio toolbar? Is it possible to do this on 2012?
Bruno, good morning.
In sql 2012 not, only in management studio 2016 onwards.