Transaction, Batch and Exception in SQL Server

Transaction and batch can include each other, exception can stop a batch but usually never rollback a transaction. In the case of exception with error level equal or above 20, since SQL Server tries to close the connection or terminate the process, the transaction will be rolled back as I can imagine.

Transaction has characters of ACID. It guarrantties atomic operations between statements included in it. Batch is a way to submit a bounch of statements in one effort. SQL Server compiles a query plan for a batch.

1. Transaction can have multiple batches

BEGIN TRAN
DML1
DML2
GO
DML3
GO
....
GO
END TRAN

Example:

begin tran;
create table t(i1 int,c1 varchar(10));
select @@TRANCOUNT;
go
insert into t values(1,'a'),(2,'b');
go
select @@TRANCOUNT;
select * from t;
rollback;
select @@TRANCOUNT;
select * from t;

2. A Batch can be composed of several transactions. In the following example, two transactions are submitted to database in one batch.

BEGIN TRAN
DML1
END TRAN
BEGIN TRAN
DML2
END TRAN
GO

Example:

begin tran;
create table t(i1 int,c1 varchar(10));
select @@TRANCOUNT as numTrans_1;
commit;

begin tran;
insert into t values(1,'a'),(2,'b');
select @@TRANCOUNT  as numTrans_1;
commit;

select @@TRANCOUNT  as numTrans_0;
select * from t;
drop table t;
go

3. Exception and Batch

3.1 Statement level exception

SQL Server continue to execute rest of a batch should a statement level exception happens. The statement itself fails of course.

select power(3, 32)
print 'this still runs'
--print is better than select in this test case since it's printed together with error message
go

3.2 Batch level exception

SQL Server stops running rest of statements in a batch when batch level 3xception happens. The exception also bubbles up each level of calling stacks, and aborts all of them. Connection to SQL server is still good.

select convert(tinyint, 'aaa')
select 'this will not execute'
go

--following code demonstrate exception bubbling up
create procedure bubbleUp
as
begin
select convert(tinyint,'aaa')
end

go

exec bubbleUp
select 'this will not return'
go

3.3 Connection level exception

The connection to the SQL Server will be closed if it happens.This is usually caused by internal server errors. I haven't tried that hard to make it happen. :)

Exception with error level 20 or above will make SQL Server to terminate the process.

Note: My connection kept alive even after SQL Server said it was terminating the process. MS really knows how to give us hard time on its error handling mechanism, like you can never make a clear understanding on state returned in error message.
--

select top 1 * from sys.objects
raiserror('this test will break the connection.',20,1) with log;
select top 1 * from sys.objects
--result

(1 row(s) affected)
Msg 2745, Level 16, State 2, Line 2
Process ID 51 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 2
this test will break the connection.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
3.4 Parsing and scope-resolution level exception

They will act like batch level exception if they happen in the same scope as rest of statements in a batch; They will act like statement level exception if they happen in lower level scope such as in a procedure or function called in a batch.

--direct in a batch
selectxyz from table
select 'this will not run'
go

--in lower scope of a batch, such as in another statement
exec('selectxyz from table')
select 'this will return'

--scope resolution
--this will be created because syntax is correct

create procedure scopeResolutionException
as
begin
select xxx from nonExistingTable
end
go

--this will fail because querying non-existing table
exec scopeResolutionException
select 'this will execute'
--also clean it up
drop procedure scopeResolutionException
go

-- scope resolution error, whole batch is aborted
select xxx from nonExistingTable

select 'this will not execute'
go

NOTE: when a statement is committed to SQL Server, SQL Server does parsing and compilation together. For a stored script, SQL Server parses the syntax but does not compile execution plan at same time. It uses late binding and create execution plan upon the time it executes the stored unit.

4. XACT_ABORT

Understanding the different level of exceptions in SQL Server is necessary since we can't handle the them well without knowing them the first. But to figure out what exception belongs to what exception type is tedious and cumbersome. The people who understand this well won't bother to deal with each situation, the people who do not understand this well also made success program, why?

SET XACT_ABORT ON;

This is our star. It aborts both batch and transaction when any level of exception occurs. Aborting transaction here means rolling back the transaction. It turns all the complex situations into one simple scenario: stop running rest of code, rollback transaction and bubble up exception to caller(if there's no TRY/CATCH).

To make a full use of it, it's better to use explicit transaction to organize related operations into one atomic transaction. Then most of time, you just need to remember to have the transaction committed.

CREATE PROCEDER ppp
AS
BEGIN
SET XACT_ABORT ON;
BEGIN TRAN
.....
COMMIT TRAN
END

For the situation TRY/CATCH is used in the code, please see this post:http://mashijie.blogspot.ca/2012/11/doomeduncommittable-transaction.html