Lock when allocating a customer no / order no 1

[Glasgow]

I want to be able to increment, return and update the next available order number from a 'pot' held in a control table. So one possible approach would be:

UPDATE RefStore SET NextOrderNo=NextOrderNo+1
SELECT NextOrderNo FROM RefStore

But what if someone else is executing the same logic simultaneously? How do I guarantee that their UPDATE has not been actioned before my SELECT? If that happens I could be returned a number which is one greater than the one I need.

I assume that I'd have to implement some kind of locking mechanism but I'm not sure how to go about this.

I appreciate that I could use an IDENTITY column with @@IDENTITY to address this problem but I'd prefer to have control over the numbering and I expect that the same problem arises in other scenarios where an IDENTITY column is not appropriate.

 

[bborissov]

If you executing this in ONE batch nobody could update the table before your batch is complete.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Glasgow]

OK thanks, that's good to know but do I need to wrap it in a transaction to achieve that?

 

[bborissov]

For me, every update, insert and delete should be in transaction

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Glasgow]

I thought I might get an answer like that! Thanks. I'll assume that it's "recommended but not necessary" to achieve my goal.