deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.

Question

Post reply

deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim.

- Win.

SSCertifiable

Points: 6790
More actions
November 14, 2010 at 9:38 pm

#388226

Message
Executed as user: NT AUTHORITY\SYSTEM. Transaction (Process ID 68) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
We are facing the above error in most of our environment (Servers). Googled lot, but couldnot find exact suggestions and steps to be taken care.
Can anyone put me into a route and resolve these issues. These jobs executes at every half an hour, starts from 12:00 AM to 11:59 PM. For every couple of hours we are facing the errors in the environment. When I execute manually after 5 mins, it runs fine.
Thanks.
- Win.
Cheers,
- Win.
" Have a great day "

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

It is a contention issue between two resources. You may find these articles useful to resolve deadlock issues.

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/[/url]

http://msdn.microsoft.com/en-us/library/ms178104.aspx

http://msdn.microsoft.com/en-us/library/aa175791(SQL.80).aspx

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

- Win. SSCertifiable Points: 6790 More actions · Answer 2

I am confused of reading many articles and yet no conclusion for my issue...

What actually has to be done - Do I need to set the Deadlock priority to LOW, NORMAL etc...

Cheers,
- Win.

" Have a great day "

Adiga One Orange Chip Points: 27284 More actions · Answer 3

Using the above mentioned examples, please try to figure out which processes are getting dead locked. Also try to find if it is a one time occurring or a recurring issue.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

SQLsprawl Old Hand Points: 326 More actions · Answer 4

You need to identify which process(s) has a lock on the resources that your job is attempting to leverage. MSSQL will automatically select the deadlock victim based on how much and what type of work each process is doing. If the other process(s) are not completing mission critical operations and you are sure that your work should be given the right of way, you can use the WITH LOCK hint to secure access to the required resources. MSSQL will then choose the other process as the victim if it is not also using WITH LOCK. As best practice, you should know exactly what each process is doing and monitor for deadlocks when the job is run.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 5

Follow these steps:

1. Check if the same query is causing locks then tune it with (nolock) hints in queries.

2. try to schedule it in different time and see.

3. Check Isolation level and see new isolation level if you are using 2008.

4. See what wait_types you see in sys.dm_os_waiting_tasks.

5. Check for ad-hoc queries frequently running and turn them into store procs.

May be helpful if you are in high OLTP.

Regards,

Pavan Srirangam.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

- Win. SSCertifiable Points: 6790 More actions · Answer 6

Thanks all.

These are recurrent errors.

We have to schedule to different time interval I believe. As application teams are not ready to do R&D on prod to identify. We are worried about this and the application teams wont allow us to run scripts as its a stock DB.

Will come back to you, if got any clues to check.

- Win.

Cheers,
- Win.

" Have a great day "

ps. One Orange Chip Points: 29422 More actions · Answer 7

You either need to run profiler to capture the deadlock graph OR enable trace flag 1222 on your system

dbcc traceon(1222)

This will capture deadlock graph in the SQL Server's errorlog.

You can then analyze the information to see which queries are offending queries and what are they waiting on.

Pradeep Singh

WayneS SSC Guru Points: 95409 More actions · Answer 8

ps. (11/16/2010)
You either need to run profiler to capture the deadlock graph OR enable trace flag 1222 on your system
dbcc traceon(1222)
This will capture deadlock graph in the SQL Server's errorlog.
You can then analyze the information to see which queries are offending queries and what are they waiting on.

I prefer to run a server-side trace to capture deadlock information. (Click here for script[/url]).

Once the deadlock has occurred, you can read the trace file with this script[/url]. I have an updated deadlock reader script - let me know if you want me to find it for you.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ps. One Orange Chip Points: 29422 More actions · Answer 9

Thanks Wayne. It's advisable not to use profiler or client side trace on a loaded production box.

Pradeep Singh

h_roderick Valued Member Points: 57 More actions · Answer 10

Just a hunch that your database backups are conflicting with your process and winning the deadlock.

alicesql SSCrazy Points: 2649 More actions · Answer 11

We had this issue as well, and could not definitively determine the conflict (it wasn't backups). I finally put in a retry attempt after 10 minutes and now the step completes successfully. This is for a job that runs once a day and inserts to a table upon which a CEO financial report depends. That report does not look good if the deadlock victim doesn't succeed.

John.Garvey SSC Enthusiast Points: 113 More actions · Answer 12

If your query allows uncommitted reads, another option is to use WITH (NOLOCK).

SQLBeginner2012 SSC Journeyman Points: 87 More actions · Answer 13

I know this is an older post, but I believe the key thing to note is the "deadlocked on lock | communication buffer resources". This is a pretty atypical deadlock from my experience.

Please reference the following post:

http://dba.stackexchange.com/questions/49538/sql-server-deadlocked-on-lock-communication-buffer-resources/72170