TransactionScope locking table and IsolationLevel

Question

I want to use TransactionScope in my project. I read about it and I found that it creates an implicit transaction in the database. I want to know if that TransactionScope locks tables that it manipulates?

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 locked until Complete command issue?

Does IsolationLevel in explicit transaction similar to implicit transaction?

Thanks

Answer 1

It's SQL Server that does the locking - if needed. Any UPDATE or DELETE operation must get an exclusive lock on those rows it affects - if those are locked already by another transaction, it cannot do that.

So in your case, if you're deleted a number of rows from the database, SQL Server by default will lock those rows only - those that are being deleted. It doesn't lock the whole table. This is unless you delete a very large number of rows at once - if you delete more then 5'000 rows in a single transaction, SQL Server will try to do a lock escalation and lock the whole table (instead of keeping and managing 5000+ individual row locks).

The isolation level only defines how long reading will lock a row - by default (READ COMMITTED), the row will have a shared lock only for the time it's being read - typically a very very short time. With isolation level REPEATABLE READ, the shared lock will be held until the end of the current transaction, and SERIALIZABLE will not only lock the rows being read, but entire ranges of rows. But again: that only affects READ operations - it has no direct impact on the DELETE or UPDATE statements (other than having a shared lock on a row might prevent the DELETE from acquiring it's exclusive lock that it needs)