Lock, query and get unique number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Lock, query and get unique number

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-11-28 : 02:02:09

Hi,

I've one table, which contains Sequence Number. Let's say that table as follow,

SYSSeqNo
Type | SeqNo
----------------
AR | 37
RR | 1477

I've stored procedure will execute INSERT statement. Before execute INSERT statement, my stored procedure will query above table to get SeqNo first. This SeqNo will be used as a one of the others input to perform INSERT statement.

My question is, how to make sure there's no other stored procedure will get the same SeqNo as I am? If my stored procedure query on SYSSeqNo table, how to make others Stored Procedure have to wait my stored procedure will finish to get SeqNo first? Then, others can come to get SeqNo from SYSSeqNo table. Did have some special SQL to control that?

Really needs technical answer.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-28 : 02:27:14

You can use an UPDATE statement first to increase SeqNo value.

DECLARE @SeqNo INT

UPDATE SYSSeqNo SET @SeqNo = SeqNo = SeqNo + 1 WHERE Type = 'AR'
SET @SeqNo = @SeqNo - 1

Two updates never happens at same time. One transaction will have to wait.
Using the technique above insures that you will get "your" value even if some other updates at the same time.
And when you fetch the value within same statement there is no chance for another to update value between your update and getting the value.

E 12°55'05.63"
N 56°04'39.26"