No tags yet.
An elegant function for a more civilized age:
Another way to detect deadlocks is to utilize the TRY CATCH block to raise errors in TSQL code. I found this to be a very elegant way of detecting deadlocks pragmatically. You can use the below code sample in a stored procedure.
Essentially, you can make it so that if the error number is within 1204, 1205, 1222 it will return a clear message that a deadlock has occurred.
What if we had an error that wasn't a deadlock?
If another error occurred that was NOT a deadlock, it would be nice to know what it was. So we added additional logic to capture the error message, state, and severity and used the RAISERROR function to display the relevant information.
For more information on RAISERROR please checkout Microsoft's documentation here.