(Reprint) Find out and fixing deadlock by sql server 2005 immediately

reprint from: http://weblogs.sqlteam.com/mladenp/archive/2008/05/21/SQL-Server-2005-Immediate-Deadlock-notifications.aspx

This article is focusing on about using Sql Server 2005's features to find out and handling deadlock problems.

1, Try Catch

2, After Catch Exception, checking 

XACT_STATE() = -1 // transaction has uncommitted

3, After that then checking 

ERROR_NUMBER() = 1205 // deadlock

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
-- check transaction state
IF XACT_STATE() = -1
BEGIN
DECLARE @message xml
-- get our deadlock info FROM the VIEW
SET @message = '<TransactionLocks>' + ( SELECT * FROM vLocks ORDER BY SPID FOR XML PATH( 'TransactionLock')) + '</TransactionLocks>'

-- issue ROLLBACK so we don't ROLLBACK mail sending
ROLLBACK

-- get our error message and number
DECLARE @ErrorNumber INT, @ErrorMessage NVARCHAR(2048)
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE()

-- if it's deadlock error send mail notification
IF @ErrorNumber = 1205
BEGIN
DECLARE @MailBody NVARCHAR( max)
-- create out mail body in the xml format. you can change this to your liking.
SELECT @MailBody = '<DeadlockNotification>'
+
( SELECT 'Error number: ' + isnull( CAST(@ErrorNumber AS VARCHAR(5)), '-1') + CHAR(10) +
'Error message: ' + isnull(@ErrorMessage, ' NO error message') + CHAR(10)
FOR XML PATH( 'ErrorMeassage'))
+
CAST(ISNULL(@message, '') AS NVARCHAR( MAX))
+
'</DeadlockNotification>'
-- for testing purposes
-- SELECT CAST(@MailBody AS XML)

-- send an email with the defined email profile.
-- since this is async it doesn't halt execution
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'your mail profile',
@recipients = 'dba@yourCompany.com',
@subject = 'Deadlock occured notification',
@body = @MailBody;
END
END
END CATCH
GO

The main part of this stored procedure is of course the CATCH block. The first line in there is check of the XACT_STATE()
value. This is a scalar function that reports the user transaction
state. -1 means that the transaction is uncommittable and has to be
rolled back. This is the state of the victim transaction in the
internal deadlock killing process. Next we read from our vLocks view to
get the full info (SPID, both SQL statements text, values, etc...)
about both SPIDs that created a deadlock. This is possible since our
deadlock victim transaction hasn't been rolled back yet and the locks
are still present. We save this data into an XML message. Next we
rollback our transaction to release locks. With error message and it's
corresponding number we check if the error is 1205 - deadlock and if it
is we send our message in an email. How to configure database mail can
be seen here.

Both the view and the stored procedures template can and probably should be customized to suit your needs.

 

Testing the theory

Let's try it out and see how it works with a textbook deadlock example that you can find in every book or tutorial.

-- create our deadlock table with 2 simple rows
CREATE TABLE DeadlockTest ( id INT)
INSERT INTO DeadlockTest
SELECT 1 UNION ALL
SELECT 2
GO

Next create two stored procedures (spProc1 and spProc2) with our template:

For spProc1 replace <SPROC TEXT GOES HERE> in the template with:

UPDATE DeadlockTest
SET id = 12
WHERE id = 2

-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest
SET id = 11
WHERE id = 1

 

For spProc2 replace <SPROC TEXT GOES HERE> in the template with:

UPDATE DeadlockTest
SET id = 11
WHERE id = 1

-- wait 5 secs TO SET up deadlock condition IN other window
WAITFOR DELAY '00:00:05'

UPDATE DeadlockTest
SET id = 12
WHERE id = 2

 

Next open 2 query windows in SSMS:

In window 1 run put this script:

exec spProc1

In window 2 put this script:

exec spProc2

 

Run the  script in the first window and after a second or two run
the script in the second window. A deadlock will happen and a few
moments after the victim transaction fails you should get the
notification mail. Mail profile has to be properly configured of
course.

The resulting email should contain an XML with full info about the
deadlock. You can view it by commenting msdb.dbo.sp_send_dbmail
execution and uncommenting the SELECT CAST(@MailBody AS XML) line.

转载于:https://www.cnblogs.com/jerryhong/archive/2009/07/06/1517894.html