February 18, 2008 at 8:42 am
From research in interpreting this log, it looks like one node has an exclusive on key_generator table and the other node has an update lock on the same table. How can this happen? I thought these were mutually exclusive? Or, do I just not understand what is being shown here? Any help is appreciated!!
2/13/2008 10:27:41,spid4,Unknown,Wait-for graph
02/13/2008 10:27:41,spid4,Unknown,
02/13/2008 10:27:41,spid4,Unknown,Node:1
02/13/2008 10:27:41,spid4,Unknown,RID: 8:1:61633:7 CleanCnt:1 Mode: X Flags: 0x2
02/13/2008 10:27:41,spid4,Unknown,Grant List 3::
02/13/2008 10:27:41,spid4,Unknown,Owner:0x63d34200 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:72 ECID:0
02/13/2008 10:27:41,spid4,Unknown,SPID: 72 ECID: 0 Statement Type: UPDATE Line #: 1
02/13/2008 10:27:41,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =2989303 WHERE kg_table_id ='scheduled_block'
02/13/2008 10:27:41,spid4,Unknown,Requested By:
02/13/2008 10:27:41,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:54 ECID:0 Ec0x330D5500) Value:0x41fcaa20 Cost0/10620)
02/13/2008 10:27:41,spid4,Unknown,
02/13/2008 10:27:41,spid4,Unknown,Node:2
02/13/2008 10:27:41,spid4,Unknown,KEY: 8:1125579048:2 (7802f631993e) CleanCnt:1 Mode: U Flags: 0x0
02/13/2008 10:27:41,spid4,Unknown,Grant List 1::
02/13/2008 10:24:39,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =2988858 WHERE kg_table_id ='scheduled_block'
02/13/2008 10:24:39,spid4,Unknown,Requested By:
02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:65 ECID:0 Ec0x3CA5F500) Value:0x420e2280 Cost0/0)
02/13/2008 10:24:39,spid4,Unknown,Victim Resource Owner:
02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:65 ECID:0 Ec0x3CA5F500) Value:0x420e2280 Cost0/0)
02/13/2008 10:24:39,spid4,Unknown,
02/13/2008 10:24:39,spid4,Unknown,
02/13/2008 10:24:39,spid4,Unknown,Wait-for graph
02/13/2008 10:24:39,spid4,Unknown,
02/13/2008 10:24:39,spid4,Unknown,Node:1
02/13/2008 10:24:39,spid4,Unknown,KEY: 8:1125579048:2 (7802f631993e) CleanCnt:1 Mode: U Flags: 0x0
02/13/2008 10:24:39,spid4,Unknown,Grant List 2::
02/13/2008 10:24:39,spid4,Unknown,Owner:0x4790c880 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:0
02/13/2008 10:24:39,spid4,Unknown,SPID: 65 ECID: 0 Statement Type: UPDATE Line #: 1
02/13/2008 10:24:39,spid4,Unknown,Input Buf: Language Event: update key_generator SET kg_next_value =kg_next_value WHERE kg_table_id ='scheduled_block'
02/13/2008 10:24:39,spid4,Unknown,Requested By:
02/13/2008 10:24:39,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec0x6454B500) Value:0x701d13e0 Cost0/236C)
02/13/2008 10:24:39,spid4,Unknown,
02/13/2008 10:24:39,spid4,Unknown,Node:2
02/13/2008 10:24:39,spid4,Unknown,RID: 8:1:61633:7 CleanCnt:1 Mode: X Flags: 0x2
02/13/2008 10:24:39,spid4,Unknown,Grant List 1::
02/13/2008 10:24:39,spid4,Unknown,Owner:0x70515fc0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:61 ECID:0
02/13/2008 10:24:39,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
02/13/2008 10:05:21,spid4,Unknown,
0
March 12, 2008 at 1:57 am
I cannot directly answer to your question but I have few remarks:
1. If you use such universal key generator table, sooner or later you will run into locks/deadlocks problem (it's just a matter of time). I think that this mentioned most probably on: http://www.sql-server-performance.com/ that such table is a bad design idea and if you want to solve the problem, you should redesign the key generation part of your database.
2. One of my customers had exactly the same idea with key generator table. Additionally, new key was generated by a stored procedure which was updating several tables so they used (long) transactions. The table had a lot of records and, as far as I remember, had no indexes. All these factors together were causing poor performance of the system - even deadlocks.
3. One of the statements in your post looks very strange. I'm talking about:
update key_generator SET kg_next_value =kg_next_value WHERE kg_table_id ='scheduled_block'
It looks like you are updating the kg_next_value column to exactly the same column (only with condition on table id). Is it what you intended to do?
Marek
March 12, 2008 at 8:24 am
thanks, Marek.
I agree with you that this (key_generator) is not optimal design, but I have inherited the problem, and can't change it! The sql line which you said looked strange is indeed a stupid piece of code, but it might actually do what the coder thought it would (lock index and table) if the line above it actually worked (which it does not). I have been able to clean up the code a lot, and get rid of that particular line, and make sure the table has a clustered index. This appears to have stopped the deadlocks, as the clustered index locks the index as well as the row.
I appreciate everyone's responses on this!
March 12, 2008 at 9:25 am
The log is hard to read. I suggest that you use the sp_lock procedure:
http://msdn2.microsoft.com/en-us/library/aa213025(SQL.80).aspx
The information is provided in a very clear way. Especially you have ObjectId and all details of locks.
You may also consider removing older entries in the table (most probably only the latest records are used). This will decrease the size of index (and speed up updates->locks).
I would also looked at: http://msdn2.microsoft.com/en-us/library/aa213039(SQL.80).aspx where you have detailed explanation of locks and read that: "Only one transaction can obtain an update (U) lock to a resource at a time."
Is it possible that this exclusive lock did not exist when the UPDATE statement obtained update lock?
Somewhere in the log there is "Victim" text.
Definitely with the output of the sp_lock procedure one could say much more about the problem.
Marek
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply