We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.
Any suggestions would be appreciated.
Thanks,
DanHowdy
Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........
I assume you have plenty of disk space for the TEMPDB and the database files?
More info / background would be useful.
Cheers,
SG
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Deadlocking Question
I have a ColdFusion web application running on a SQL Server 7.0 back-end.
Within this application, there is are three queries in a row where the second query deadlocks 1-2 times a day, which is too high. The queries do the following:
1. Insert member details from a web form into member table
2. Select ID (key) of what was just inserted into the member table
3. Update a third table with the member ID
As I said earlier, the 2nd query is the one that I see deadlocked in the ColdFusion error logs. I am unable to replicate the problem, so I have not been able to troubleshoot using the procedures described in SQL-BOL (unless I am mis-understanding the documentation).
This sequence runs an average of 150 times per day, but it can be anywhere from 100 to 500 times, so the failure rate is about 1%.
Any ideas on why this is happening and what I can do to prevent it?
Thanks,
CybermudA spid that does only one thing can't deadlock, it isn't possible.
When a spid accesses an object, it normally takes a lock (of some kind) on that object.
When a spid has an exclusive lock on an object, and another spid tries to access that object, the new spid is "blocked". When a spid is blocked, it stops executing until the object that is causing the blocking becomes available again.
When two spids are running (lets call them 69 and 70), spid 69 locks object A, spid 70 locks object B and everything is still happy. Then 69 attempts to lock object B, but it becomes blocked because 70 already has it locked. Then 70 tries to lock object A, which causes it to be blocked and now we have a deadlock! Both spids are blocked, waiting for each other. SQL Server detects this condition, and picks one of the deadlocked spids as the "victim" and automagically kills the victim (allowing the other spid to proceed).
The best way to avoid deadlocks is to keep your locks small. Don't lock objects for long periods of time. When you do have to lock objects, try to always lock them in the same sequence so that blocks rarely become deadlocks.
If you set Trace flag 1204 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_646r.asp), you'll get more diagnostic information about the cause of the deadlock in your SQL errorlog file.
-PatP|||Pat:
Thanks for the reply. I do not understand what you mean when you say
"A spid that does only one thing can't deadlock, it isn't possible."
Are you saying that since there is only one Cold Fusion application, there will only be one spid? I do understand the principles of deadlocking but can't seem to figure out how to apply them to my situation.
Also, is there any performance hit from leaving trace flag 1204 on for an extended period of time?
Thanks,
Cybermud|||If you think about what a deadlock is, an spid with only one object locked CAN'T deadlock. In order for a deadlock to occur, you must already have one object locked, then try to lock another.
Spid usage depends on how your ColdFusion engine is configured, but typically it will support many threads (therefore many spids). R937 would be able to answer this kind of question much better than I can, although he might prefer you to post it in the ColdFusion (http://www.dbforums.com/f223) forum.
While traceflag 1204 used to impose some significant overhead, I don't believe that is the case anymore. I'd go ahead and run it for a while, but watch for any signs of server distress (just in case!).
-PatP|||Pat:
Thanks for the quick reply. I think I am understanding it correctly...that sequence of the three queries can't be causing a deadlock by itself, because its only one spid, right?
That means there is something else going on...and I will be taking this to the ColdFusion forum.
I also plan on leaving flag 1204 on for the night to see if it turns anything up. Thanks again for all your help.
Cybermud
Within this application, there is are three queries in a row where the second query deadlocks 1-2 times a day, which is too high. The queries do the following:
1. Insert member details from a web form into member table
2. Select ID (key) of what was just inserted into the member table
3. Update a third table with the member ID
As I said earlier, the 2nd query is the one that I see deadlocked in the ColdFusion error logs. I am unable to replicate the problem, so I have not been able to troubleshoot using the procedures described in SQL-BOL (unless I am mis-understanding the documentation).
This sequence runs an average of 150 times per day, but it can be anywhere from 100 to 500 times, so the failure rate is about 1%.
Any ideas on why this is happening and what I can do to prevent it?
Thanks,
CybermudA spid that does only one thing can't deadlock, it isn't possible.
When a spid accesses an object, it normally takes a lock (of some kind) on that object.
When a spid has an exclusive lock on an object, and another spid tries to access that object, the new spid is "blocked". When a spid is blocked, it stops executing until the object that is causing the blocking becomes available again.
When two spids are running (lets call them 69 and 70), spid 69 locks object A, spid 70 locks object B and everything is still happy. Then 69 attempts to lock object B, but it becomes blocked because 70 already has it locked. Then 70 tries to lock object A, which causes it to be blocked and now we have a deadlock! Both spids are blocked, waiting for each other. SQL Server detects this condition, and picks one of the deadlocked spids as the "victim" and automagically kills the victim (allowing the other spid to proceed).
The best way to avoid deadlocks is to keep your locks small. Don't lock objects for long periods of time. When you do have to lock objects, try to always lock them in the same sequence so that blocks rarely become deadlocks.
If you set Trace flag 1204 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_646r.asp), you'll get more diagnostic information about the cause of the deadlock in your SQL errorlog file.
-PatP|||Pat:
Thanks for the reply. I do not understand what you mean when you say
"A spid that does only one thing can't deadlock, it isn't possible."
Are you saying that since there is only one Cold Fusion application, there will only be one spid? I do understand the principles of deadlocking but can't seem to figure out how to apply them to my situation.
Also, is there any performance hit from leaving trace flag 1204 on for an extended period of time?
Thanks,
Cybermud|||If you think about what a deadlock is, an spid with only one object locked CAN'T deadlock. In order for a deadlock to occur, you must already have one object locked, then try to lock another.
Spid usage depends on how your ColdFusion engine is configured, but typically it will support many threads (therefore many spids). R937 would be able to answer this kind of question much better than I can, although he might prefer you to post it in the ColdFusion (http://www.dbforums.com/f223) forum.
While traceflag 1204 used to impose some significant overhead, I don't believe that is the case anymore. I'd go ahead and run it for a while, but watch for any signs of server distress (just in case!).
-PatP|||Pat:
Thanks for the quick reply. I think I am understanding it correctly...that sequence of the three queries can't be causing a deadlock by itself, because its only one spid, right?
That means there is something else going on...and I will be taking this to the ColdFusion forum.
I also plan on leaving flag 1204 on for the night to see if it turns anything up. Thanks again for all your help.
Cybermud
Deadlocking on indexes?
It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
Joel
The order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200804/1
|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.co m...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>
sql
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
Joel
The order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200804/1
|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.co m...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>
sql
Deadlocking on indexes?
It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
JoelThe order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200804/1|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.com...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>> It seems that a common problem we have is deadlocks occurring when one
>> stored proc is updating a row and another store proc is running a read
>> query against the same table. They'll often get deadlocked on two of the
>> indexes for the table.
>> For example:
>> SP1 is updating a row in TableA, causing it to get an exclusive lock on
>> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2
>> and attempts to get a shared lock on TableAIndex1.
>> Deadlock!
>> Of course, I can fix this by lowering the isolation level in SP2 to "read
>> uncommitted", but I'd rather not.
>> I can normally fix deadlocks by changing the order in which locks are
>> acquired, but I don't know how to influence the order in which locks on
>> indexes are acquired. Is it controlled by the order in which the fields
>> are listed in a select or an update statement? If I standardize the
>> order in which individual fields are listed whenever I perform a select
>> or update from a given table, will that effect thr order in which index
>> locks are acquired?
>> Thoughts?
>> Joel
>
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
JoelThe order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200804/1|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.com...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>> It seems that a common problem we have is deadlocks occurring when one
>> stored proc is updating a row and another store proc is running a read
>> query against the same table. They'll often get deadlocked on two of the
>> indexes for the table.
>> For example:
>> SP1 is updating a row in TableA, causing it to get an exclusive lock on
>> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2
>> and attempts to get a shared lock on TableAIndex1.
>> Deadlock!
>> Of course, I can fix this by lowering the isolation level in SP2 to "read
>> uncommitted", but I'd rather not.
>> I can normally fix deadlocks by changing the order in which locks are
>> acquired, but I don't know how to influence the order in which locks on
>> indexes are acquired. Is it controlled by the order in which the fields
>> are listed in a select or an update statement? If I standardize the
>> order in which individual fields are listed whenever I perform a select
>> or update from a given table, will that effect thr order in which index
>> locks are acquired?
>> Thoughts?
>> Joel
>
deadlocked on lock resources. SQL Server 2000
Hi, i am getting this error when i am running a stored procedure.
Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
i think so it is getting this error becasue it blocking it self at one point in the SP
DECLARE cty_Cursor CURSOR FOR
SELECT Country FROM TB_Country
declare @.cty varchar(2)
OPEN cty_Cursor;
FETCH NEXT FROM cty_Cursor into @.cty;
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC SP_DO_SOMETHING @.cty
FETCH NEXT FROM cty_Cursor into @.cty;
END;
CLOSE cty_Cursor;
DEALLOCATE cty_Cursor;
i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.
how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.
Regards,
Haroon
what happens when you run the stored procedure outside of the cursor?
what's in the stored procedure?
Sunday, March 25, 2012
Deadlock trouble shooting
Hi all, I have a rather annoying problem finding out what causes my deadlocks.
I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
scheduled tasks and perhaps 20 databases. I have read a couple of articles
regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
and 3605. I am trying to follow the steps of this article in order to
pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
Furthermore I have used the sqldiag utility as well as profiler and gotten
the outputfile sqldiag.txt and I am looking at it with the help of the
article:
Here are two examples of deadlocks from the sqldiag.txt:
--
2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
2005-03-30 11:29:16.19 spid4 Target Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Wait-for graph
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:1
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Wait List:
2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
Ref:1 Life:00000000 SPID:90 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
move_vcpu_dtl_from_spots @.days2copy = '1'
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:2
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 1::
2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:3
2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 0::
2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
was found.
2005-03-30 11:29:16.19 spid4 --
2005-03-30 11:29:21.19 spid4 --
2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
2005-03-30 11:29:21.19 spid4 Target Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Wait-for graph
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:1
2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 1::
2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:2
2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 0::
2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
was found.
2005-03-30 11:29:21.19 spid4
--
Now as I understand it there should also be a value named "Key XXXX"
somewhere after the "Grant List" but I can not find it. I guess , correct me
if I am wrong, I need that value in order to take the trouble shooting
further... So this is as far as I get. Could someone please give me some
hints and pointers of what I need to do next'
Thanks on forehand!!
--
Alex
--
Computer Science StudentAlexandre geia ,
I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
your code regarding this line and try to find any loops or any other issue.
Also you can delete and create your indexes
Let me know if you need anything else
Andreas
"Alexander Simeonidis" wrote:
> Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> scheduled tasks and perhaps 20 databases. I have read a couple of articles
> regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> and 3605. I am trying to follow the steps of this article in order to
> pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> Furthermore I have used the sqldiag utility as well as profiler and gotten
> the outputfile sqldiag.txt and I am looking at it with the help of the
> article:
> Here are two examples of deadlocks from the sqldiag.txt:
> --
> 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Wait-for graph
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:1
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Wait List:
> 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> Ref:1 Life:00000000 SPID:90 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> move_vcpu_dtl_from_spots @.days2copy = '1'
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:2
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 1::
> 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:3
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 0::
> 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> was found.
> 2005-03-30 11:29:16.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Wait-for graph
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:1
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 1::
> 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:2
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 0::
> 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> was found.
> 2005-03-30 11:29:21.19 spid4
> --
> Now as I understand it there should also be a value named "Key XXXX"
> somewhere after the "Grant List" but I can not find it. I guess , correct me
> if I am wrong, I need that value in order to take the trouble shooting
> further... So this is as far as I get. Could someone please give me some
> hints and pointers of what I need to do next'
> Thanks on forehand!!
>
> --
> Alex
> --
> Computer Science Student|||Geia Andrea :) kai efcharisto!
I am afraid the problem is not that simple. I did as you said and checked my
three
stored procedures, none had anything in common on line #50.
One had a print cmd,
the other an exec cmd and the third
an if stmnt. And these cmds had nothing in common i.e writing to the same
table etc.. So I am still looking for pointers of how to take my
troubleshooting further.
Thank you for your answer...
"Andreas Mavrogenis" wrote:
> Alexandre geia ,
> I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> your code regarding this line and try to find any loops or any other issue.
> Also you can delete and create your indexes
> Let me know if you need anything else
> Andreas
>
> "Alexander Simeonidis" wrote:
> > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > and 3605. I am trying to follow the steps of this article in order to
> > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > the outputfile sqldiag.txt and I am looking at it with the help of the
> > article:
> > Here are two examples of deadlocks from the sqldiag.txt:
> > --
> > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> >
> > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:1
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Wait List:
> > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > Ref:1 Life:00000000 SPID:90 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > move_vcpu_dtl_from_spots @.days2copy = '1'
> >
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:2
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:3
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > was found.
> > 2005-03-30 11:29:16.19 spid4 --
> >
> > 2005-03-30 11:29:21.19 spid4 --
> > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> >
> > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:1
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:2
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > was found.
> > 2005-03-30 11:29:21.19 spid4
> > --
> >
> > Now as I understand it there should also be a value named "Key XXXX"
> > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > if I am wrong, I need that value in order to take the trouble shooting
> > further... So this is as far as I get. Could someone please give me some
> > hints and pointers of what I need to do next'
> >
> > Thanks on forehand!!
> >
> >
> > --
> > Alex
> > --
> > Computer Science Student|||Geia,
Did you droped your indexes ?
Andreas
"Alexander Simeonidis" wrote:
> Geia Andrea :) kai efcharisto!
> I am afraid the problem is not that simple. I did as you said and checked my
> three
> stored procedures, none had anything in common on line #50.
> One had a print cmd,
> the other an exec cmd and the third
> an if stmnt. And these cmds had nothing in common i.e writing to the same
> table etc.. So I am still looking for pointers of how to take my
> troubleshooting further.
> Thank you for your answer...
> "Andreas Mavrogenis" wrote:
> > Alexandre geia ,
> >
> > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > your code regarding this line and try to find any loops or any other issue.
> >
> > Also you can delete and create your indexes
> >
> > Let me know if you need anything else
> > Andreas
> >
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > and 3605. I am trying to follow the steps of this article in order to
> > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > article:
> > > Here are two examples of deadlocks from the sqldiag.txt:
> > > --
> > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > >
> > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > >
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:16.19 spid4 --
> > >
> > > 2005-03-30 11:29:21.19 spid4 --
> > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > >
> > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:21.19 spid4
> > > --
> > >
> > > Now as I understand it there should also be a value named "Key XXXX"
> > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > if I am wrong, I need that value in order to take the trouble shooting
> > > further... So this is as far as I get. Could someone please give me some
> > > hints and pointers of what I need to do next'
> > >
> > > Thanks on forehand!!
> > >
> > >
> > > --
> > > Alex
> > > --
> > > Computer Science Student|||Hi, do you mean dropping all my indexes on the tables involved?
I can not drop the indexes on my tables because the only index I have is the
PK.
To explain the flow of how my stored procedures work:
[Raw data table] -> [tmp-table ] -> [data-table]
The raw data tables contains no keys whatsoever and they are deleted after
each moving of data to the tmp tables. The tmp tables are also deleted after
each move of the data to the data tables. And they have no indexes or keys
either.
So the only table that have indexes are the datatables and for them the
index is the PK. But the PK consists of 5-6 different columns and the amount
of data in the table is about half of million rows.
The problems with deadlocks most often arises when I try to move data from
the raw data tables to the tmp tables.
Thanks a lot for your help and patience...
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student|||Alexandre gria kai pali,
Look, try to delete * from tmp before raw data insertion. Try the insertion
with insert statement rather that the update. I saw the update statement in
the error that you posted.
Also, try to put indexes on you tables if your app allows
Try that and let me know,
Andreas
"Alexander Simeonidis" wrote:
> Hi, do you mean dropping all my indexes on the tables involved?
> I can not drop the indexes on my tables because the only index I have is the
> PK.
> To explain the flow of how my stored procedures work:
> [Raw data table] -> [tmp-table ] -> [data-table]
> The raw data tables contains no keys whatsoever and they are deleted after
> each moving of data to the tmp tables. The tmp tables are also deleted after
> each move of the data to the data tables. And they have no indexes or keys
> either.
> So the only table that have indexes are the datatables and for them the
> index is the PK. But the PK consists of 5-6 different columns and the amount
> of data in the table is about half of million rows.
> The problems with deadlocks most often arises when I try to move data from
> the raw data tables to the tmp tables.
> Thanks a lot for your help and patience...
>
> "Andreas Mavrogenis" wrote:
> > Geia,
> >
> > Did you droped your indexes ?
> > Andreas
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Geia Andrea :) kai efcharisto!
> > > I am afraid the problem is not that simple. I did as you said and checked my
> > > three
> > > stored procedures, none had anything in common on line #50.
> > > One had a print cmd,
> > > the other an exec cmd and the third
> > > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > > table etc.. So I am still looking for pointers of how to take my
> > > troubleshooting further.
> > > Thank you for your answer...
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Alexandre geia ,
> > > >
> > > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > > your code regarding this line and try to find any loops or any other issue.
> > > >
> > > > Also you can delete and create your indexes
> > > >
> > > > Let me know if you need anything else
> > > > Andreas
> > > >
> > > >
> > > > "Alexander Simeonidis" wrote:
> > > >
> > > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > > and 3605. I am trying to follow the steps of this article in order to
> > > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > > article:
> > > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > > --
> > > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:16.19 spid4 --
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 --
> > > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > --
> > > > >
> > > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > > further... So this is as far as I get. Could someone please give me some
> > > > > hints and pointers of what I need to do next'
> > > > >
> > > > > Thanks on forehand!!
> > > > >
> > > > >
> > > > > --
> > > > > Alex
> > > > > --
> > > > > Computer Science Student|||See that article also,
http://support.microsoft.com/default.aspx?scid=kb;en-us;821537
CU
Andreas
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student
I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
scheduled tasks and perhaps 20 databases. I have read a couple of articles
regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
and 3605. I am trying to follow the steps of this article in order to
pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
Furthermore I have used the sqldiag utility as well as profiler and gotten
the outputfile sqldiag.txt and I am looking at it with the help of the
article:
Here are two examples of deadlocks from the sqldiag.txt:
--
2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
2005-03-30 11:29:16.19 spid4 Target Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Wait-for graph
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:1
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Wait List:
2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
Ref:1 Life:00000000 SPID:90 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
move_vcpu_dtl_from_spots @.days2copy = '1'
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:2
2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 1::
2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 Node:3
2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:16.19 spid4 Grant List 0::
2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:16.19 spid4 Requested By:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
2005-03-30 11:29:16.19 spid4
2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
was found.
2005-03-30 11:29:16.19 spid4 --
2005-03-30 11:29:21.19 spid4 --
2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
2005-03-30 11:29:21.19 spid4 Target Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
verifying cycle
2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4
Deadlock encountered ... Printing deadlock information
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Wait-for graph
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:1
2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 1::
2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:88 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_ssnc_dtl
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 Node:2
2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
Mode: X Flags: 0x2
2005-03-30 11:29:21.19 spid4 Grant List 0::
2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:86 ECID:0
2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
Line #: 50
2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
import_pdc_sweden
2005-03-30 11:29:21.19 spid4 Requested By:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
2005-03-30 11:29:21.19 spid4
2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
was found.
2005-03-30 11:29:21.19 spid4
--
Now as I understand it there should also be a value named "Key XXXX"
somewhere after the "Grant List" but I can not find it. I guess , correct me
if I am wrong, I need that value in order to take the trouble shooting
further... So this is as far as I get. Could someone please give me some
hints and pointers of what I need to do next'
Thanks on forehand!!
--
Alex
--
Computer Science StudentAlexandre geia ,
I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
your code regarding this line and try to find any loops or any other issue.
Also you can delete and create your indexes
Let me know if you need anything else
Andreas
"Alexander Simeonidis" wrote:
> Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> scheduled tasks and perhaps 20 databases. I have read a couple of articles
> regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> and 3605. I am trying to follow the steps of this article in order to
> pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> Furthermore I have used the sqldiag utility as well as profiler and gotten
> the outputfile sqldiag.txt and I am looking at it with the help of the
> article:
> Here are two examples of deadlocks from the sqldiag.txt:
> --
> 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Wait-for graph
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:1
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Wait List:
> 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> Ref:1 Life:00000000 SPID:90 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> move_vcpu_dtl_from_spots @.days2copy = '1'
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:2
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 1::
> 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 Node:3
> 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:16.19 spid4 Grant List 0::
> 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:16.19 spid4 Requested By:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> 2005-03-30 11:29:16.19 spid4
> 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> was found.
> 2005-03-30 11:29:16.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 --
> 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> verifying cycle
> 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4
> Deadlock encountered ... Printing deadlock information
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Wait-for graph
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:1
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 1::
> 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:88 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_ssnc_dtl
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 Node:2
> 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> Mode: X Flags: 0x2
> 2005-03-30 11:29:21.19 spid4 Grant List 0::
> 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:86 ECID:0
> 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> Line #: 50
> 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> import_pdc_sweden
> 2005-03-30 11:29:21.19 spid4 Requested By:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> 2005-03-30 11:29:21.19 spid4
> 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> was found.
> 2005-03-30 11:29:21.19 spid4
> --
> Now as I understand it there should also be a value named "Key XXXX"
> somewhere after the "Grant List" but I can not find it. I guess , correct me
> if I am wrong, I need that value in order to take the trouble shooting
> further... So this is as far as I get. Could someone please give me some
> hints and pointers of what I need to do next'
> Thanks on forehand!!
>
> --
> Alex
> --
> Computer Science Student|||Geia Andrea :) kai efcharisto!
I am afraid the problem is not that simple. I did as you said and checked my
three
stored procedures, none had anything in common on line #50.
One had a print cmd,
the other an exec cmd and the third
an if stmnt. And these cmds had nothing in common i.e writing to the same
table etc.. So I am still looking for pointers of how to take my
troubleshooting further.
Thank you for your answer...
"Andreas Mavrogenis" wrote:
> Alexandre geia ,
> I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> your code regarding this line and try to find any loops or any other issue.
> Also you can delete and create your indexes
> Let me know if you need anything else
> Andreas
>
> "Alexander Simeonidis" wrote:
> > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > and 3605. I am trying to follow the steps of this article in order to
> > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > the outputfile sqldiag.txt and I am looking at it with the help of the
> > article:
> > Here are two examples of deadlocks from the sqldiag.txt:
> > --
> > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> >
> > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:1
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Wait List:
> > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > Ref:1 Life:00000000 SPID:90 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > move_vcpu_dtl_from_spots @.days2copy = '1'
> >
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:2
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 Node:3
> > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:16.19 spid4 Requested By:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > 2005-03-30 11:29:16.19 spid4
> > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > was found.
> > 2005-03-30 11:29:16.19 spid4 --
> >
> > 2005-03-30 11:29:21.19 spid4 --
> > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> >
> > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > verifying cycle
> > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4
> > Deadlock encountered ... Printing deadlock information
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:1
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:88 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_ssnc_dtl
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 Node:2
> > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > Mode: X Flags: 0x2
> > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > Ref:0 Life:02000000 SPID:86 ECID:0
> > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > Line #: 50
> > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > import_pdc_sweden
> > 2005-03-30 11:29:21.19 spid4 Requested By:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > 2005-03-30 11:29:21.19 spid4
> > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > was found.
> > 2005-03-30 11:29:21.19 spid4
> > --
> >
> > Now as I understand it there should also be a value named "Key XXXX"
> > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > if I am wrong, I need that value in order to take the trouble shooting
> > further... So this is as far as I get. Could someone please give me some
> > hints and pointers of what I need to do next'
> >
> > Thanks on forehand!!
> >
> >
> > --
> > Alex
> > --
> > Computer Science Student|||Geia,
Did you droped your indexes ?
Andreas
"Alexander Simeonidis" wrote:
> Geia Andrea :) kai efcharisto!
> I am afraid the problem is not that simple. I did as you said and checked my
> three
> stored procedures, none had anything in common on line #50.
> One had a print cmd,
> the other an exec cmd and the third
> an if stmnt. And these cmds had nothing in common i.e writing to the same
> table etc.. So I am still looking for pointers of how to take my
> troubleshooting further.
> Thank you for your answer...
> "Andreas Mavrogenis" wrote:
> > Alexandre geia ,
> >
> > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > your code regarding this line and try to find any loops or any other issue.
> >
> > Also you can delete and create your indexes
> >
> > Let me know if you need anything else
> > Andreas
> >
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > and 3605. I am trying to follow the steps of this article in order to
> > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > article:
> > > Here are two examples of deadlocks from the sqldiag.txt:
> > > --
> > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > >
> > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > >
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > 2005-03-30 11:29:16.19 spid4
> > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:16.19 spid4 --
> > >
> > > 2005-03-30 11:29:21.19 spid4 --
> > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > >
> > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > verifying cycle
> > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4
> > > Deadlock encountered ... Printing deadlock information
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_ssnc_dtl
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > Mode: X Flags: 0x2
> > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > Line #: 50
> > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > import_pdc_sweden
> > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > 2005-03-30 11:29:21.19 spid4
> > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > was found.
> > > 2005-03-30 11:29:21.19 spid4
> > > --
> > >
> > > Now as I understand it there should also be a value named "Key XXXX"
> > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > if I am wrong, I need that value in order to take the trouble shooting
> > > further... So this is as far as I get. Could someone please give me some
> > > hints and pointers of what I need to do next'
> > >
> > > Thanks on forehand!!
> > >
> > >
> > > --
> > > Alex
> > > --
> > > Computer Science Student|||Hi, do you mean dropping all my indexes on the tables involved?
I can not drop the indexes on my tables because the only index I have is the
PK.
To explain the flow of how my stored procedures work:
[Raw data table] -> [tmp-table ] -> [data-table]
The raw data tables contains no keys whatsoever and they are deleted after
each moving of data to the tmp tables. The tmp tables are also deleted after
each move of the data to the data tables. And they have no indexes or keys
either.
So the only table that have indexes are the datatables and for them the
index is the PK. But the PK consists of 5-6 different columns and the amount
of data in the table is about half of million rows.
The problems with deadlocks most often arises when I try to move data from
the raw data tables to the tmp tables.
Thanks a lot for your help and patience...
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student|||Alexandre gria kai pali,
Look, try to delete * from tmp before raw data insertion. Try the insertion
with insert statement rather that the update. I saw the update statement in
the error that you posted.
Also, try to put indexes on you tables if your app allows
Try that and let me know,
Andreas
"Alexander Simeonidis" wrote:
> Hi, do you mean dropping all my indexes on the tables involved?
> I can not drop the indexes on my tables because the only index I have is the
> PK.
> To explain the flow of how my stored procedures work:
> [Raw data table] -> [tmp-table ] -> [data-table]
> The raw data tables contains no keys whatsoever and they are deleted after
> each moving of data to the tmp tables. The tmp tables are also deleted after
> each move of the data to the data tables. And they have no indexes or keys
> either.
> So the only table that have indexes are the datatables and for them the
> index is the PK. But the PK consists of 5-6 different columns and the amount
> of data in the table is about half of million rows.
> The problems with deadlocks most often arises when I try to move data from
> the raw data tables to the tmp tables.
> Thanks a lot for your help and patience...
>
> "Andreas Mavrogenis" wrote:
> > Geia,
> >
> > Did you droped your indexes ?
> > Andreas
> >
> > "Alexander Simeonidis" wrote:
> >
> > > Geia Andrea :) kai efcharisto!
> > > I am afraid the problem is not that simple. I did as you said and checked my
> > > three
> > > stored procedures, none had anything in common on line #50.
> > > One had a print cmd,
> > > the other an exec cmd and the third
> > > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > > table etc.. So I am still looking for pointers of how to take my
> > > troubleshooting further.
> > > Thank you for your answer...
> > >
> > > "Andreas Mavrogenis" wrote:
> > >
> > > > Alexandre geia ,
> > > >
> > > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > > your code regarding this line and try to find any loops or any other issue.
> > > >
> > > > Also you can delete and create your indexes
> > > >
> > > > Let me know if you need anything else
> > > > Andreas
> > > >
> > > >
> > > > "Alexander Simeonidis" wrote:
> > > >
> > > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > > and 3605. I am trying to follow the steps of this article in order to
> > > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > > article:
> > > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > > --
> > > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > > >
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > > 2005-03-30 11:29:16.19 spid4
> > > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:16.19 spid4 --
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 --
> > > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > > >
> > > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > > verifying cycle
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > Deadlock encountered ... Printing deadlock information
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_ssnc_dtl
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > > Mode: X Flags: 0x2
> > > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > > Line #: 50
> > > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > > import_pdc_sweden
> > > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > > was found.
> > > > > 2005-03-30 11:29:21.19 spid4
> > > > > --
> > > > >
> > > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > > further... So this is as far as I get. Could someone please give me some
> > > > > hints and pointers of what I need to do next'
> > > > >
> > > > > Thanks on forehand!!
> > > > >
> > > > >
> > > > > --
> > > > > Alex
> > > > > --
> > > > > Computer Science Student|||See that article also,
http://support.microsoft.com/default.aspx?scid=kb;en-us;821537
CU
Andreas
"Andreas Mavrogenis" wrote:
> Geia,
> Did you droped your indexes ?
> Andreas
> "Alexander Simeonidis" wrote:
> > Geia Andrea :) kai efcharisto!
> > I am afraid the problem is not that simple. I did as you said and checked my
> > three
> > stored procedures, none had anything in common on line #50.
> > One had a print cmd,
> > the other an exec cmd and the third
> > an if stmnt. And these cmds had nothing in common i.e writing to the same
> > table etc.. So I am still looking for pointers of how to take my
> > troubleshooting further.
> > Thank you for your answer...
> >
> > "Andreas Mavrogenis" wrote:
> >
> > > Alexandre geia ,
> > >
> > > I saw that the SPID's 90,88,86 have an UPDATE statement in Line 50# Check
> > > your code regarding this line and try to find any loops or any other issue.
> > >
> > > Also you can delete and create your indexes
> > >
> > > Let me know if you need anything else
> > > Andreas
> > >
> > >
> > > "Alexander Simeonidis" wrote:
> > >
> > > > Hi all, I have a rather annoying problem finding out what causes my deadlocks.
> > > > I am running SQL Server 2000 8.0 with SP3. The server contains several (30)
> > > > scheduled tasks and perhaps 20 databases. I have read a couple of articles
> > > > regarding deadlocks and I am using (have switched on) Traceflags 1204, 1205
> > > > and 3605. I am trying to follow the steps of this article in order to
> > > > pinpoint what causes the deadlocks: http://support.microsoft.com/?kbid=832524
> > > > Furthermore I have used the sqldiag utility as well as profiler and gotten
> > > > the outputfile sqldiag.txt and I am looking at it with the help of the
> > > > article:
> > > > Here are two examples of deadlocks from the sqldiag.txt:
> > > > --
> > > > 2005-03-30 11:29:16.19 spid4 Starting deadlock search 328939
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:16.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4 Node:3 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:1
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Wait List:
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x65589780 Mode: U Flg:0x0
> > > > Ref:1 Life:00000000 SPID:90 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 90 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event:
> > > > move_vcpu_dtl_from_spots @.days2copy = '1'
> > > >
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:2
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2073567:33 CleanCnt:2
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 Node:3
> > > > 2005-03-30 11:29:16.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:16.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:16.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:16.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:16.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:16.19 spid4 Requested By:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:16.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:16.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:90 ECID:0 Ec:(0x79EC5538) Value:0x65589780 Cost:(0/0)
> > > > 2005-03-30 11:29:16.19 spid4
> > > > 2005-03-30 11:29:16.19 spid4 End deadlock search 328939 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:16.19 spid4 --
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 --
> > > > 2005-03-30 11:29:21.19 spid4 Starting deadlock search 328942
> > > >
> > > > 2005-03-30 11:29:21.19 spid4 Target Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Deadlock cycle was encountered ...
> > > > verifying cycle
> > > > 2005-03-30 11:29:21.19 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode:
> > > > U SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4
> > > > Deadlock encountered ... Printing deadlock information
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Wait-for graph
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:1
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2073567:33 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 1::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x2e3c0420 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:88 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 88 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_ssnc_dtl
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:86 ECID:0 Ec:(0x3AD115E8) Value:0x59929840 Cost:(0/429BCA0)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 Node:2
> > > > 2005-03-30 11:29:21.19 spid4 RID: 15:1:2576096:68 CleanCnt:1
> > > > Mode: X Flags: 0x2
> > > > 2005-03-30 11:29:21.19 spid4 Grant List 0::
> > > > 2005-03-30 11:29:21.19 spid4 Owner:0x5969d9a0 Mode: X Flg:0x0
> > > > Ref:0 Life:02000000 SPID:86 ECID:0
> > > > 2005-03-30 11:29:21.19 spid4 SPID: 86 ECID: 0 Statement Type: UPDATE
> > > > Line #: 50
> > > > 2005-03-30 11:29:21.19 spid4 Input Buf: Language Event: exec
> > > > import_pdc_sweden
> > > > 2005-03-30 11:29:21.19 spid4 Requested By:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4 Victim Resource Owner:
> > > > 2005-03-30 11:29:21.19 spid4 ResType:LockOwner Stype:'OR' Mode: U
> > > > SPID:88 ECID:0 Ec:(0x6948B5E8) Value:0x2e3c0340 Cost:(0/F4)
> > > > 2005-03-30 11:29:21.19 spid4
> > > > 2005-03-30 11:29:21.19 spid4 End deadlock search 328942 ... a deadlock
> > > > was found.
> > > > 2005-03-30 11:29:21.19 spid4
> > > > --
> > > >
> > > > Now as I understand it there should also be a value named "Key XXXX"
> > > > somewhere after the "Grant List" but I can not find it. I guess , correct me
> > > > if I am wrong, I need that value in order to take the trouble shooting
> > > > further... So this is as far as I get. Could someone please give me some
> > > > hints and pointers of what I need to do next'
> > > >
> > > > Thanks on forehand!!
> > > >
> > > >
> > > > --
> > > > Alex
> > > > --
> > > > Computer Science Student
Subscribe to:
Posts (Atom)