TransactionScope, Selects using Subsonic

Question

Hi. I have an Invoice table (and a SubSonic 'Invoice' ActiveRecord) with an InvoiceNumebr column that needs to have unique numbers. I am trying to GetTheNextAvailableNumber() inside of a TransactionScope using block. This works. What I'm not sure of is, what happens if 5, or 50 different users try to create an Invoice at approx. the same time, the method would return the same number for all 5 or 50 users if they don't save the invoice object until later.

The GetTheNextAvailableNumber() method which called inside the TransactionScope block uses a Subsonic Select query with a MAX() to get the maximum number, then adds 1. The column itself does have a UNIQUE index!

Would the Transaction's isolation level default (Serializable) make sure that each of those gets a unique number? Or is there a more clever mechanism to achieve that? The column cannot have an IDENTITY, since the PK column InvoiceID already has it.

Answer 1

You're talking about concurrency here over the span of a transaction - the only way this would work is to throw a lock on the table while the trannie executes and ... that can kill your app.

Is there a chance you can use a Guid?

Answer 2

What if you build a second table in your db and store value of the NextAvailableNumber in that table. Then you'd use a StoreProc to retrieve that value and increment it in the same call. You'd just need to lock that stored proc to prevent concurrent calls. You also might be able to lock it within the stored proc.