Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.
Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
sql

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:
> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

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

Deadlock Question

Hi All,

Can multiple updates on one table using single
query generate deadlock ?
For example, at the same time, there are 2 users
run 2 queries as follows :

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Note :
The content of the column "no" on table tab2 :
('A','B','C',...,'X','Y','Z')
The content of the column "no" on table tab3
is like in table tab2, but in different order :
('Z','Y','X',....,'C','B','A')

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> Can multiple updates on one table using single
> query generate deadlock ?
> For example, at the same time, there are 2 users
> run 2 queries as follows :
> User1 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> User2 runs :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab3 on tab1.no = tab3.no
> Note :
> The content of the column "no" on table tab2 :
> ('A','B','C',...,'X','Y','Z')
> The content of the column "no" on table tab3
> is like in table tab2, but in different order :
> ('Z','Y','X',....,'C','B','A')

Tables in a relational database are sets, and data has no order.

But, of course, for the evaluation of a query the physical order may
affect such things as deadlock.

Anyway, I am not going to answer the question directly, because there
is a lot of unknown elements. Is tbl.v a primary key or at least
indexed? What about tbl2.no and tbl3.no? And what exactly is
different order?

CREATE TABLE statements for the tables and INSERT statemetns for the
data may help.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for your reply.

Deadlock can be found easily in several command steps.
But, how can I find it in multiple updates using
only one step of command ?

This question is posted because I do not know
exactly how SQL Server handles my sample query.
And I become worry after reading many deadlock
articles here. Especially deadlock that is caused
by table index.

Below is the description of the tables :

Table tb1 :
- no CHAR(10); no2 CHAR(10); v INT
- Index possibility : only one index, on no or on no2
- no is unique, no2 is unique
- v is not a key.

Table tb2 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Table tb3 :
- x CHAR(10); no CHAR(10)
- Index : on x
- x is not unique, no is unique

Regards,

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Deadlock can be found easily in several command steps.
> But, how can I find it in multiple updates using
> only one step of command ?

Testing deadlocks that may occur from single statements are indeed not
trivial to construct at will.

One possibility is to write a small app - could even be a stored procedure
- that runs the supicious SQL statement all over again in an infinite
loop. If you get a deadlock, you now know that it can happen. If you
don't get a deadlock - well you still don't know, because may the test
was not good enough.

Another approach is to introduce a waitstate somewhere, so that you get
a chance to start a second query window with the competing query. This
is not trivial either. For a simple case, I used this function some
time ago:

create function nisse () returns int as
begin
exec master..xp_cmdshell 'osql -E -n -Q "WAITFOR DELAY ''00:00:20''"'
return 1
end

In your case, at least one your updates should read:

UPDATE tbl
SET col = dbo.nisse() -- Or some expression including dbo.nisse().
...

But of course, this constructs a situation which is not really the same
as the real-world scenario, and the observations may not be transferrable.
(But it seems to me that in this case, they could.)

Since you did not provide CREATE TABLE statements and INSERT statements
with sample data, I was too lazy to actually try this technique with
your example. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||When SQL Server receive query :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no
I expect it follows the procedure like this :
a. Find the rows that will be updated.
b. If they are not found then exit.
c. Try locking the rows found.
d. If locking is successfull then update the
rows and exit.
e. Wait for miliseconds.
f. If query timeout expires then exit.
g. goto c.

Since I do not have information about how SQL Server
handles the query, I usually insert tablock hint in the query :
update tab1 with (tablock) set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

Though by using tablock hint it will lock all rows
in the table (prevent other rows from being updated
by other users) but, I think I should take this way.
It is free from deadlock.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> When SQL Server receive query :
> update tab1 set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> I expect it follows the procedure like this :
> a. Find the rows that will be updated.
> b. If they are not found then exit.
> c. Try locking the rows found.
> d. If locking is successfull then update the
> rows and exit.
> e. Wait for miliseconds.
> f. If query timeout expires then exit.
> g. goto c.

