Clever Geek Handbook

TransactionScope, selects with Subsonic

I have an Invoice table (and SubSonic 'Invoice' ActiveRecord()

with a column InvoiceNumebr

that must have unique numbers. I am trying to use GetTheNextAvailableNumber()

inside a block TransactionScope

.

I'm not sure what happens if 5 or 50 different users try to create an invoice for approx. at the same time, the method will return the same number for all 5 or 50 users, unless they save the account object until it appears.

The method GetTheNextAvailableNumber()

that is called inside the block TransactionScope

uses a Subsonic Select query MAX()

to get the maximum number, and then adds 1. The column itself has a UNIQUE index!

Will there be a transaction isolation level default (Serializable) make sure each one gets a unique number? Or is there a smarter mechanism to achieve this? The column cannot have an IDENTITY because the PK InvoiceID column already has one.

+2


source to share


2 answers


You are talking about concurrency here in the middle of a transaction - the only way it will work is to throw a lock on the table while the trannie is done and ... which might kill your application.



Is there a chance you can use the Guid?

+1


source


What if you create a second table in your db and store the value NextAvailableNumber

in that table. You will then use a stored procedure to retrieve this value and increment it in the same call. You just need to lock this stored procedure to prevent concurrent calls. You can also lock it in a stored procedure.



0


source






More articles:


All Articles