CODEWITHSUNDEEP
subsonic
John
John

Reputation: 3677

TransactionScope, Selects using Subsonic

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.

Upvotes: 2

Views: 286

Answers (2)

Jim W
Jim W

Reputation: 4970

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

Upvotes: 0

user1151
user1151

Reputation:

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?

Upvotes: 1

Related Questions