Transaction deadlocked on lock resources with another process

Overview

This article explains what causes the "Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim" error found in the server error log files and provides the procedure that can be used to carry out further troubleshooting in order to isolate the root cause if the problem persists.

 

Information

A common issue in SQL Server environments is deadlocks which are often more prevalent in large OLTP deployments.  A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.  When this situation occurs and there is no way for these processes to resolve the conflict, SQL Server will choose one of the processes as the deadlock victim and rollback that process, so the other process or processes can move forward.

By default when this occurs, the error message that SQL Server sends back to the client is similar to the following:

Msg 1205, Level 13, State 51, Line 3
Transaction (Process ID xx) was deadlocked on {xxx} resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction. 

These transaction deadlock errors will appear in the Everest server error log files located at %ProgramData%\Everest\Temp\svrerr.log in Windows environments as:

Transaction_deadlocked.png

Usually, SQL Server is able to internally manage and recover from these deadlock situations automatically and the errors can be safely ignored if users are still able to complete processes and save desired data in Everest.

However, if this persists and results in client-side errors, a database administrator can use SQL Server Extended Events to monitor and capture deadlock events and related information useful for further troubleshooting to identify the root cause. Customizations and external integrations with Everest are known to occasionally cause deadlock situations where multiple processes try to work on the same table or same set of records at the same time.

This external article on Capturing SQL Server Deadlocks using Extended Events provides the steps you can take to capture deadlock information and some useful tips you can take to mitigate the problem.

Back to top

Comments

0 comments

Please sign in to leave a comment.