MTS Transaction Control

[KZeronik]

I am familiar with MTS declaritive transactions.&nbsp;&nbsp;However, I am having a problem with the following...<br><br>I have an application that is comprised of the following operations that make up a transaction.<br><br>Select Reference Number<br>Insert Records Based Upon Selected Reference Number<br>Update Reference Number<br><br>I have 3 methods that carry out each action and a wrapper method to call each of these according to our business logic.&nbsp;&nbsp;MTS is sumbitting or rolling back as 1 transaction, BUT I can not adequately lock the table that is storing the reference number for the duration of a transaction.&nbsp;&nbsp;That is if multiple clients initiate this transaction all clients read the same reference number ... I don't want clients to select the reference number until previous transactions have been completed or aborted.&nbsp;&nbsp;From what I can find, read locks do not exclude other read locks.&nbsp;&nbsp;Any suggestions?

 

[andybee]

I did something similar on my last project. What worked for me was tweaking the SQL / stored procedure that reads the reference number (step 1 above) so that it actually does a dummy update as well as the read, e.g. UPDATE RefNumbers SET NextRefNum = NextRefNum. In the same procedure, do the select that actually selects the reference number as before. This way, the reference number table / row gets a lock for the duration of the transaction (even though you haven't actually changed its value yet until step 3).