I have to admit that I don't fully master the internal procedure, but
I would expect it to be somewhat different. I would expect that already
when SQL Server finds the matching rows that it applies at least
shared locks, possible also intent locks. Once a row is found to
qualify, I would suppose SQL Server puts an exclusive lock on a
row.

You mention "query timeout". I suppose you mean lock timeout, which you
control with SET LOCK_TIMEOUT. Query timeout is a client (mis)feature,
and does not affect locking.

Going back to your original post, you had these two statements:

User1 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab2 on tab1.no = tab2.no

User2 runs :
update tab1 set tab1.v = tab1.v + 1
from tab1 inner join tab3 on tab1.no = tab3.no

Working from my assumptions above - which I like to stress are nothing
but assumptions, you could get a deadlock here, if the statistics on
the table are such that the optimizer chooses different query plans.
For instance, for the first query, the optimizer decides to scan tab1
and then perform a nested join with tab2. But for the second query,
the optimizer scans tab3, and performs a nested join with index seek
on tab1. If the two queries start at the same time, they will find
matching rows in tab1 in different order, and therefor they will
deadlock.

> Since I do not have information about how SQL Server
> handles the query, I usually insert tablock hint in the query :
> update tab1 with (tablock) set tab1.v = tab1.v + 1
> from tab1 inner join tab2 on tab1.no = tab2.no
> Though by using tablock hint it will lock all rows
> in the table (prevent other rows from being updated
> by other users) but, I think I should take this way.
> It is free from deadlock.

Yes, this should be deadlock free. But there are of course other issues
with tablock. If most updates are on single rows, tablock might be too
heavy-handed and lead to concurrency issues.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for your reply,

Yes, your assumption is somewhat different with what I expect. I expect
: if there are 10 matching rows and SQL Server can lock only 9 rows,
then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
rows again.
If my expectation is true, then the query is deadlock free and I will
avoid using tablock hint.

My last question is where I can get information that tell us your
assumption or my expectation is true ?

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> Yes, your assumption is somewhat different with what I expect. I expect
>: if there are 10 matching rows and SQL Server can lock only 9 rows,
> then : SQL Server unlock 9 rows, wait for a moment, and try locking 10
> rows again.
> If my expectation is true, then the query is deadlock free and I will
> avoid using tablock hint.
> My last question is where I can get information that tell us your
> assumption or my expectation is true ?

So much I can tell with confidence, that SQL Server never releases locks
because it cannot get all locks it needs to carry out a task. While such
a strategy could reduce deadlock, it could have other nasty effects like
lock starvation. A process that needs to access many rows in a busy system
would never get all locks.

Also, I believe that the work order is something like: lock one row,
update that row, lock next row and so on. In this case, it is of course
even less possible to release rows.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog,

Thanks for all your replies.

Regards,
Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deadlock Issues

