SQLState 40001 NativeError 1205 deadlock victim

jb2009
jb2009 used Ask the Experts™
on
We received a deadlock condition on a SQL server running a proprietary software application.  The software vendor said this occurred because of a computer network issue.  This application is running on a VMware virtual server.  Any suggestions for finding  a solution to this issue are welcome.  Is it possible for this to occur due to a network issue?  The application runs on the server where SQL is installed (the database is not on a separate server).
Error message [Microsoft] [ODBC SQL Server Driver] [SQL Server] Transaction (Process ID 117) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.  Rerun the transaction.  
 
We have not been able to identify the specific transaction.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
James MurrellProduct Specialist

Commented:
There are two reasons for this error to occur:
One:
This error has occured due to a select query and update command accesses one table at the same time from two resources.
This can be avoided if we apply a with(nolock) on the select query provided.

Two:
It could be due to an index is being accessed by two resources as specified above. This issue can be resolved if we rebuild the indexes for the table. This feature is available in SQL Server 2005. The deadlock issue can be reduced by doing reduild of indexes.
Commented:
It is not a network issue.

Deadlocks are when two commands try to access the resource, and SQL cannot handle the locks.  One process is chosen as the victim and terminated.  Usually one long-running  process hits another.

Understanding the problem is important first.  I wouldn't just apply a solution.  Nolocks allow dirty reads.  That's fine if the data stays static.  But if it doesn't, you might get inaccurate results.  Index rebuilding is a possible solution, but you need to know what is going on.

Go to the server and look in the Event Log under application to see if there is any more information.

Next by opening a SQL query window and running sp_who while the problem is happening.  Look for processes that have a LastBatch that is several days old.  

Next run sp_lock.  Lock for cases where you see lots of locks for the same object_id.  You can get the object name by running select db_id (dbid), then going to that database, and running select object_name (objid) from the object window.  That is a contested resource.

There are many approaches.  Update statistics <table name> just to make sure the index stats are fresh.
You can check index fragmentation to see if there is high indexes fragmentation and then use alter index  reorganize to defrag it.

This should hopefully give a start.

Author

Commented:
Thank you for your solution.  The problem is sporatic so looking while the problem is happening is difficult.  It did help that you specified this is not a network issue.  I returned to our software vendor and their additional research showed some areas we need to focus on to pinpoint the cause of this issue.  Fragmentation is one possibility, but the data in this specific database is only one days work of activity.  At the end of each day the data gets passed off to another database, which should cause this database to clear and reduce in size, but instead this database is the same size as the one that retains a weeks worth of data.  Thus, we believe the issue lies in how the database releases the data, or actually is not releasing the data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial