I need to read a 1 row control table to get the next account number to assign (MS SQL Server). I then will insert a row to a Customer table using the next available account number.
The problem is if 2 threads/sessions read the Control table at the same time. The 1st to do the insert will be OK but the next will fail.
I found 3 solutions (1 SQL Server and 2 CF) but I'm not sure which to use :
1) T-SQL Update Intent :
It seems you can read a row with update intent :
SELECT * from CONTROLTBL (UPDLOCK)
--
Insert your row
--
UPDATE CONTROLTBL
SET Counter = counter +1
2) There is a CFTRANSACTION tag that is used to group a series of updates and roll back if any one of them fails. But is has an option of :
<CFTRANSACTION isolation="serializable">
Read and update
</CFTRANSACTION>
3) The last is a CFLOCK tag that will prevent any thread from proceeding
<cflock scope = "Application" name = "newaccount" type = "exclusive "> <!--- CFML to be synchronized --->
</cflock>
Any suggestions?
Thanks in advance,
hefterr
AdChoices