TransactionScope and IsolationLevel lock table

Question

I want to use TransactionScope in my project. I read about this, and found that it was creating an implicit transaction in the database. I want to know if this blocks TransactionScope tables that it manages?

For example, in this code:

 using (Entities ent = new Entities()) { using (TransactionScope tran = Common.GetTransactionScope()) { var oldRecords = ent.tblUser.Where(o => o.UserID == UserID); foreach (var item in oldRecords) { ent.tblUser.DeleteObject(item); } 

and

 public static TransactionScope GetTransactionScope() { TransactionOptions transactionOptions = new TransactionOptions(); transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable; return new TransactionScope(TransactionScopeOption.Required, transactionOptions); } 

Is tblUser until the Complete command is executed?

Is IsolationLevel in an explicit transaction similar to an implicit transaction?

thanks

Answer 1

He is a SQL Server that does a lock - if necessary. Any UPDATE or DELETE operation must contain an exclusive lock on the rows that it affects - if they are already locked by another transaction, it cannot do this. p>

So, in your case, if you deleted several rows from the database, SQL Server will by default block only those rows - those that are deleted. It does not lock the entire table. This is if you do not delete a very large number of rows at the same time - if you delete more than 5'000 rows in one transaction, SQL Server will try to lock the lock and lock the entire table (instead of saving and managing 5000+ individual row locks).

The isolation level determines how long the read locks the line - by default ( READ COMMITTED ), the line will have a common lock only for the time it is read - usually a very very short time. With the isolation level REPEATABLE READ, the shared lock will be held until the end of the current transaction, and SERIALIZABLE will not only block the read lines, but also entire ranges of lines, But again: it only affects READ operations - it does not directly affect DELETE or UPDATE ( in addition to having a general row lock, it can prevent DELETE getting its exclusive lock, which it needs)