SQL Server 2005: Immediate Deadlock notifications
Deadlocks… huh??
Deadlocks can be a pain to debug since they’re so rare and unpredictable. The problem lies in repeating them in your dev environment. That’s why it’s crucial to have as much information about them from the production environment as possible.
There are two ways to monitor deadlocks, about which I’ll talk about in the future posts. Those are SQL Server tracing and Error log checking. Unfortunately both of them suffer from the same thing: you don’t know immediately when a deadlock occurs. Getting this info as soon as possible is sometimes crucial in production environments. Sure you can always set the trace flag 1222 on, but this still doesn’t solve the immediate notification problem.
One problem for some might be that this method is only truly useful if you limit data access to stored procedures. <joke> So all you ORM lovers stop reading since this doesn’t apply to you anymore! </joke>
The other problem is that it requires a rewrite of the problematic stored procedures to support it. However since SQL Server 2005 came out my opinion is that every stored procedure should have the try … catch block implemented. There’s no visible performance hit from this and the benefits can be huge. One of those benefits are the instant deadlocking notifications.
Needed "infrastructure"
So let’s see how it done. This must be implemented in the database you wish to monitor of course.
First we need a view that will get lock info about the deadlock that just happened. You can read why this type of query gives info we need in my previous post.
CREATE VIEW vLocks
AS
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction AS IsUserTransaction,
AT.name AS TransactionName
FROM sys.dm_tran_locks L
LEFT JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
LEFT JOIN sys.objects O ON O.object_id = P.object_id
LEFT JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
LEFT JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
LEFT JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
LEFT JOIN sys.dm_exec_requests ER ON AT.transaction_id = ER.transaction_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE resource_database_id = db_id()
GO
Next we have to create our stored procedure template:
CREATE PROC <ProcedureName>
AS
BEGIN TRAN
BEGIN TRY
<SPROC TEXT GOES HERE>
COMMIT
END TRY
BEGIN CATCH
mytechconnect 