System.Data.SQLite

For my app, it would be very useful to have access to the BEGIN EXCLUSIVE command of SQLite.  I'm trying to manage ping-ponging access to a database file between multiple readers and a single writer, and it would be much easier to make the writer process tolerant of timeouts waiting for readers to finish if I could isolate the possibilty of failing to acquire locks to the transaction begin.

Thinking about this some more, esp. how it might make sense to implement it ...

It would be nice if BEGIN EXCLUSIVE could be wedged into the interpretation of IsolationLevel values like BEGIN IMMEDIATE currently is, but I don't see a really good way to do this.  The closest IsolationLevel value to what it does I think would be Snapshot, but that really isn't correct, as it implies that a copy is created of data, and other readers and writers can continue.

The best idea I've come up with is:

• For methods that start a transaction that only take an IsolationLevel value, its interpretation stays the same
• For methods that take the bool deferredLock parameter, replace it with an enum, perhaps called SQLiteLockLevel, with Deferred, Immediate, and Exclusive values
• In line with this, replace the DefaultIsolationLevel property on the connection string (and builder) with a DefaultLockLevel parameter taking one of these enum values
• Add a property to the SQLiteTransaction class exposing the lock level with which it was created

Of course, any of those items that say "replace" introduce a breaking change for code that currently uses the isolation/lock level feature.  It's probably a good idea to keep the old method signatures around for a while for backwards compatibility.  The only tricky one there might be the DefaultIsolationLevel property on the connection string.

I started working on writing up a patch for this for the 1.0.65.0 code, but noticed that the existing methods taking the bool deferredLock parameter are already marked obsolete, which makes me second guess this whole idea, since the argument to keep with the standard API seems like a good one to me.  I'm just not sure how to do this within that standard.

If you're interested, I can finish up the patch and email it.

I hate to suggest breaking compatibility with existing code, but with more reading, the only way I can see to fit this feature into the standard ADO.Net API is to change the interpretation of IsolationLevel values, changing the interpretation of Snapshot to map to BEGIN EXCLUSIVE, and using RepeatableRead to map to BEGIN IMMEDIATE, keeping the mapping of ReadCommitted to the default deferred lock behavior.

I'll stop rambling now and let you think and comment :)

Why not make your own entirely new implementation of SQLiteTransaction into SQLiteExclusiveTransaction in your application and use that instead?

Good question ... only real problem I can see with doing that is that I can't manipulate the _transactionLevel member of the SQLiteConnection.  Rummaging around, however, that only seems to be used outside the SQLiteTransaction class when doing distributed transaction enlistment, which I'm not doing.  I'll give that a go, thanks for the suggestion :)

OK, giving that a shot, and came up with a couple problems:

1. Since I can't access SQLiteConnection._transactionLevel, I can't duplicate any of the state/sanity checks based on that
2. Same problem with sanity checks based on SQLiteConnection._version
3. I can't access the SQLiteConnection._sql member or its type to get the SQLiteBase.AutoCommit property for that sanity check

I think I've worked around #1 and #2 with judicious use of attaching event handlers to the connection.  Short of evil reflection hacks, however, I don't see any way to work around #3.

For anyone else who wants to try this out, here's the code I'm using.  AFAICT, it works fine, but caveat emptor: I've only tested it under normal, correct operations, I've not tested its resiliency in the face of misuse or other errors.  In case the comment is not obvious, I officially release this code into the Public Domain, same as the rest of SQLite :)

using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Threading;
using InterfaceLib;
using System.Diagnostics.CodeAnalysis;

/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * Adapted by Matthew Gabeler-Lee (matt.gabeler-lee@virgininstruments.com)
 *
 * Released to the public domain, use at your own risk!
 ********************************************************/

namespace UtilLib.SQLite
{
    /// <summary>
    /// Hacked up version of SQLiteTransaction that uses BEGIN EXCLUSIVE
    /// </summary>
    public sealed class SQLiteExclusiveTransaction : DbTransaction
    {
        /// <summary>
        /// The connection to which this transaction is bound
        /// </summary>
        private SQLiteConnection connection;
        /// <summary>
        /// Is this transaction still open?
        /// </summary>
        private bool isOpen;