You need to post detail about your queries. Show DDL, sample data, and the two queries that are deadlocking, and we can help you re-write them so that they don't deadlock. See the following if you need help providing DDL and sample data: http://www.aspfaq.com/etiquette.asp?id=5006 -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <clubberx@.discussions.microsoft.com> wrote in message news:41ed2763-84cf-4fa2-94d9-6c611f19ff0a@.discussions.microsoft.com...Hi,We have a site setup using MsSQL 2000 SP4, Coldfusion and IIS. We are seeing constant deadlock issues which are slowing the site and resulting in errors.As well as the usual restarts and reboots - steps taken so far have included:*Configured Ms SQL to only use one processor at a time - as per http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=91297 *Turned off Coldfusion global client variable updates as per http://www.houseoffusion.com/cf_lists/messages.cfm/forumid:4/Threadid:41512#213650*Turned on MsSQL trace for deadlock errorsThe Errors are as follows:Coldfusion:Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.The error occurred on line 406.MsSQL Trace:Deadlock encountered .... Printing deadlock information2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Wait-for graph2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Node:12005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-10-11 10:39:10.53 spid4 Grant List 1::2005-10-11 10:39:10.53 spid4 Grant List 2::2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:02005-10-11 10:39:10.53 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 12005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_execute;12005-10-11 10:39:10.53 spid4 Grant List 3::2005-10-11 10:39:10.53 spid4 Requested By: 2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x42D01500) Value:0x42bfbaa0 Cost:(0/0)2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Node:22005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-10-11 10:39:10.53 spid4 Grant List 1::2005-10-11 10:39:10.53 spid4 Owner:0x42bf6540 Mode: IS Flg:0x0 Ref:1 Life:02000000 SPID:54 ECID:02005-10-11 10:39:10.53 spid4 SPID: 54 ECID: 0 Statement Type: SELECT Line #: 12005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_prepexec;12005-10-11 10:39:10.53 spid4 Grant List 2::2005-10-11 10:39:10.53 spid4 Grant List 3::2005-10-11 10:39:10.53 spid4 Requested By: 2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x445C9500) Value:0x42c296e0 Cost:(0/0)2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Node:32005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-10-11 10:39:10.53 spid4 Grant List 1::2005-10-11 10:39:10.53 spid4 Grant List 2::2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:02005-10-11 10:39:10.53 spid4 Grant List 3::2005-10-11 10:39:10.53 spid4 Requested By: 2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)2005-10-11 10:39:10.53 spid4 2005-10-11 10:39:10.53 spid4 Node:62005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x02005-10-11 10:39:10.53 spid4 Grant List 1::2005-10-11 10:39:10.53 spid4 Grant List 2::2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:02005-10-11 10:39:10.53 spid4 Grant List 3::2005-10-11 10:39:10.53 spid4 Requested By: 2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x42EC1500) Value:0x42c29760 Cost:(0/0)2005-10-11 10:39:10.53 spid4 Victim Resource Owner:2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)2005-10-11 10:39:13.73 spid4 Any help greatly Appreciated,Regards,Chris.Hi,
We have a site setup using MsSQL 2000 SP4, Coldfusion and IIS. We are seeing constant deadlock issues which are slowing the site and resulting in errors.
As well as the usual restarts and reboots - steps taken so far have included:
*Configured Ms SQL to only use one processor at a time - as per
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=91297
*Turned off Coldfusion global client variable updates as per
http://www.houseoffusion.com/cf_lists/messages.cfm/forumid:4/Threadid:41512#213650
*Turned on MsSQL trace for deadlock errors
The Errors are as follows:
Coldfusion:
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The error occurred on line 406.
MsSQL Trace:
Deadlock encountered .... Printing deadlock information
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Wait-for graph
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:1
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 1
2005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_execute;1
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x42D01500) Value:0x42bfbaa0 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:2
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Owner:0x42bf6540 Mode: IS Flg:0x0 Ref:1 Life:02000000 SPID:54 ECID:0
2005-10-11 10:39:10.53 spid4 SPID: 54 ECID: 0 Statement Type: SELECT Line #: 1
2005-10-11 10:39:10.53 spid4 Input Buf: RPC Event: sp_prepexec;1
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x445C9500) Value:0x42c296e0 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:3
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)
2005-10-11 10:39:10.53 spid4
2005-10-11 10:39:10.53 spid4 Node:6
2005-10-11 10:39:10.53 spid4 TAB: 5:462624691 [] CleanCnt:5 Mode: SIX Flags: 0x0
2005-10-11 10:39:10.53 spid4 Grant List 1::
2005-10-11 10:39:10.53 spid4 Grant List 2::
2005-10-11 10:39:10.53 spid4 Owner:0x42c29800 Mode: SIX Flg:0x0 Ref:3 Life:02000000 SPID:57 ECID:0
2005-10-11 10:39:10.53 spid4 Grant List 3::
2005-10-11 10:39:10.53 spid4 Requested By:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x42EC1500) Value:0x42c29760 Cost:(0/0)
2005-10-11 10:39:10.53 spid4 Victim Resource Owner:
2005-10-11 10:39:10.53 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x445D9500) Value:0x42bf6580 Cost:(0/0)
2005-10-11 10:39:13.73 spid4
Any help greatly Appreciated,
Regards,
Chris.

