How do I structure this transaction?
===

We have an ASP.NET/MSSQL based web app which generates orders with sequential order numbers.

When a user saves a form, a new order is created as follows:

1. SELECT MAX(order_number) FROM order_table, call this max_order_number
2. set new_order_number = max_order_number + 1
3. INSERT a new order record, with this new_order_number (it's just a field in the order record, not a database key)

If I enclose the above 3 steps in single transaction, will it avoid duplicate order numbers from being created, if two customers save a new order at the same time? (And let's say the system is eventually on a web farm with multiple IIS servers and one MSSQL server).

I want to avoid two customers selecting the same MAX(order_number) due to concurrency somewhere in the system.

What isolation level should be used? Thank you.
