What is transaction(Process ID 116 )deadlocked on Lock?

0
votes

I got below error message from application:

Transaction(Process ID 116 ) was deadlocked on Lock | communication buffer resources with another process and has been chosen as the dead lock victim. Return the transaction.

I have read the blog "https://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/"

But I want to know what is (Process ID 116) and what cause of error?

Regards,

Rahul.

2
sql-server
Its the sessionid that ran the transaction at the time. you can run dbcc inputbuffer(116), this will find the query that ran. – mvisser
Can you post the sample code and scenario details? It can help us to understand your problem. – Vishwanath Dalvi

2 Answers

0
votes

Rahul, this indicates the session_id. If you are familiair with Adam Machanic's sp_WhoIsActive, you can run that. Or download it at http://whoisactive.com/.

Apart from your specific problem, this stored proc provides a lot of nice insights, including blocking info and other diagnostic info.This might help you check the cause of the error.

0
votes

What is (Process ID 116)?

SPID in SQL Server is a Server Process ID. These process ID's are essentially sessions in SQL Server. Every time an application connects to SQL Server, a new connection (or SPID) is created. Process ID 116 is nothing but SPID 116

USE EXEC sp_who2 for finding all sessions in SQL server.

What cause of error??

Deadlock occurs when any two SQL server process IDs are locking a separate resource and each one of them is trying to access the resource locked by the other process.

Fix/Resolution:

  • Try to use SQL server profiler to Analyze Deadlocks. http://msdn.microsoft.com/en-us/library/ms188246.aspx

  • In each script that has a transaction(locking) invoived, Begin the transaction as late as possible and commit it as soon as possible.

  • Try to set the optimal lock time value depending up on your script.use @@LOCK_TIMEOUT

  • SET the DEADLOCK_PRIORITY value in each script so that the priority processes wont be deadlocked.Use SET DEADLOCK_PRIORITY

  • Use query hints for each TSQL Query. Use WITH NOLOCK in select statements (when the waiting/locking is not needed)