February 15, 2008 at 3:26 pm
From research in interpreting this log, it looks like one node has an excludive 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 Ec:(0x330D5500) Value:0x41fcaa20 Cost:(0/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 Ec:(0x3CA5F500) Value:0x420e2280 Cost:(0/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 Ec:(0x3CA5F500) Value:0x420e2280 Cost:(0/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 Ec:(0x6454B500) Value:0x701d13e0 Cost:(0/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
February 17, 2008 at 1:25 am
You wrote:
From research in interpreting this log, it looks like one node has an excludive 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?
I must confess I may not have the full answer, but here is what I'm reading in the first one of the deadlocks you are showing (the 2nd deadlock is not fully shown - which SPID was victimized in the end?):
There are 3 SPIDs involved: 72, 54 and 65.
Node 1:
SPID 72 has requested and has been granted an exclusive (X) lock on RID: 8:1:61633:7.
SPID 54 has requested an update (U) lock. Update locks are a special type of lock held on resources that can be updated. This request in not granted. At this point, SPID 72 is blocking SPID 54, but there is no deadlock.
Node 2:
The X lock granted to SPID 72 in Node 1 has been escalated to U lock on resource
KEY: 8:1125579048:2.
SPID 65 is requesting an U lock on the same resource. SPID 65 already has an X lock, but wants it escalated to a U lock to do the update.
SPID 72 cannot proceed with the update since SPID 65 holds an X lock.
SPID 65 cannot proceed with the update since SPID 72 holds a U lock.
A deadlock occurs and SPID 65 is victimized.
I have made some leaps and jumps in this description, and some of the above may be incorrect. 🙂
Can someone out there take a look and give some feedback?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 18, 2008 at 8:28 am
Thanks for your reply! That is pretty much how I see it, too. My question then becomes: how can both an X and a U be granted on the same resource? These should be mutually exclusive locks - an exclusive and an update can not be granted at the same time, as I understand it? Is there a bug in SQL Server 2000?
February 18, 2008 at 9:04 am
shipleyv (2/18/2008)
Thanks for your reply! That is pretty much how I see it, too. My question then becomes: how can both an X and a U be granted on the same resource? These should be mutually exclusive locks - an exclusive and an update can not be granted at the same time, as I understand it? Is there a bug in SQL Server 2000?
Hi,
I think the problem here is that an X and U are not being granted on the same resource. The exclusive lock is granted on a row (RID: 8:1:61633:7). The update lock has been granted on an index (KEY: 8:1125579048:2). You need to find out what that index is, but my guess is that it's probably an index on kg_next_value.
The sequence of events that leads to this kind of lock is something like this:
Process A runs the WHERE part of the update statement first, so an UPDATE lock is granted (probably on the index on kg_table_id). Then SQL Server does the UPDATE part of the statement. Now, if the kg_next_value column has an index on it then an exclusive lock needs to be taken out on the index. However, prior to this, another process (Process B) could have an UPDATE lock on the index. So process A is waiting for process B to release its UPDATE lock. Problem is that process B is trying to UPGRADE its lock to an exclusive lock. But it cannot upgrade to an exclusive because process A has an exclusive lock on the row.
I think that's more or less how it goes - it's been a few years since I've looked at these types of deadlocks. Do a search on conversion deadlocks - I'm sure you'll come up with a better explanation 🙂
February 18, 2008 at 9:14 am
Thanks, Karl, that makes sense. The only index on this table is on kg_table_id, a column which never gets updated. So how would I generate an exclusive lock on the index while updating a non-indexed column? FYI, I am in a transaction which has READ, UPDATE, COMMIT in a READ COMMITTED setting.
February 18, 2008 at 9:26 am
shipleyv (2/18/2008)
Thanks, Karl, that makes sense. The only index on this table is on kg_table_id, a column which never gets updated. So how would I generate an exclusive lock on the index while updating a non-indexed column? FYI, I am in a transaction which has READ, UPDATE, COMMIT in a READ COMMITTED setting.
That's strange because the last digit in the KEY identifier is 2, which is the id of the index. Do a select * from sysindexes where id = 1125579048. The index with indid = 2 is the index that has the UPDATE lock.
When you say that you're in a transaction that has a READ, UPDATE, COMMIT.... do you mean to say that you perform a SELECT statement prior to the UPDATE statement? If so that could also lead to deadlocks because the SELECT statement will issue shared locks, which won't be allowed to be upgraded whilst other statements have shared locks.
If this is the case you might want to consider using a UPDLOCK locking hint on the select statement. This will ensure that an UPDATE locks is held on the resource, preventing other processes from taking out shared locks on the same resource.
February 18, 2008 at 9:47 am
Ran the select, and indid = 2 is for the primary key on the table (indid = 0 is for the table, and there is no indid = 1). There are also five indexes all beginning with _WA_Sys_ -- what are these? Something SQL Server adds? Since one is on _next_value, could that be a problem?
February 18, 2008 at 10:41 am
shipleyv (2/18/2008)
Ran the select, and indid = 2 is for the primary key on the table (indid = 0 is for the table, and there is no indid = 1). There are also five indexes all beginning with _WA_Sys_ -- what are these? Something SQL Server adds? Since one is on _next_value, could that be a problem?
That would mean that the primary key is a nonclustered index. seeing as it's the only index in the table I would recommend that you change it to a clustered index.
The indexes beginning with _WA are index statistics, that are probably auto_created by SQL Server if you have auto create statistics turned on. I don't believe that SQL Server can take out a key lock on these objects (if that's what they are).
February 18, 2008 at 11:32 am
Yes, the primary key is non-clustered. It sounds like if I 1) make this a clustered key, and 2) issue my select WITH UPDATE, that since I only pull one record, everybody else on the system trying for other rows in this table will be ok, and anyone trying for the row I have will wait until it is released. My deadlocks should go away. Do you agree? Or will making it a clustered key mean that everyone else will not be able to select other rows for update?
Thanks for your help!!
February 18, 2008 at 12:09 pm
shipleyv (2/18/2008)
Yes, the primary key is non-clustered. It sounds like if I 1) make this a clustered key, and 2) issue my select WITH UPDATE, that since I only pull one record, everybody else on the system trying for other rows in this table will be ok, and anyone trying for the row I have will wait until it is released. My deadlocks should go away. Do you agree? Or will making it a clustered key mean that everyone else will not be able to select other rows for update?Thanks for your help!!
Deadlocks are all about timing. Proper indexing ensures that queries get completed quicker AND fewer resources are locked in the process. So the odds for deadlocks are greatly diminished.
However, do not expect deadlocks to completely disappear. They, just as blocking, are a natural consequence of concurrency in a database system.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 18, 2008 at 1:55 pm
shipleyv (2/18/2008)
Yes, the primary key is non-clustered. It sounds like if I 1) make this a clustered key, and 2) issue my select WITH UPDATE, that since I only pull one record, everybody else on the system trying for other rows in this table will be ok, and anyone trying for the row I have will wait until it is released. My deadlocks should go away. Do you agree? Or will making it a clustered key mean that everyone else will not be able to select other rows for update?Thanks for your help!!
As Marios, said, proper indexing ensures that your transactions happen quicker, which in turn reduces the amount of time that other processes are left waiting.
You can never eliminate locking/blocking - in fact you wouldn't want to. The clustered index won't ensure that only 1 record is locked. Whether one record is locked or not is dependant on lock escalation, which is covered in BOL. If there are sufficient resources (i.e. memory) on the server then SQL Server will use row-level locking. However, if necessary SQL Server will automatically escalate a row-level lock to the page level, or extent level (or even table level).
What the clustered index will do is ensure that, regardless of what granularity of locking is used by SQL Server, the statement will run faster, so everyone else will be able to get to the data quicker.
The deadlocking situation is special situation and, if I've understood what you're doing correctly, then using the UPDLOCK hint in your select statement will eliminate the occurrance of these deadlocks. That's not to say that you'll eliminate all deadlocks - just the ones that have been occurring in this fashion.
Hope that helps.
February 18, 2008 at 1:59 pm
Helps a lot, and thanks very much!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply