Will the page lock be released on committing nested transaction?

Question

It is well known that the database locks taken inside the transaction are released on the end of that transaction. So, in this code..

public static TransactionScope CreateTransactionScope()
{
    return new TransactionScope(TransactionScopeOption.Required,
     new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted });
}

Actually, in this one...

using (DataContext dataContext = new DataContext())
using (TransactionScope rootScope = CreateTransactionScope())
{
    using (TransactionScope nested = CreateTransactionScope())
    {
        Ticket ticket = dataContext.ExecuteQuery<Ticket>(
          "SELECT * FROM Tickets WITH (UPDLOCK) WHERE id={0}", ticketId).First();
        nested.Complete();
    }

    // Will the lock be still ON here? Because I don't need him to be!
}

When exactly the row/page lock (UPDLOCK) will be released - after the disposing the nested transaction or the root one?

score 2 · Accepted Answer · answered Jul 23 '12 at 08:09

It will be released only after the root scope will exit the using block and will dispose.

The best way to learn this kind of stuff is by getting your hands dirty.

Create a simple database with table A and table B, each contains single column, name it "TimeStamp" and create simple console application that inserting timestamp (or any kind of value) and you can play with the transaction options and learn the behaviour.

Will the page lock be released on committing nested transaction?

1 Answers1