auto commit transaction

Auto commit Transaction

 

I have  many scripts files for a DB migration. My intension is to combine all scripts into a single file so any developer can easily run this file when they want to migrate database. So I kept all the scripts separating with batch (GO) into a single file. While testing, I ran that file by pressing F5). I got some error message and also some row effected messages.

Problem: Now the problem is ‘is Roll Backed all the statements of that BATCH where statement got failed? Or all remaining statements of that failed batch are committed?’ My database session is in Autocommit mode.

 

  Solution :

In auto commit mode, it sometimes appears as if an instance of the Database Engine has rolled back an entire batch instead of just one SQL statement. This happens if the error encountered is a compile error, not a run-time error. A compile error prevents the Database Engine from building an execution plan, so nothing in the batch is executed. Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. In the following example, none of the INSERT statements in the third batch are executed because of a compile error. It appears that the first two INSERT statements are rolled back when they are never executed.

 

 

 

In the following example, the third INSERT statement generates a run-time duplicate primary key error. The first two INSERT statements are successful and committed, so they remain after the run-time error.

 

 

 

 

The Database Engine uses deferred name resolution, in which object names are not resolved until execution time. In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

 

 

 

 

 

Refrence: MSDN