How to handle TransactionScope on multi-instance applications?

Question

I'm using Entity Framework 5.0. I need to restrict access to a row while I'm reading and Updating it.

My application run on more that 10 machines and when I use TransactionScope ,some time some other application on other machines (randomly) dump and can not update or read data from that table.

I think TransactionScope restricted access to my table while its reading or updating and other update or reading request will dump.

How can I handle other requests from other applications to update or read data from that table when one application did not done TransactionScope action?

How can I handle it?

using (var myDB = new MyDBEntities())
{
    using (TransactionScope scope = new TransactionScope())
    {
        // read and update myDB object with some code in here
        // ...

        myDB.SaveChanges();
        scope.Complete();
    }
}

Answer 1

When using transaction scopes you can hold another transaction to select/update the same row.

Also, you can hide uncommitted data from another transaction using special table hint called as READPAST.

Example:

session1

BEGIN TRANSACTION
update users with (SERIALIZABLE) set name='test' where Id = 1    
-- COMMIT --not committed yet

session2

select * from users where Id = 1 
--waits session1 to finish its job
--rows returns after commit

session3

select * from users with (READPAST) where Id = 1 --returns 0 row

While you're not commit the transaction, other sessions could not read or update the data. When you commit the transaction on session1, session2 will be able to read the row.

http://omerc.blogspot.com.tr/2010/04/transaction-and-locks-in-ms-sql-2008.html

https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx

https://www.codeproject.com/Articles/690136/All-About-TransactionScope

Be aware that data is still open to lost updates. To prevent it you may consider to use optimistic/pessimistic locking.

https://logicalread.com/sql-server-concurrency-lost-updates-w01/#.WskuNNNuZ-U