0

This is what I am trying to do and it doesnt lock the transaction reads

using( transaction = TransactionScope()) //Default ReadCommitted Isoloation
{
 read transactionid  (stored proc without nolock)
 if transactionid ispending do transaction else return
 mark transactionid is completed
 transaction.finish()
}

But it allows simulataneous calls to succeed doing transaction. Shouldnt this only succeed if its a nolock?

It seems to be working fine with (updlock) and (rowlock)

would using updlock in Storedproc 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 dont 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

Dexters
  • 2,419
  • 6
  • 37
  • 57
  • 1
    If you are asking why two `select`s don't block each other, then it's because they are [not supposed to](https://stackoverflow.com/a/12610346/11683). – GSerg Oct 19 '21 at 19:22
  • Thanks for this, but combine this with Transaction in the code => wouldnt the transactionwrappers ReadCommitted isolation level ensure that any other transaction with this dataset will get the latest data (i.e the first one to enter transaction with this data will be committed before other can read)? – Dexters Oct 19 '21 at 21:00
  • 1
    Please click the link that explains it. It lacks a further link to the [lock compatibility matrix](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock_compatibility), so also consult that. – GSerg Oct 19 '21 at 21:35

0 Answers0