Re: Deadlocks on Update

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nikhil Patel (nikhil0100_at_aol.com)
Date: 05/26/04 

Date: Wed, 26 May 2004 11:38:42 -0400

Hi,
   Try changing the following line
        Inner Join ShopDB.dbo.AttributeValues aval On
    To
        Inner Join ShopDB.dbo.AttributeValues aval WITH(ROWLOCK) On

If you have other queries, you may have to change them. Use WITH(NOLOCK)
hint if you are not updating the table. If you are updating the table use
WITH(ROWLOCK) hint. This way your update query will lock only one row at any
time instead of locking the entire table.

"Vidya" <anonymous@discussions.microsoft.com> wrote in message
news:CDDFF1C9-BE9F-4A5F-9E97-1FC8B547612D@microsoft.com...
> I have a multithreaded service executing a stored procedure which updates
the table shown below. SQL Server repeatedly runs into deadlocks on the
Update statement given below. I have included the trace error log messages
as well.
>
> Is the Inner Join in the Update query causing locking issues? Any insight
on how to mitigate this deadlock issue would be very helpful.
>
> Thanks in advance for your help.
>
> Thanks,
> Vidya
>
> AttributeValues table -
> Name Type Length Nullable
> avalId int 4 No
> aclaID int 4 No
> avalInt int 4 Yes
> avalDecimal decimal 9,3 Yes
> avalMin decimal 9,3 Yes
> avalMax decimal 9,3 yes
> avalString nvarchar 50 Yes
>
> Update aval
> Set
> aval.avalString = tmpAtt.stringValue
> From @tmpAttributes tmpAtt
> Inner Join ShopDB.dbo.AttributeValues aval On
> (aval.aclaId =
tmpAtt.aclaId
> And
Coalesce(aval.avalInt,-999) = Coalesce(tmpAtt.intValue,-99)
> And
Coalesce(aval.avalDecimal,-99999.99) =
Coalesce(tmpAtt.decimalValue,-99999.99)
> And tmpAtt.aclaType In
('I','D')--update string value only for Int and Decimal value types
> And (aval.avalString
<> tmpAtt.stringValue)
> )
>
> The following is the information from SQL Server Error Log -
>
> Date Source Message
> 2004-05-24 19:22:37.30 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:68 ECID:0 Ec:(0x1cb2f5a0) Value:0x194
> 2004-05-24 19:22:37.30 spid4 Victim Resource Owner:
> 2004-05-24 19:22:37.30 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:68 ECID:0 Ec:(0x1cb2f5a0) Value:0x194
> 2004-05-24 19:22:37.30 spid4 Requested By:
> 2004-05-24 19:22:37.30 spid4 Input Buf: Language Event: exec
ShopDB.dbo.usp_GetIdDataForIndexer @kwrdList=N
> 2004-05-24 19:22:37.30 spid4 SPID: 66 ECID: 0 Statement Type:
UPDATE Line #: 89
> 2004-05-24 19:22:37.30 spid4 Owner:0x1942ee80 Mode: U
Flg:0x0 Ref:0 Life:00000001 SPID:66 ECID:0
> 2004-05-24 19:22:37.30 spid4 Grant List::
> 2004-05-24 19:22:37.30 spid4 KEY: 17:715149593:7 (5700dbe939b3)
CleanCnt:1 Mode: U Flags: 0x0
> 2004-05-24 19:22:37.30 spid4 Node:2
> 2004-05-24 19:22:37.30 spid4
> 2004-05-24 19:22:37.30 spid4 ResType:LockOwner Stype:'OR' Mode: U
SPID:66 ECID:0 Ec:(0x1bf615a0) Value:0x194
> 2004-05-24 19:22:37.30 spid4 Requested By:
> 2004-05-24 19:22:37.30 spid4 Input Buf: Language Event: exec
ShopDB.dbo.usp_GetIdDataForIndexer @kwrdList=N
> 2004-05-24 19:22:37.30 spid4 SPID: 68 ECID: 0 Statement Type:
UPDATE Line #: 89
> 2004-05-24 19:22:37.30 spid4 Owner:0x194356a0 Mode: U
Flg:0x0 Ref:0 Life:00000001 SPID:68 ECID:0
> 2004-05-24 19:22:37.30 spid4 Grant List::
> 2004-05-24 19:22:37.30 spid4 KEY: 17:715149593:7 (07008281220c)
CleanCnt:1 Mode: U Flags: 0x0
> 2004-05-24 19:22:37.30 spid4 Node:1
> 2004-05-24 19:22:37.30 spid4
> 2004-05-24 19:22:37.30 spid4 Wait-for graph
>
>

Relevant Pages

  • Re: New to c++.net (need help)
    ... > using namespace std; ... > int main ... Here's a hint for when ... your program is behaving other than how you expect, ...
    (alt.comp.lang.learn.c-cpp)
  • Re: Updates from a DataGridView
    ... It seems that you do something terrible wrong, a dataadapter is updating only updating ). ... Those rows have all a rowstate which should be have not a value unchanged, it seems to me that you are doing something where you ignore the rowstate. ... @Status_ID int, ... @UserID int, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Bit fiddling
    ... int n; should be unsigned int n;, or a better solution would have ... Hint #2: Is the value of ~3 even or odd? ... divisibility-by-three test would make a useful improvement. ... On the six-year-old machine sitting in front of me at the ...
    (comp.lang.c)
  • Re: Keylocks and updates
    ... updating a single row so many times per second? ... page hit counter? ... you owner qualify the sp when you call it. ... all int. ...
    (microsoft.public.sqlserver.programming)
  • Re: Reading some pieces of a file
    ... ron wrote: ... find how to open and read a file (hint: ... read the answer and compare ... int main ...
    (alt.comp.lang.learn.c-cpp)