|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Deadlock problemI'm having a terrible day full of deadlocks, and I would be greatful if someone can help me out of this problem. We have a table NEXTSERIALNO which is used to generate a sequencial number. To ensure that the sequence is properly maintained (as a duplicated number would mean a serious flaw in the system) we are doing the following; 1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 2. BEGIN TRANSACTION 3. SELECT LastNo FROM NEXTSERIALNO WITH (ROWLOCK) WHERE <Where condition> 4. INCREASE the LastNo to the next number 5. ENDTRANS 6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED When the above is done lot of times, a deadlock occurs. Currently we'are executing the above from VFP, So if I put the above into a stored procedure, it'll definetely get fast, and hopefully the deadlocks may become less. But is there any better solution ? I hope there would be. Thanking you in anticipation, Roshan Jayalath How about using the identity property?
-- Show quoteHide quoteHilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Roshan Jayalath" <RoshanJayal***@discussions.microsoft.com> wrote in message news:A3C833AE-DBD4-4C12-BB25-A5C8464AAB9F@microsoft.com... > Hi all, > > I'm having a terrible day full of deadlocks, and I would be greatful if > someone can help me out of this problem. > > We have a table NEXTSERIALNO which is used to generate a sequencial > number. > > To ensure that the sequence is properly maintained (as a duplicated number > would mean a serious flaw in the system) we are doing the following; > > 1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > 2. BEGIN TRANSACTION > 3. SELECT LastNo FROM NEXTSERIALNO WITH (ROWLOCK) WHERE <Where condition> > 4. INCREASE the LastNo to the next number > 5. ENDTRANS > 6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED > > When the above is done lot of times, a deadlock occurs. Currently we'are > executing the above from VFP, So if I put the above into a stored > procedure, > it'll definetely get fast, and hopefully the deadlocks may become less. > > But is there any better solution ? I hope there would be. > > Thanking you in anticipation, > Roshan Jayalath Dear Hillary,
Thanks for the reply. I'm not sure how to use the identity in this case. Here we are only having a single record where the last no. is retrieved and then it is updated by adding one everytime a method is called. Eg : IdKey LastNo Type 1 185 X 2 25 Y Where condition would be like Type = 'X' When the method is called with 'X' as param, it will return 186 and update record with 186. Best Regards, Roshan Jayalath Show quoteHide quote "Hilary Cotter" wrote: > How about using the identity property? > > -- > Hilary Cotter > Director of Text Mining and Database Strategy > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. > > This posting is my own and doesn't necessarily represent RelevantNoise's > positions, strategies or opinions. > > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > > > "Roshan Jayalath" <RoshanJayal***@discussions.microsoft.com> wrote in > message news:A3C833AE-DBD4-4C12-BB25-A5C8464AAB9F@microsoft.com... > > Hi all, > > > > I'm having a terrible day full of deadlocks, and I would be greatful if > > someone can help me out of this problem. > > > > We have a table NEXTSERIALNO which is used to generate a sequencial > > number. > > > > To ensure that the sequence is properly maintained (as a duplicated number > > would mean a serious flaw in the system) we are doing the following; > > > > 1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > > 2. BEGIN TRANSACTION > > 3. SELECT LastNo FROM NEXTSERIALNO WITH (ROWLOCK) WHERE <Where condition> > > 4. INCREASE the LastNo to the next number > > 5. ENDTRANS > > 6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED > > > > When the above is done lot of times, a deadlock occurs. Currently we'are > > executing the above from VFP, So if I put the above into a stored > > procedure, > > it'll definetely get fast, and hopefully the deadlocks may become less. > > > > But is there any better solution ? I hope there would be. > > > > Thanking you in anticipation, > > Roshan Jayalath > > > Roshan wrote:
Show quoteHide quote > > > I'm having a terrible day full of deadlocks, and I would be greatful if Hilary wrote:> > > someone can help me out of this problem. > > > > > > We have a table NEXTSERIALNO which is used to generate a sequencial > > > number. > > > > > > To ensure that the sequence is properly maintained (as a duplicated number > > > would mean a serious flaw in the system) we are doing the following; > > > > > > 1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE > > > 2. BEGIN TRANSACTION > > > 3. SELECT LastNo FROM NEXTSERIALNO WITH (ROWLOCK) WHERE <Where condition> > > > 4. INCREASE the LastNo to the next number > > > 5. ENDTRANS > > > 6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED > > > > > > When the above is done lot of times, a deadlock occurs. Currently we'are > > > executing the above from VFP, So if I put the above into a stored > > > procedure, > > > it'll definetely get fast, and hopefully the deadlocks may become less. > > > > > > But is there any better solution ? I hope there would be. > > How about using the identity property? Roshan wrote:Show quoteHide quote > I'm not sure how to use the identity in this case. Here we are only having a Dump the serializable isolation level and try something like this:> single record where the last no. is retrieved and then it is updated by > adding one everytime a method is called. > > Eg : > IdKey LastNo Type > 1 185 X > 2 25 Y > > Where condition would be like Type = 'X' > > When the method is called with 'X' as param, it will return 186 and update > record with 186. -- All in transaction to maintain the lock. BEGIN TRANSACTION -- This locks the row with "Type = @Type" and increments it's value. UPDATE NEXTSERIALNO SET LastNo = LastNo + 1 WHERE Type = @Type -- This returns the incremented value. SELECT LastNo FROM NEXTSERIALNO WHERE Type = @Type -- Now we release the lock. COMMIT TRANSACTION Kamil 'Hilarion' Nowicki What indexes defined on the NEXTSERIALNO table? If the table is small
enough, remove any non-clustered ones and see if that is going to help. If u still have deadlocks, u could enable trace flag 1204 in order to see which objects get involved. HTH, Igor *** Sent via Developersdex http://www.developersdex.com *** On Mon, 4 Sep 2006 06:21:01 -0700, Roshan Jayalath wrote:
Show quoteHide quote >Hi all, Hi Roshan,> >I'm having a terrible day full of deadlocks, and I would be greatful if >someone can help me out of this problem. > >We have a table NEXTSERIALNO which is used to generate a sequencial number. > >To ensure that the sequence is properly maintained (as a duplicated number >would mean a serious flaw in the system) we are doing the following; > >1. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE >2. BEGIN TRANSACTION >3. SELECT LastNo FROM NEXTSERIALNO WITH (ROWLOCK) WHERE <Where condition> >4. INCREASE the LastNo to the next number >5. ENDTRANS >6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED > >When the above is done lot of times, a deadlock occurs. Currently we'are >executing the above from VFP, So if I put the above into a stored procedure, >it'll definetely get fast, and hopefully the deadlocks may become less. > >But is there any better solution ? I hope there would be. Step 3 gets a shared lock. Step 4 tries to promote the lock to an exclusive lock. If a second connection executes step 3 when the first is between 3 and 4, it will also get a shared lock. Then, none of the two connections can promote to exclusive - deadlock. Three ways around this. 1. In this case, you can change the code - see reply by Kamil 'Hilarion' Nowicki. This is good advice in this case, but yoou can't use it in all scenario's where you encounter deadlocks. That's why you also need the next two. 2. Force SQL Server to obtain an exclusive lock already in step 3, by adding the XLOCK locking hint. Great if code like the sequence above is the only way you use the table, since you also get a small performance gain - no need to promote the lock between steps 3 and 4. The downside is that the exclusive lock will also lock other transactions that really only need to show the data without intention of changing it. 3. Force SQL Server to get a so-called "Update lock" in step 3 by adding the UPDLOCK locking hint. This is halfway between shared and exclusive lock - it's a shared lock (i.e. other shared lock requests will be granted), but with an exclusive right to promote to exclusive lock later (i.e. other update lock and exclusive lock requests are denied). -- Hugo Kornelis, SQL Server MVP  
Thread options
Other interesting topics
Creating 2nd transaction form inside db ?
About Cursor on Dynamically created Tables. SQL Emergency! Need Help Add a Column at Particullar Position n MS SQL Server Date between two wildcards SQL server 2005 query problem Query question Record searching question Incorrect syntax near the keyword 'End'. Numbering rows in SELECT query? |
|||||||||||||||||||||||
