simplest Update to minimise deadlock? 1

[ttdobj]

I want to generate a reference number based on the following format:

ddddd/nnnnn

where ddddd is a unique number for the day, which is increased by 1 every day.

nnnnn is an incremented number for each item and is zeroed every morning
given the table:

CREATE TABLE [ReferenceNumberTable] (
[ReferenceNumber] [varchar] (11)
)

what is the best method of returning a new reference for each item (many items per day) and avoiding the possibility of deadlocks, or duplication?

Maybe a different ReferenceNumberTable structure would help
such as ?

CREATE TABLE [ReferenceNumberTable] (
[DailyReferenceNumber] [int],
[ItemReferenceNumber] [int]
)

so that
update ReferenceNumberTable
set ItemReferenceNumber = ItemReferenceNumber + 1
select ItemReferenceNumber from ReferenceNumberTable

and then bulk out the second part of the reference number to five characters after it has been returned to the calling program
???

Am I making a mole out of a moundhill?

John

 

[mrdenny]

I would use the second ReferenceNumberTable. To ensure unqieness on the table wrap the update and select in a transaction so that no one else can update or select from the table while you are using it. As long as you keep the transactions small you shouldn't have any problems with blocking. As you are only referencing one table in the transaction deadlocking shouldn't be an issue at all.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com