SQL SERVER 2008 R2 - Trasaction and Isolation Levels - "Cannot roll back Transaction. No transaction or savepoint of that name was found."

Who Should Read This Blog: DBA who wants to understan how Transaction works.

Let's run following command first:

1. SELECT @@TRANCOUNT
--Result 0

2. DBCC OPENTRAN()
--Result as follows
Transaction information for database 'Tuition'.
Oldest active transaction:
    SPID (server process ID): 54
    UID (user ID) : -1
    Name          : Affordable
    LSN           : (3073:52373:1)
    Start time    : Jan 11 2011  6:23:47:993PM
    SID           : 0x0105000000000005150000001ac6c4f30376dea8123bc97be8030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

So the difference between these two commands are as follows:

1. @@TRANCOUNT will tell you how many open transactions are there whereas DBCC OPENTRAN() tells only about the oldest trasaction.

2. @@TRANCOUNT will tell you the open transaction as soon as you run BEGIN TRAN but DBCC OPENTRAN() will tell some information only when any other command is run under BEGIN TRAN.

There is more to understand about the transactions and two popular ISOLATION Levels ie READ COMMITTED AND READ UNCOMMITTED:

Let's create a simple test table TestTran with a column server. Populate the table with any data.

Execute the commands as follows:

SELECT @@TRANCOUNT --0
DBCC OPENTRAN()
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Let's run following command now and verify the difference between @@TRANCOUNT and DBCC
OPENTRAN()
BEGIN TRAN Tuition

Let's verify

SELECT @@TRANCOUNT --1
DBCC OPENTRAN()
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I want to add one popular error in the blog which most of the DBAs would get when they try to rollback the transaction. Use follwoing code to see the error:

BEGIN TRAN Affordable

ROLLBACK tran affordable
Msg 6401, Level 16, State 1, Line 1
Cannot roll back Affordable. No transaction or savepoint of that name was found.

Try this now:

ROLLBACK tran Affordable
Command(s) completed successfully.

Now this is the time to go ahead with understanding how nested transactions behave:

Custom Search

Run following command:

BEGIN TRAN Affordable1
UPDATE TestTran
SET Server= 'Affordable1'

BEGIN TRAN Affordable2
UPDATE TestTran
SET Server= 'Affordable2'

BEGIN TRAN Affordable3
UPDATE TestTran
SET Server= 'Affordable3'

Now we shall try to rollback the last trans Affordable3. We get following error msg:

Msg 6401, Level 16, State 1, Line 1
Cannot roll back Afford. No transaction or savepoint of that name was found.

You can commit any internal transaction but cann't rollback. Now if you rollback the transaction the changes affected by the  internal transaction would go away.

Let's also understand READ COMMITTED and READ UNCOMMITTED Transaction Isolation Level.

Let's run following command on one connection.

BEGIN TRAN Affordable1
UPDATE TestTran
SET Server= 'Affordable1'

Run following command on another session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT *
FROM TestTran

The session will hung as you asked the sql server to show you only committed data.

Now run following command on another session

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM TestTran

This data is nothing but the "dirty read" because if you rollback the transaction in the previous session then the data would change.

Regards,
http://tuitionaffordable.webstarts.com