June 8, 2009 at 3:40 am
Hi All,
I'm having a deadlock on a table with only one row that keeps a counter for an AccountNumber.
The problem raised only on SQL 2005 an not on SQL 2000.:w00t:
After selecting an AccountNumber the number is incremented with 1 and an update is performed
within the same transaction.
The example says more about it:
SET TRANSACTION isolation level serializable
SET NOCOUNT ON
DECLARE @newCount numeric(19,0)
DECLARE @currentObjID uniqueidentifier
BEGIN TRAN
-- Check for existing record
SELECT
@currentObjID = objID,
@newCount = AccountNumber
FROM AccountCounter WITH (UPDLOCK)
IF @newCount IS NULL
-- INSERT statement for new 'init' Record with counter value =0
-- ...
-- Increment Number
SET @newCount = @newCount + 1.0
UPDATE AccountCounter with (ReadCommitted)
SET AccountNumber = @newCount
WHERE objid = @currentObjID
-- Retrun value
SELECT @newCount
COMMIT TRAN
The row 'ObjiD' is the primary key and has an Index on it.
The query plan indicates a table scan on the select and an index seek on the update statement
Can somebody point out the cause of the Deadlock?
Thx in advance - Frank
June 8, 2009 at 4:18 am
I'm not 100% sure but I think it's because you have an UPDLOCK within the transaction.
Have you tried removing that table hint?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 8, 2009 at 4:49 am
Why not avoid the issue altogether , and do the work in one statement ?
UPDATE AccountCounter
SET AccountNumber = AccountNumber+1
Output inserted.AccountNumber
WHERE objid = @currentObjID
June 9, 2009 at 1:03 am
I added UPDLOCK to assure to hold the value for other not to update will within the transaction.
As I mentioned before, the SP worked perfectly on SQL2000 in a very stressed environment.
I'll dig further into the UPDLOCK hint.
June 9, 2009 at 1:04 am
I'll try different cases to see if the one line statement could replace the initial statement.
Thanks voor the tip!
June 10, 2009 at 12:17 pm
Dave Ballantyne (6/8/2009)
Why not avoid the issue altogether , and do the work in one statement ?UPDATE AccountCounter
SET AccountNumber = AccountNumber+1
Output inserted.AccountNumber
WHERE objid = @currentObjID
I think that will require the use of a temp object (or derived table) to place the output in (this can be avoided in SQL 2008) and then reading that to get the output into the variables you desire. This can add to overhead in a stressed environment.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 11, 2009 at 1:31 am
I will keep your advise in mind when we migrate from SQL2005 to SQL2008.
For now I changed the UPDLOCK hint to TABLOCKX and this seems to be doing it!
Thanks to all for your replies 😉
June 11, 2009 at 1:40 am
I guess that it could put a small strain on the server , not anything i would particularly worry about.
Here's another method anyway
create table tabx
(
AccNo integer
)
go
insert into tabx values(0)
go
Declare @AccNo integer
Update tabx
set @AccNo = AccNo,
AccNo = AccNo +1
select @AccNo
June 11, 2009 at 1:54 am
As the AccountNumber table is to be treated as a Counter Table we can not forget to test if the record corresponding to the where clause exists!
If not then we have to create an initial record with the counter value = 0.
But your statement simplifies and combines the incremental part with the update part.
That was nice to know 🙂
June 11, 2009 at 2:05 am
Presumably there is job responsible for creating the objid's , could that not insert the priming row ?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply