Hiya Meg77,

You don't need to use sp_getapplock, the built in transactions work fine for the example you mentioned.

The following code is just one way to skin this cat...

declare @i_uid int

begin transaction

SELECT @i_uid = i_nextUID

FROM t_table with (updlock)

update t_table set i_nextUID = i_nextUID +1

commit transaction

--Use @i_uid here.

Remember that changes to variables, unlike actions to Real tables, are not rolled back, they are left in the same state as before the rollback (if the transaction fails or is deliberately rolled-back).

A select statement which uses the update lock type reacts differently when inside a transaction than when within a normal session.

When inside the transaction the select "with (updlock)" locks the table for updates for the entire transaction. As this uses an update lock we can then update the table in the same transaction.

Any select statement that tries to access the same table with an update lock will simply wait until the lock get released as the transaction ends (or it may timeout if your update code take too long).

You can try it out with the following example.

Create the table:

create table t_table (i_nextUID int)

insert into t_table values (1)

Update code:

declare @i_uid int

begin transaction

SELECT @i_uid = i_nextUID

FROM t_table with (updlock)

waitfor delay '00:00:30'

update t_table set i_nextUID = i_nextUID +1

commit transaction

print @i_uid

Test code:

SELECT i_nextUID, getdate()

FROM t_table with (nolock)

go

SELECT i_nextUID, getdate()

FROM t_table

go

SELECT i_nextUID, getdate()

FROM t_table with (updlock)

Create the table then open two connections, and paste the update code in one and the test code in the other.

Run the update code and then whilst it is executing run the test code in the other window.

You should find that the test code returns three rowsets, the first rowset should return immediately, the second may also do so (dependent on your connection lock settings), the third will always wait until the example code releases the lock when it closes the transaction. Note: the test code is not in a transaction.

The waitfor delay is simply used to slow the query after the lock has been granted but before the update has happened, effectively forcing the situation that you want to avoid, two people reading the next number from the table.

The three rowsets demonstrate that you can still read from the table using either Nolock, or a normal select statement, but that you can not if you use the update lock whilst an existing update lock is being held.

For confirmation you can try replacing the test code (Not the update code) with the following:

declare @i_uid int

begin transaction

SELECT @i_uid = i_nextUID

FROM t_table with (updlock)

update t_table set i_nextUID = i_nextUID +1

commit transaction

print @i_uid

You should now have two connections with nearly identical SQL.

Run the update code as before and then execute the new test code, you should find that even tho this new code has no waitfor delay it still waits for the update code to finish executing and both return unique numbers.

All done without using extra stored procedures or other "locking" tables, just using the same code that you probably would have anyway, but now inside a transaction with an update lock. Just make sure that the code inside the transaction is just enough to get the job done, don't put more in than is needed otherwise you will slow down the generation of uids.

As a final test (to prove we need to provide the table locking hint to SQL Server) remove the "with (updlock)" from both the update connection and the test connection and run both as before, you will find that the test connection returns immediately, that both return the same uid and that both increment the table missing a uid out.

Of course an identity (autonumber) field with SCOPE_IDENTITY also works very well...

SgtWilko