Last night I wrote a bit about an experience I had where the transaction blocked access.
So I decided to quickly write about how to rollback a transaction.
Suppose we have two tables:



I run the following SQL to illustrate wraping some INSERTs in a transaction.

BEGIN TRAN

BEGIN try

INSERT INTO TableOne VALUES ('aa')
INSERT INTO TableTwo  VALUES (SCOPE_IDENTITY(), 'bb')
INSERT INTO TableTwo  VALUES (-1, 'cc')
INSERT INTO TableOne VALUES ('aa')

END try
BEGIN catch
ROLLBACK TRAN
END catch

IF @@TRANCOUNT = 1 
   COMMIT TRAN

What happens here is the first insert will insert 'aa' into TableOne.
Then I use the identity value from TableOne and inserts into TableTwo in the subsequent insert. This will succeed. The third insert will try to insert a value -1 which will throw a foreign key restraint error.

Because I have wrapped in a transaction, and utilize the Try/Catch functionality (like in most other programming languages) – I can within rollback nicely within the "catch", and make sure nothing gets committed to the database.

The end of the query tests the transaction counter (@@TRANCOUNT) and whether or not the transaction should be committed. This way I avoid having open transactions, and I avoid trying to close a transaction which have already been handled. Each BEGIN TRAN(saction) will increment @@TRANCOUNT, whereas ROLLBACK and COMMIT will decrement it.

I had an experience at work today when I started playing around with transactions.

Basically – unless you play around with the isolation levels (a large topic on its own, for another time), if you open a transaction it’ll lock access to the table(s) you are modifying within said transaction.
Nothing strange about that.

However I experienced a transaction timing out when called from my data-access code layer. That meant the transaction was still “live” and blocked access to the table.
The lock could only be solved in one of 3 ways.
Either I had to manually kill the connection (for example via activity monitor) in SQL Server.
Or I had to wait until the application pool (aspnet process) got recycled (or I could kill it manually).
Or I had to wait until SQL Server killed the transaction (which in my test examples took around 5 minutes).

The lesson learned here is that one needs to watch the transactions and their running because you can end up with a long period of locking.

The way I tested this was I made an asp.net webform which called a Stored Procedure.
In this procedure I started a transaction, and then performed some actions which I knew would result in a time out when called from my webform. Even after the call from my webform had failed with a timeout, access to the affected table was still locked until I did as mentioned.

Transactions are a good thing, but you need to be aware of them. And remember to use rollback, cause otherwise transactions are pretty useless :)