SQL Server - Knowing and Disabling AutoCommit

Views: 2.732
Reading Time: 5 minutes

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 DBMSs (Oracle, Postgree, etc.) this is not enabled by default, which leads to an oddity for the DEV's and DBA's that came from these technologies. Whenever we talk about data changes in Production, we should keep in mind the use of transactions. This is especially useful and safe because, in case of any errors in the data update process, we can undo all changes made with a simple ROLLBACK command. If you are not using a transaction and have made multiple INSERT's, UPDATE's, and DELETE's, you will have a great job of undoing these changes, not to mention the time when the database will have the wrong 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.

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 processing begins, as they would require too much processing to validate this information. Imagine SQL Server having to validate if 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.

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 a runtime error is when the object does not exist. SQL Server cannot validate a batch of 10.000 INSERT's whether each object exists in the base or not, as it would consume a lot of processing, as this check is already done at run time.

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:

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 in SQL Server Management Studio and select the "Options…" option.

In the options screen, navigate to the “Query Execution”> “SQL Server”> “ANSI” category and check the “IMPLICIT_TRANSACTIONS” checkbox.

After checking this option and clicking “OK”, whenever you open a new query window (New Query Window), the IMPLICIT_TRANSACTIONS option is enabled by default (you can disable 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.