Return to Answer

I've had a look and MS themselves offer a solution without locks

Link

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...

I've had a look and Microsoft themselves offer a solution without locks:

SQL Server Sequence Number

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a second process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a second select then you don't need the transaction.

I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...

I've had a look and MS themselves offer a solution without locks

Link

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...

I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...

I've had a look and MS themselves offer a solution without locks

http://blogs.msdn.com/b/sqlcat/archive/2006/04/10/sql-server-sequence-number.aspx

This is a simple update with no lock hints, but they say it locks/deadlocks.

Nothing much on SO about this either.

I'd be inclined to add UPDLOCK to your ROWLOCK (as per "table as a queue" (SO) but without READPAST). This will increase isolation in case a 2nd process starts reading.

However, the fact all your processes want to read/write the same row makes me second guess myself. READPAST allows safe concurrency but in this case it's useless.

Note: you can use the OUTPUT clause instead of a 2nd select then you don't need the transaction.

HTH...