|||You might want to use Profiler and evaluate the Lock:Escalation event. I'm betting you'll find that one of your queries is trying to escalate to a table lock, which is causing the deadlock. Just a hunch... Often these kinds of issues can crop up if someone has changed an index and removed a column you need for the query to be able to use a lower-granularity lock, or if some statistics are stale. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <clubberx@.discussions.microsoft.com> wrote in message news:0f0e4727-5cb4-45c3-9fc2-8bce55e77036@.discussions.microsoft.com... Hi - Thanks for your response.>You need to post detail about your queries. Show DDL, sample data, and the two queries that >are deadlocking, and we can help you re-write them so that they don't deadlock.I am pretty sure it is not the queries - this is old code that we have used before many times with minimal changes - also, last night I tested this on one of our development servers with the same software platform (Coldfusion 7, MsSQL 2000 SP4) and we saw no deadlocks at all. Regards,Chris. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net|||

Hi - Thanks for your response.
>You need to post detail about your queries. Show DDL, sample data, and the two queries that >are deadlocking, and we can help you re-write them so that they don't deadlock.
I am pretty sure it is not the queries - this is old code that we have used before many times with minimal changes - also, last night I tested this on one of our development servers with the same software platform (Coldfusion 7, MsSQL 2000 SP4) and we saw no deadlocks at all.

Regards,
Chris.

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

|||Hi,

I have enabled profiler for

Lock:Cancel
Lock:Deadlock
Lock:Deadlock Chain
Lock:Escalation
Lock:Timeout

All I am seeing is 'Lock:Deadlock' and 'Lock:Deadlock Chain' Events,

regards,

Chris.

|||

Looking at you deadlock infomation, looks like all sessions are getting lock at the table level with session 57 having SIX lock on the table and wanting to upgrade it to X while session 54 has IS lock and waiting to acquire S lock.

Since you are not seeing any lock escalation, I think the SQL Server is choosing too coarse (in your case, a table) a locking granularity. SQL Server has some heuristic to determine the locking granularity. I believe it also depends on some statistical information on the table. I will recommend running update statistics to see if it helps.

Thanks

DBxtra Data explorer

DBxtra Data explorer
DBxtra version 1 is ready.
You can connect to unlimited MS Access, MS SQL Server, Paradox, PDF and Excel tables and queries.
Get the sense out of your data!1
1. Connect to your data
2. Explore your data
3. Design and deploy your reports
4. Export your data
5. Send your data by E-mail
6. Schedule reports and alerts
Try the free Download!
http://www.dbxtra.comADVERTISING

I think i am going to post ads for commercial items that i believe in

"hey you got you chocolate in my peanut butter"
"You got your peanut butter on my chocolate"
"two great tastes that taste great together"
reeses peanut butter cups :eek:|||Hey...whats with the dead chick on the couch?

http://www.dbxtra.com/images/p1.jpg|||I've got another idea for a great commercial venture.

Gasoline powered "adult" toys!

What a marketing opportunity! We can all get rich, very quickly... Live lives of leisure, travel the world, sample the best of everything. We can be rich I tell you, just rich if we all work together to tap this as yet unexplored marketing opportunity!!!

On a (very slightly) more serious note, will an admin please move this thread to the Marketplace (http://www.dbforums.com/f188) where it belongs?

-PatP|||But if will have to marketed for outdoor use only...

Maybe the chick on the couch was using one of your products and passed out from the exhaust...|||pat i hate to tell you this but we already have gasoline powered adult toys.

porsche
ferrari
jaguar
lamborghini
etc.|||pat i hate to tell you this but we already have gasoline powered adult toys... and look at the money that people will spend on them! I tell you, there is a fortune to be made here if we can just find the right products and advertising medium!!!

Somebody, PLEASE move this to the MarketPlace (http://www.dbforums.com/f188)!

-PatP