Deadlock Issue 1

[mortonsa]

Hello All,

I am trying to run the query below in query analyzer and I keep getting the following error message:

Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID 53) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I have been all through Books on line and nothing I have tried seems to work. If I kill the process, I can run the query successfully immediately after that... but after about 1 minute I get the deadlock message again.

Any help would be appreciated!

SELECT
(tu.user_name_first + ' ' + tu.user_name_last) as RepName,
fv.key_eautorep as RepID,
sum(case when cs.isFleet = 1 then 1 else 0 end) as eFleetCount,
sum(case when cs.isFleet = 0 then 1 else 0 end) as eAutoCount
FROM tbl_users as tu, fn_vehicles as fv, claim_status as cs, fn_acord as fn
WHERE tu.user_id = fv.key_eautorep
AND fv.transid = cs.transid
AND cs.transid = fn.transid
AND tu.client_id = 161
AND tu.inactive = 0
AND fv.repair_shop_id <> 1885
AND fv.key_eautorep NOT IN (128,162,229)
AND fv.subprocid NOT IN (14,30)
AND (fv.repair_shop_id > 0 or cs.clientid = 566)
GROUP BY tu.user_name_first, tu.user_name_last, fv.key_eautorep
ORDER BY RepName

 

[MDXer]

if your just doing a select you could try using nolock

&quot;Shoot Me! Shoot Me NOW!!!&quot;
- Daffy Duck

 

[mortonsa]

I tried using NOLOCK (see syntax) and I am still getting the error message.

SELECT
(tu.user_name_first + ' ' + tu.user_name_last) as RepName,
fv.key_eautorep as RepID,
sum(case when cs.isFleet = 1 then 1 else 0 end) as eFleetCount,
sum(case when cs.isFleet = 0 then 1 else 0 end) as eAutoCount
FROM tbl_users as tu (NOLOCK), fn_vehicles as fv (NOLOCK),
claim_status as cs (NOLOCK), fn_acord as fn (NOLOCK)
WHERE tu.user_id = fv.key_eautorep
AND fv.transid = cs.transid
AND cs.transid = fn.transid
AND tu.client_id = 161
AND tu.inactive = 0
AND fv.repair_shop_id <> 1885
AND fv.key_eautorep NOT IN (128,162,229)
AND fv.subprocid NOT IN (14,30)
AND (fv.repair_shop_id > 0 or cs.clientid = 566)
GROUP BY tu.user_name_first, tu.user_name_last, fv.key_eautorep
ORDER BY RepName

 

[mortonsa]

Does anyone have any other suggestions?

 

[hmckillop]

use sp_lock in another query analyser window to determine what resources are being held and by who. From the sp_lock you should be able to tell what process id is holding the locks on the resources that your query needs.

From this you should be able to determine the offending process. (is anyone else using this database etc.)

Also noted in your query a few pointers, use the INNER JOIN syntax instead of WHERE tablea.colid = tableb.colid to join tables, ensure you have an index on foreign keys, any frequently used search criteria should also have indices etc etc etc.

 

[mortonsa]

I rewrote the query so now it looks like the syntax below. I verified that I have indexes on my tables as well and I am still getting the same message. I ran sp_lock in a seperate window but nothing is jumping out at me as being wrong. Can anyone please look at my output from sp_lock and let me know if they see anything? I have pasted it below the query (Note: There was no data in the resource column).

THANKS!

SELECT
(tu.user_name_first + ' ' + tu.user_name_last) as RepName,
fv.key_eautorep as RepID,
sum(case when cs.isFleet = 1 then 1 else 0 end) as eFleetCount,
sum(case when cs.isFleet = 0 then 1 else 0 end) as eAutoCount
FROM tbl_users as tu (NOLOCK)
INNER JOIN fn_vehicles as fv (NOLOCK)
ON (fv.key_eautorep = tu.user_id)
INNER JOIN claim_status as cs (NOLOCK)
ON (cs.transid = fv.transid)
INNER JOIN fn_acord as fn (NOLOCK)
ON (fn.transid = cs.transid)
WHERE tu.client_id = 161
AND tu.inactive = 0
AND fv.repair_shop_id <> 1885
AND fv.key_eautorep NOT IN (128,162,229)
AND fv.subprocid NOT IN (14,30)
AND (fv.repair_shop_id > 0 or cs.clientid = 566)
GROUP BY tu.user_name_first, tu.user_name_last, fv.key_eautorep
ORDER BY RepName

spid dbid ObjId IndId Type Resource Mode Status
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 9 0 0 DB S GRANT
54 7 0 0 DB S GRANT
55 7 0 0 DB S GRANT
56 7 0 0 DB S GRANT
57 7 0 0 DB S GRANT
58 7 0 0 DB S GRANT
60 7 0 0 DB S GRANT
61 7 0 0 DB S GRANT
61 1 85575343 0 TAB IS GRANT
63 7 0 0 DB S GRANT
64 9 0 0 DB S GRANT
65 7 0 0 DB S GRANT
74 7 0 0 DB S GRANT

 

[hmckillop]

Have you run any trace flags on your machine?
You can check this using
DBCC TraceStatus(-1)
GO


The only other thing is that the indices may be causing the deadlocking, if any them are at cross purposes, i.e. use the same field but in a different order, and then the joins are being used to match up the fields.

When you have indexes on a table, SQL Server may only lock some of the indexes associated with a particular row. In effect you get "column Level" locks instead of row-level locks. This can lead to problems if your application tries to access/update the same row twice.
Though as you are using table hints (and the default isolation in query analyser is Read Committed) only shared locks should be taken out.

Can you script all the indices on the tables involved?

 

[mortonsa]

Thank you hmckillop! Your feedback helped me to understand better why this was happening.

After reading your post I ended up copying my production tables that this query was using over to my development server (I was not having the same problem on production). That fixed the issue. So, I am assuming that some changes were made to the indexes in my development environment.