Nested Transactions for System.Data.SQLite with Savepoints

• Download source - 1.3 KB
• Download demo - 14.9 MB

Introduction

SQLite does not inherently allow nested transactions. However, it does allow for a transaction to be marked with intermediate savepoints, which emulate nested transactions to a degree. The documentation explaining how all this works can be found here^.

Background

Although SAVEPOINT commands can be issued directly on a database connection without a current transaction, doing so implicitly creates a transaction and alters the way the RELEASE command works. In order to alleviate any confusion, the module is set up so that Savepoints can only be created within the context of a transaction. The resulting updates to the database are therefore only written when a COMMIT is issued, and the entire transaction is aborted if a ROLLBACK is issued, regardless of any Releases or Rollbacks of Savepoints within the transaction. This is consistent with the way transactions work in general (with or without Savepoints).

Using the Code

All of the code required is in a single .cs file. Rather than putting it into a class library, just include the file into your projects and start using it (although you may build it into a separate library if you so desire). In fact, the code all exists in a single static extension class. There are no special objects as far as usage of the extension is concerned.

Most of the manipulation of Savepoints is handled in extension methods on the SQLiteTransaction object. There are four extension methods defined for the SQLiteTransaction object. There are also two BeginTransaction extension methods for the SQLiteConnection object, that take an additional string savepointName parameter, and automatically add the named Savepoint to the newly created Transaction.

It is important to note that all Savepoint names are case-insensitive, and do not have to be unique. Any Release or Rollback of a Savepoint will act upon the most recent one with a matching name.

The four extension methods defined for the SQLiteTransaction object are:

• void AddSavepoint(string savepointName)

  This method, as its name suggests, creates a new Savepoint within the Transaction.

• void ReleaseSavepoint(string savepointName)

  I think the best description of how this works is the 2^nd definition in the SQLite documentation:

  Another view of RELEASE is that it merges a named transaction into its parent transaction, so that the named transaction and its parent become the same transaction. After RELEASE, the named transaction and its parent will commit or rollback together, whatever their fate may be.

  This means that all commands within the Savepoint that have not been previously rolled back are effectively appended to the commands immediately preceding the Savepoint, and the released Savepoint no longer exists in the transaction. Any Savepoints following the one being released, that have not been previously been released or rolled back, are also automatically released at this time.

• void RollbackToSavepoint(string savepointName)

  This method rolls back all commands issued following the named Savepoint, even if those commands include Savepoints that have previously been Released. The named Savepoint itself, however, still exists. Any commands issued to the transaction after the Rollback are still considered to be a part of that Savepoint.

• void RollbackAndRelease(string savepointName)

  This command encapsulates both a Rollback and a Release, issued in succession. When issued like this, the transaction is returned to the state it was in immediately before the named Savepoint was created.

Points of Interest

Performing a SQLiteTransaction.Commit(), SQLiteTransaction.Rollback(), or closing or disposing the underlying connection, automatically releases the list of Savepoints from the transaction. This is internally managed by attaching event handlers on the connection's Commit, Rollback, StateChange, and Disposed events. I did find out the hard way, however, that it was important to remove these event handlers from the list when disposing of the list. Because the events were attached to connection object, a new transaction created on that connection was firing the events within deleted lists.

I also learnt that Stack enumerators go from last to first item (i.e. the order the items will be removed, and not the order in which they were entered).