March 25, 2008 at 5:47 am
Hello, I was hoping someone out there can help me out with a question/issue I'm running into.
I inherited an application and am trying to repair the various bugs. One bug is that when 2 or more users use a form that uses a specific Stored Procedure, they get PK conflict errors.
I traced it to the SP that does the following:
1. It grabs the highest ID number in a table. (They didn't design the table with an IDENTITY ID - so the new records need to have the IDs manually inserted)
Start While Loop
2. Does some other code.
3. Then inserts the new record into the table with the highest ID + 1.
4. If there are more than one record to insert, it increments the highest ID and loops through again without regrabbing the highest ID again.
End While Loop
Problem is if 2 users are running the SP at the same time, User A usually grabs the highest ID, then User B grabs the SAME highest ID, then user A does the insert, then User B tries to insert using the same new ID and BAM!! PK conflict error.
I moved the code that does the new insert right up so that it is the very next statement after the highest ID grab. I also took out the looping increment code and replaced it with a new highest ID grab select.
I really wanted to put those 2 statements in a serializable transaction so that the code could grab the highest ID and do the new insert without someone else reading the same highest ID...but when I do that, User B gets a deadlocking error and is shut out. So the code looks like this now...
START WHILE LOOP
Start Serializable Transaction
1. grab the highest ID number in the table
2. insert the new record w/the highest ID + 1.
End Transaction
3. Do other code.
END WHILE LOOP
How can I isolate the grab and insert statements (1 & 2 above) and prevent someone else from interfering without them getting a deadlocking error. Isn't there some way to have SQL Server queue up the requests so that it waits until the transaction is committed? The key is that I don't want them to even be able to read from the table until the transaction is done...any other way to do this?
March 25, 2008 at 6:11 am
Hi George,
I feel for you. No identity and a while loop?
How are you starting the serializable transaction - is it by set the transaction isolation level to serializable?
One thing you could try is to use a locking hint when performing the select statement that grabs the highest ID. Using an update lock and specifying holdlock should ensure that the lock is held on the table until the completion of the entire transaction.
Something like this perhaps...
begin tran
select @next_id = max(id) from my_table with (updlock, holdlock)
--perform the insert
--error handling
commit tran
March 25, 2008 at 6:31 am
Karl, thanks...
I am setting the level as serializable at the beginning of the SP:
"SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"
I don't know much about locking hints...will this prevent the other SP users from getting the Deadlock error? If I use the hints, do I set the trans level to a lower setting?
Here's more of the SP code...
"OPEN cur
FETCH FROM cur INTO @ProtocolNumber, @HousingVivariumCode, @HousingRoomID, @SpeciesCode,@Strain, @AgeRange, @WeightRange, @GENDer, @ContactPennID, @AnimalIds, @HousingUnitCode
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
--GRABS THE HIGHEST ID + 1
SELECT @CageCardId = MAX(CONVERT(INT,cagecardid))+1 FROM cagecards
--INSERTS NEW RECORD INTO TABLE
INSERT INTO CageCards (CageCardId, CagecardType,
ProtocolNumber, vivariumcode,
roomcardid, SpeciesCode,
Strain, AgeRange, WeightRange,
GENDer, ContactPennID,
CageCardStatus, CreatedBy,
CreatedDate, requisitionid,
instanceid, ActivatedDate,
animalid, HousingUnitCode,
QueuedDate, TimesPrinted,
timesPrintedSinceLastRevision,
ModifiedBy, ModifiedDate)
VALUES (REPLICATE('0',(7-len(@CageCardId))) + CAST((@CageCardId) as varchar), 1,
@ProtocolNumber, @HousingVivariumCode,
@HousingRoomID, @SpeciesCode,
@Strain, @AgeRange, @WeightRange,
@GENDer, @ContactPennID,
@CageCardStatus, @createdby,
GETDATE(), @RequisitionId,
@InstanceId, @ActivationDate,
@AnimalIds, @HousingUnitCode,
@ActivationDate, 0,
0,
@createdby, GETDATE())
COMMIT TRANSACTION
MORE CODE HERE..."
March 25, 2008 at 6:33 am
I'm thinking the READPAST locking hint might help!
March 25, 2008 at 6:46 am
George Heinrich (3/25/2008)
I'm thinking the READPAST locking hint might help!
I wouldn't use the READPAST locking hint as that could cause a considerable amount of problems. Even so, it will only work when the isolation level is read committed.
If you've got serializable set then this is effectively the same as using the holdlock locking hint. Given that you are getting deadlocking I'm tempted to think that the deadlocking is not occurring with the first two statements but could be occurring with some of the code before and/or after the insert statement.
If you have a foreign key in the table CageCards and you're modifying it later on that might cause problems.
To solve the deadlock problem you're going to have to analyse the deadlocking wait graph.
If you haven't already, enable the trace flags 1204 and 1205:
DBCC TRACEON (1204)
DBCC TRACEON (1205)
Then duplicate the steps necessary to cause the deadlock and then look at the error log:
exec sp_readerrorlog
If you haven't looked at the wait graph before it can appear daunting but it's relatively simple to follow once you've done it a few times, and there are quite a few resources online (and in BOL) that can help you read through it.
March 25, 2008 at 6:51 am
OK, I'll give that a try...so enable the trace flags, reproduce the deadlock error, then read the error log right?
The only thing that makes me think it isn't any of the other statements is that they aren't in the transaction. Only the grab and insert statements are in the transaction - I commit the transaction before moving on to the other code.
Thanks.
March 25, 2008 at 7:03 am
George Heinrich (3/25/2008)
OK, I'll give that a try...so enable the trace flags, reproduce the deadlock error, then read the error log right?
Yep, that's right.
The only thing that makes me think it isn't any of the other statements is that they aren't in the transaction. Only the grab and insert statements are in the transaction - I commit the transaction before moving on to the other code.
Even though the other statements aren't in a transaction they're still part of a transaction. If you don't explicitly start a transaction SQL Server will automatically wrap each data modification statement in its own transaction.
It's quite possible that the deadlocking problem was always there but you never ran into it because the PK constraint problem was always hitting you first. Now that you've fixed that problem you're running into a completely seperate problem.
It's part of the fun when you inherit applications :).
March 25, 2008 at 7:04 am
OK, so I set the trace flags on and recreateed the deadlock. The actual deadlock error is:
"Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
Here's the deadlock info from the sp_readerrorlog (I have no idea what it means):
2008-03-25 09:06:07.14 spid4 ----------------------------------|0
2008-03-25 09:06:07.14 spid4 Starting deadlock search 5|0
2008-03-25 09:06:07.14 spid4 Target Resource Owner:|0
2008-03-25 09:06:07.14 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f340|0
2008-03-25 09:06:07.14 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: IX SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f340|0
2008-03-25 09:06:07.14 spid4 |0
2008-03-25 09:06:07.14 spid4 End deadlock search 5 ... a deadlock was not found.|0
2008-03-25 09:06:07.14 spid4 ----------------------------------|0
2008-03-25 09:06:12.14 spid4 ----------------------------------|0
2008-03-25 09:06:12.14 spid4 Starting deadlock search 6|0
2008-03-25 09:06:12.14 spid4 Target Resource Owner:|0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060|0
2008-03-25 09:06:12.14 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060|0
2008-03-25 09:06:12.14 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380|0
2008-03-25 09:06:12.14 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Deadlock cycle was encountered .... verifying cycle|0
2008-03-25 09:06:12.14 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 Cycle: ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 |0
Deadlock encountered .... Printing deadlock information|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Wait-for graph|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Node:1|0
2008-03-25 09:06:12.14 spid4 TAB: 5:1504060444 [] CleanCnt:3 Mode: IX Flags: 0x0|0
2008-03-25 09:06:12.14 spid4 Grant List 0::|0
2008-03-25 09:06:12.14 spid4 Owner:0x7016f340 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:63 ECID:0|0
2008-03-25 09:06:12.14 spid4 SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 95|0
2008-03-25 09:06:12.14 spid4 Input Buf: Language Event: EXEC plrGenerateCageCards 66897,00,34218664,'10/24/2006' ,50,-99,40|0
2008-03-25 09:06:12.14 spid4 Requested By: |0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Node:2|0
2008-03-25 09:06:12.14 spid4 TAB: 5:1504060444 [] CleanCnt:3 Mode: IX Flags: 0x0|0
2008-03-25 09:06:12.14 spid4 Grant List 0::|0
2008-03-25 09:06:12.14 spid4 Owner:0x7016f420 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0|0
2008-03-25 09:06:12.14 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 23|0
2008-03-25 09:06:12.14 spid4 Input Buf: Language Event: EXEC plrGenerateCageCards 70101,00,34218664,'04/04/2007' ,25,-99,17|0
2008-03-25 09:06:12.14 spid4 Requested By: |0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 Victim Resource Owner:|0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380 Cost:(0/0)|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 End deadlock search 6 ... a deadlock was found.|0
2008-03-25 09:06:12.14 spid4 ----------------------------------|0
2008-03-25 09:06:12.14 spid4 ----------------------------------|0
2008-03-25 09:06:12.14 spid4 Starting deadlock search 7|0
2008-03-25 09:06:12.14 spid4 Target Resource Owner:|0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380|0
2008-03-25 09:06:12.14 spid4 Node:1 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec:(0x29BBB598) Value:0x7016f380|0
2008-03-25 09:06:12.14 spid4 Node:2 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x29C0B598) Value:0x7016f060|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Previous victim encountered ... aborting search|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 End deadlock search 7 ... a deadlock was not found.|0
2008-03-25 09:06:12.14 spid4 ----------------------------------|0
March 25, 2008 at 7:13 am
Even though the other statements aren't in a transaction they're still part of a transaction. If you don't explicitly start a transaction SQL Server will automatically wrap each data modification statement in its own transaction.
It's quite possible that the deadlocking problem was always there but you never ran into it because the PK constraint problem was always hitting you first. Now that you've fixed that problem you're running into a completely seperate problem.[/QUOTE]
Hmmm...never thought of that. Good Point.
It's part of the fun when you inherit applications [Smile] .
Amen to that.
Do you know of a good resource that explains the error log info?
March 25, 2008 at 7:17 am
I've identified one area that's useful in that output. I've made the useful bits bold to help.
Deadlock encountered .... Printing deadlock information|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Wait-for graph|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Node:1|0
2008-03-25 09:06:12.14 spid4 TAB: 5:1504060444 [] CleanCnt:3 Mode: IX Flags: 0x0|0
2008-03-25 09:06:12.14 spid4 Grant List 0::|0
2008-03-25 09:06:12.14 spid4 Owner:0x7016f340 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:63 ECID:0|0
2008-03-25 09:06:12.14 spid4 SPID: 63 ECID: 0 Statement Type: UPDATE Line #: 95|0
2008-03-25 09:06:12.14 spid4 Input Buf: Language Event: EXEC plrGenerateCageCards 66897,00,34218664,'10/24/2006' ,50,-99,40|0
2008-03-25 09:06:12.14 spid4 Requested By: |0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec0x29C0B598) Value:0x7016f060 Cost0/0)|0
2008-03-25 09:06:12.14 spid4 |0
2008-03-25 09:06:12.14 spid4 Node:2|0
2008-03-25 09:06:12.14 spid4 TAB: 5:1504060444 [] CleanCnt:3 Mode: IX Flags: 0x0|0
2008-03-25 09:06:12.14 spid4 Grant List 0::|0
2008-03-25 09:06:12.14 spid4 Owner:0x7016f420 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:57 ECID:0|0
2008-03-25 09:06:12.14 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE Line #: 23|0
2008-03-25 09:06:12.14 spid4 Input Buf: Language Event: EXEC plrGenerateCageCards 70101,00,34218664,'04/04/2007' ,25,-99,17|0
2008-03-25 09:06:12.14 spid4 Requested By: |0
2008-03-25 09:06:12.14 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:63 ECID:0 Ec0x29BBB598) Value:0x7016f380 Cost
Starting from the top:
Looking at Node 1:
Spid 63 has been given an intent exclusive lock on table with id 1504060444.
This is as a result of the update on line number 95 in the stored procedure plrGenerateCageCards
At the same time, spid 57 has requested an exclusive lock on the same resource.
Looking at Node 2:
Spid 57 has an intent exclusive lock on the same table as a result of the update on line number 23 in the same procedure.
As we know, from Node 1, spid 63 is also requesting an exclusive lock on the same resource.
So basically it looks like you've got two update statements on line 23 and 95 that is the cause.
Hope that helps.
March 25, 2008 at 7:23 am
Wow...that's insane. So it's not the select or insert statement at all that's doing it.
OK, so now for a super newbie question...how do I figure out the "real" line numbers in my SP (line 93 is a comment for example when I view it in QA) so that I can ID the actual update statements.
BTW, thank you VERY much for helping me out!!!
March 25, 2008 at 7:30 am
Sounds like I'm late to the party - but why not simply switch it to an identity column right now? You're going to keep getting these kinds of headaches with a manually managed id column. Between deadlocks and Pk violations, you're going to have headache upon headache. Would it not be better to rewrite using an identity column? Concurrency in a scenario like this is always murder, especially when there is insistence on performing tasks one row at a time (not what SQL server was designed to do).
Again - I probably don't have all of the facts, but it still sounds like just plain old bad code, which just needs rewriting. You CAN put a bandaid on a gunshot wound, but it's usually a good idea to pull the bullet out and stop the bleeding FIRST.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 7:45 am
Matt,
Thanks for the input.
The problem is that they set this column up as a varchar(15) and often replicate 0's to the front of it. So a cagecard id can be 002341567.
Unless there's a way to use an IDENTITY column that allows auto formatting with leading 0's I'd have to go through a LOT of code to remedy that. I'd also have to fix a lot of CASTs from varchar to int and other stuff like that. My boss will never give me time for that kind of proactive fix. He's more into the..."slap this fix together so that it runs" kinda guy, if you know what I mean.
Believe me, this system could use a complete overhaul...the db design is ridiculous, the code is horrendous, and there's almost no security or injection prevention.
March 25, 2008 at 7:53 am
George Heinrich (3/25/2008)
Wow...that's insane. So it's not the select or insert statement at all that's doing it.OK, so now for a super newbie question...how do I figure out the "real" line numbers in my SP (line 93 is a comment for example when I view it in QA) so that I can ID the actual update statements.
BTW, thank you VERY much for helping me out!!!
I've normally find the line numbering is pretty close to spot on - give or a take a line or two. BTW, I got it wrong and the line number is 95 (I think).
Failing that, find the table name (select object_name(id)) and see if there's an update on that table around line number 95. It's also possible that, if this stored procedure calls another procedure then the line number relates to the nested stored procedure.
I agree with Matt, if you can re-design the table then that's the way to go. I understand what it's like having a boss that demands you spend as little time on it though, so that's another problem you have to work with. Generally speaking though, it's quite possible that the time spent in re-designing is less than the overall time spent in fixing problem after problem.
As for online resources, there's a bunch out there but I'd start with http://support.microsoft.com/kb/832524
Just do a search for trace 1204 and 1205 and you should get a bunch of articles on that. Bear in mind that the SQL Server 2005 deadlock output is different to 2000 so you don't want to get the two confused.
March 25, 2008 at 8:36 am
George Heinrich (3/25/2008)
Matt,Thanks for the input.
The problem is that they set this column up as a varchar(15) and often replicate 0's to the front of it. So a cagecard id can be 002341567.
Unless there's a way to use an IDENTITY column that allows auto formatting with leading 0's I'd have to go through a LOT of code to remedy that. I'd also have to fix a lot of CASTs from varchar to int and other stuff like that. My boss will never give me time for that kind of proactive fix. He's more into the..."slap this fix together so that it runs" kinda guy, if you know what I mean.
Believe me, this system could use a complete overhaul...the db design is ridiculous, the code is horrendous, and there's almost no security or injection prevention.
The identity itself might not, but a computed column based on the indentity column could easily be used for that ( formula: right('0000000000'+cast(id as varchar(10)),10) ). Not necessarily pretty, but it should be a LOT prettier than the craziness you're dealing with.
And I hear you about the boss. I've had several of those, and I've replaced a few of them too....:)
Of course - if you should happen to keep track of your time, and show him that you're fixing this and its screwups x hours per week, versus a one-shot fix - even "slap-dash" bosses start to get the message. And no - I'm not takling about the entire app right now, just this one area. I can't help but think that you are having a lot of soft time being lost, data getting screwed up, etc..., so the head in the sand thing just means you're leaving yourself in a rather "vulnerable" position.
You know....BOHICA....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply