faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

21 of 33 people (64%) answered Yes
Recently 4 of 10 people (40%) answered Yes

Entry

How do you create a transaction-safe, thread-safe sequence generator on SQL Server?

Nov 11th, 2006 11:29
Matt Chatterley, Neil Moss,


This is a tricky one in some ways. In theory any query is
transaction-safe (assuming you mean ACID compliant) as all queries are
implicitly executed within transactions, even if not specified. Thread
safety is more of an issue, and you WILL have to force a certain amount
of locking.
If all you require is an incremental seed value on a table, consider use
of the IDENTITY property against a table (consult books on-line via F1
for more details). If you require a custom sequence, try using a stored
procedure which does something like:
BEGIN TRANSACTION IncrementSequence
SELECT @CurrentMax = MAX(Value) FROM dbo.SequenceTable WITH(ROWLOCK,
UPDLOCK, HOLDLOCK)
SET @NewMax = @CurrentMax + 1
INSERT INTO SequenceTable WITH(ROWLOCK, UPDLOCK, HOLDLOCK)
(Value) 
VALUES (@NewMax)
COMMIT TRANSACTION
RETURN @NewMax
This will acquire the highest value, lock the row, mark the lock as held
for update and persist it through the duration of the transaction (the
hints operate in that order), and then do the same for the newly stored
value. There are severe performance implications with this approach
though, so put as few commands into the transaction as possible!