Transaction and connection

Apr 202009

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 :)

Kill active connections to the database

Nov 282007

This script is able to kill the active connections to a database in case you need an exclusive lock for example.

As always, do remember to back up your database(s) before running strange new scripts. I’ve not had any illeffects from using it, as it simply only closes the connections, and modifies nothing else, but hey – “real men don’t backup, they just cry alot”. So backup is a good idea before doing anything new.

Anyways – the script.

USE master
SET NOCOUNT ON;

DECLARE @SPIDToKill INT
DECLARE @KillQuery VARCHAR(400)
DECLARE @DBName VARCHAR(200)
SET @DBName = 'DATABASE NAME HERE'

--First check if it is needed to run.
IF EXISTS(
SELECT T1.SPID
FROM SYSPROCESSES T1
INNER JOIN SYSDATABASES T2 ON T1.DBID=T2.DBID
WHERE T2.NAME=@DBName
) BEGIN

PRINT('Active Connections')
--Create table for the session id's
DECLARE @SPIDTable TABLE (SPID INT)
--insert into the table
INSERT INTO @SPIDTable
SELECT T1.SPID
FROM SYSPROCESSES T1
INNER JOIN SYSDATABASES T2 ON T1.DBID=T2.DBID
WHERE T2.NAME=@DBName

--Declare a cursor to run through the SPIDTable entities
DECLARE SPID_CURSOR CURSOR FOR
SELECT * FROM @SPIDTable
OPEN SPID_CURSOR
FETCH NEXT FROM SPID_CURSOR INTO @SPIDToKill

--while we can fetch IDs from SPIDTable
WHILE(@@FETCH_STATUS =0) BEGIN

PRINT('Attempting to kill SPID: ' + CONVERT(VARCHAR,@SPIDToKill))
--Build the kill query
SET @KillQuery = 'KILL '+ LTRIM(STR(@SPIDToKill))
--exec it.
EXEC (@KillQuery)
FETCH NEXT FROM SPID_CURSOR INTO @SPIDToKill
END

--clean up
CLOSE SPID_CURSOR
DEALLOCATE SPID_CURSOR
END ELSE BEGIN
PRINT('No active connections')
END