sql server - Generate Invoice Number for Multi user environment in C# -

i generating invoice numbers using max function , adding 1 last invoice number. straightforward. applied multi user environment getting problem. because 2 users open invoice window @ same time both same id, invoice number should first thing appear , not last cannot use identity (auto generate id) invoice number. want generate invoice number multi user environment windows form application in c#...

the other problem happen 2 users accessing , updating same record same time.

i hope understand problem. read optimistic vs. pessimistic locking need solution. can please reply me

have different database table store max invoice number. when user opens invoice window, run stored procedure to:

• lock table
• get current number
• store current+1 number
• unlock table
• return current+1 number

this ensure though there simultaneous requests unique invoice number.

the flip side: 1. stored procedure cannot run simultaneously multiple users bottleneck in case of high traffic. 2. there holes in invoice numbers - holes invoices cancelled.

if not positive approach invoice number must generated @ time of saving , identity column have mentioned users see invoice number when start working on invoice.

update:

i found excellent article elaborates above approach using sp_getapplock. article link here. recommend use approach.