Row blocked or table blocked when using a TransactionScope c# object?

Question

I have the following code:

using (TransactionScope ts = new TransactionScope())
 {
      DataAccess da = new DataAccess();
      if (da.UpdateRecord(recordId))
      {
           ts.Complete();
      }
 }

what happens when my code is executing "da.UpdateRecord(recordId)", the row with id = "recordId" is blocked for the rest of the requests until I execute "ts.Complete" ? or the entire table is blocked ?

Thanks in advance, German.

Answer 1

It depends on the Database. If you are using SQL Server, then any row or table level lock is released when ts.Complete(); is executed.

Regarding whether it will be a row level lock or higher, SQL Server will not necessarily default to row level locking - it may use page level or table level locks.

The default isolation level of a new TransactionScope() object is read serializable. This may not be what you need for your situation and also this default is allegedly prone to deadlocks and not considered useful in general.

The TransactionScope’s default constructor defaults the isolation level to Serializable and the timeout to 1 minute. In SQL Server SERIALIZABLE transactions are rarely useful and extremely deadlock-prone.

Source: MSDN Blog - Using new TransactionScope() Considered Harmful