Concurrency Issue

[ericdugas]

Hello All,
Basically I am trying to implement assigning of unique consecutive integers from a particular row in a table in MSSQL 2000.

Example pseudocode:

<code>
SET ISOLATION LEVEL REPEATABLE READ

SELECT @VAR1 = NextAvailableNumberColumn FROM TheTable WHERE pk=@pk

UPDATE TheTable SET NextAvailableNumberColumn = @VAR1 + 1 WHERE pk=@pk

RETURN @VAR1

COMMIT
</code>

Will this ensure that each client will always get a unique consecutive number, or is it possible that this could return the same number to more than one client? Also, if this works, could someone please explain why.

[iburyak]

You are trying to reinvent a wheel for ancient databases.

Create an identity column and it will do for you automatically.

To declare identity column you should do following:

Create table table_name(
ID int identity (1,1),
next columns.....

)

Good Luck.

[ericdugas]

That is not acceptable in this case. The reason being it is a legacy app that generates autonumbers for different ranges for many different entities. Basically, if I were to use an identity column, I would have to have a different table for each entitiy, which would mean about 500 different tables.

[iburyak]

In this case you can do following:

[PHP]

DECLARE @VAR1 int, @pk int

BEGIN TRAN

SELECT @VAR1 = NextAvailableNumberColumn FROM TheTable WHERE pk=@pk

UPDATE TheTable SET NextAvailableNumberColumn = @VAR1 + 1 WHERE pk=@pk

RETURN @VAR1

COMMIT TRAN[/PHP]

To test if it works, open 2 windows in query analyzer.
In the first window execute everything but the last line, which is COMMIT TRAN.


Go to the second window and try to execute:

[PHP]SELECT * FROM TheTable[/PHP]

It shouldn't work at this point and query will run without displaying any result. Don't stop your query.

Go to the first window and execute last line.
Result in the second window should return immediately.

It mean while you are in transaction nobody would be able to enter a table until it is finished.

Good Luck.

[iburyak]

OOPS

I think return should be a lat line:

[PHP]DECLARE @VAR1 int, @pk int

BEGIN TRAN

SELECT @VAR1 = NextAvailableNumberColumn FROM TheTable WHERE pk=@pk

UPDATE TheTable SET NextAvailableNumberColumn = @VAR1 + 1 WHERE pk=@pk



COMMIT TRAN

RETURN @VAR1[/PHP]