INF: Boundaries of Implicit Transactions

• Microsoft SQL Server version 4.2 for OS/2
• Microsoft SQL Server, version 4.2

SUMMARY

This article discusses what constitutes a implicit transaction in SQL Server.

MORE INFORMATION

Users can explicitly delimit the boundaries of transactions with the BEGIN, COMMIT, and ROLLBACK TRANSACTION commands. These user-defined transactions are started with a BEGIN TRANSACTION command. All updates done from the BEGIN TRANSACTION command to a subsequent COMMIT TRANSACTION or ROLLBACK TRANSACTION are considered to be one transaction and are either all successful or all rolled back as one unit.

Outside of a user-defined transaction, SQL Server defaults to considering each individual UPDATE, INSERT, or DELETE command to be a distinct transaction. These are known as implicit transactions. If a batch of UPDATE commands is submitted and an error occurs when executing one of the commands, all of the updates prior to that command will have already been committed to the database because SQL Server would have considered each one to be an implicit transaction.

However, there are times when it might appear that SQL Server has not followed this rule. These are due to errors which occur before a batch of commands is executed and are discussed briefly in the SQL Server "Programmers Reference for C" section on the dbsqlsend() command.

Commands are always sent to SQL Server in a batch. In the SQL Server tools, a batch is terminated with a go command:

   select * from sysusages
   select dbid, name from sysdatabases
   go

If some error prevents SQL Server from compiling the execution plan, none of the commands in the batch are executed, and the user might think the entire batch was rolled back. Examples of this type of error include (but are not limited to) invalid names, syntax errors, or permissions errors. While it might appear the entire batch was rolled back, instead nothing in the batch was executed.

If the execution plan is successfully built and SQL Server begins executing the plan, then the standard rule that each individual command is a distinct transaction (outside a user-defined transaction) applies. If a run time error occurs during the execution of one of the commands in the batch, any UPDATE commands already executed will have been committed. Examples of these types of errors include (but are not limited to) insufficient resource errors, trigger violations, and deadlocks.