This is what I am trying to do and it doesn't lock the transaction reads
using( transaction = TransactionScope()) //Default ReadCommitted Isoloation
{
read transactionid (stored procedure without nolock)
if transactionid ispending do transaction else return
mark transactionid is completed
transaction.finish()
}
But it allows simultaneous calls to succeed doing transaction. Shouldn't this only succeed if it's using a nolock?
It seems to be working fine with (updlock) and (rowlock)
Would using updlock in a stored procedure lead to other issues like deadlock? (seems okay)
I would like to lock only that particular row and not the table.
Another option is IsolationLevel but not sure which one is granular enough for this?
Also I don't want to lock other reads inside this transactions which may occur.
Only want to ensure that once transactionwrapper created and when transactionid read happens every other simultaneous reads will wait until this transactionid is read is completed in the scope its running and therefore will quit