        /// <summary>
        /// Constructs the transaction object, binding it to the supplied connection
        /// </summary>
        /// <param name="connection">The connection to open a transaction on</param>
        internal SQLiteExclusiveTransaction(SQLiteConnection connection)
        {
            this.connection = connection;

            try
            {
                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "BEGIN EXCLUSIVE";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SQLiteException)
            {
                connection = null;
                throw;
            }

            isOpen = true;

            connection.StateChange += connection_StateChange;
            connection.RollBack += connection_RollBack;
            connection.Commit += connection_Commit;
        }

        private void connection_Commit(object sender, CommitEventArgs e)
        {
            OnTransactionDone();
        }

        private void connection_RollBack(object sender, EventArgs e)
        {
            OnTransactionDone();
        }

        private void connection_StateChange(object sender, StateChangeEventArgs e)
        {
            if (e.CurrentState == ConnectionState.Closed || e.CurrentState == ConnectionState.Broken)
                OnTransactionDone();
        }

        private void OnTransactionDone()
        {
            if (!isOpen)
                throw new AssertionFailedException();
            isOpen = false;
            connection.StateChange -= connection_StateChange;
            connection.RollBack -= connection_RollBack;
            connection.Commit -= connection_Commit;
        }

        /// <summary>
        /// Commits the current transaction.
        /// </summary>
        public override void Commit()
        {
            IsValid(true);

            if (isOpen)
            {
                using (SQLiteCommand cmd = connection.CreateCommand())
                {
                    cmd.CommandText = "COMMIT";
                    cmd.ExecuteNonQuery();
                }
            }
            // event handlers on the connection should have updated the isOpen flag by now
            if (isOpen)
                throw new AssertionFailedException();
            connection = null;
        }

        /// <summary>
        /// Returns the underlying connection to which this transaction applies.
        /// </summary>
        public new SQLiteConnection Connection
        {
            get
            {
                return connection;
            }
        }

        /// <summary>
        /// Forwards to the local Connection property
        /// </summary>
        protected override DbConnection DbConnection
        {
            get
            {
                return Connection;
            }
        }

        /// <summary>
        /// Disposes the transaction.  If it is currently active, any changes are rolled back.
        /// </summary>
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                if (IsValid(false))
                {
                    IssueRollback();
                }
            }
            base.Dispose(disposing);
        }

        /// <summary>
        /// Gets the isolation level of the transaction.  SQLite only supports Serializable transactions.
        /// </summary>
        public override IsolationLevel IsolationLevel
        {
            get
            {
                return IsolationLevel.Serializable;
            }
        }

        /// <summary>
        /// Rolls back the active transaction.
        /// </summary>
        public override void Rollback()
        {
            IsValid(true);
            IssueRollback();
        }

        internal void IssueRollback()
        {
            SQLiteConnection cnn = Interlocked.Exchange(ref connection, null);

            if (cnn != null)
            {
                using (SQLiteCommand cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = "ROLLBACK";
                    cmd.ExecuteNonQuery();
                }
            }
        }

        [SuppressMessage("Microsoft.Usage", "CA2208:InstantiateArgumentExceptionsCorrectly", Justification = "Matching behavior of existing SQLite code")]
        internal bool IsValid(bool throwError)
        {
            if (connection == null)
            {
                if (throwError == true)
                    throw new ArgumentNullException("No connection associated with this transaction");
                else
                    return false;
            }

            // we can't access the _version member
            // the StateChange event handler should deal with this
#if CANTACCESSVERSION
            if (connection._version != _version)
            {
                if (throwError == true)
                    throw new SQLiteException((int)SQLiteErrorCode.Misuse, "The connection was closed and re-opened, changes were already rolled back");
                else
                    return false;
            }
#endif
            if (connection.State != ConnectionState.Open)
            {
                if (throwError == true)
                    throw new SQLiteException((int)SQLiteErrorCode.Misuse, "Connection was closed");
                else
                    return false;
            }

            // want:
            // || connection._sql.AutoCommit == true
            if (!isOpen)
            {
                if (throwError == true)
                    throw new SQLiteException((int)SQLiteErrorCode.Misuse, "No transaction is active on this connection");
                else
                    return false;
            }

            return true;
        }
    }
}