SQLite Transaction not committing

Question

For an application we are developing we need to read n rows from a table and then selectively update those rows based on domain specific criteria. During this operation all other users of the database need to be locked out to avoid bad reads.

I begin a transaction, read the rows, and while iterating on the recordset build up a string of update statements. After I'm done reading the recordset, I close the recordset and run the updates. At this point I commit the transaction, however none of the updates are being performed on the database.

 private static SQLiteConnection OpenNewConnection()
        {

        try
        {
            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = ConnectionString;//System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            conn.Open();
            return conn;
        }               
        catch (SQLiteException e)
        {
            LogEvent("Exception raised when opening connection to [" + ConnectionString + "].  Exception Message " + e.Message);
            throw e;
        }
    }

    SQLiteConnection conn = OpenNewConnection();
            SQLiteCommand command = new SQLiteCommand(conn);
            SQLiteTransaction transaction = conn.BeginTransaction();
// Also fails           transaction = conn.BeginTransaction();
            transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            command.CommandType = CommandType.Text;
            command.Transaction = transaction;
            command.Connection = conn;
            try
            {
                string sql = "select * From X Where Y;";
                command.CommandText = sql;
                SQLiteDataReader ranges;

                ranges = command.ExecuteReader();
                sql = string.Empty;
                ArrayList ret = new ArrayList();
                while (MemberVariable > 0 && ranges.Read())
                {
                    // Domain stuff

                    sql += "Update X Set Z = 'foo' Where Y;";
                }
                ranges.Close();
                command.CommandText = sql;
                command.ExecuteNonQuery();
                                // UPDATES NOT BEING APPLIED
                transaction.Commit();
                return ret;

            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                transaction.Dispose();
                command.Dispose();
                conn.Close();
            }

            return null;

If I remove the transaction everything works as expected. The "Domain stuff" is domain specfic and other than reading values from the recordset doesn't access the database. Did I forget a step?

Solution 1

When you put a breakpoint on your transaction.Commit() line do you see it getting hit?

Final answer:

SQLite's locking does not work like you're assuming see http://www.sqlite.org/lockingv3.html. Given that, I think you're having a transaction scoping issue which can be easily resolved by reorganizing your code as such:

string selectSql = "select * From X Where Y;";      
using(var conn = OpenNewConnection()){
    StringBuilder updateBuilder = new StringBuilder();

    using(var cmd = new SQLiteCommand(selectSql, conn))
    using(var ranges = cmd.ExecuteReader()) {
        while(MemberVariable > 0 && ranges.Read()) {
            updateBuilder.Append("Update X Set Z = 'foo' Where Y;");
        }
    }

    using(var trans = conn.BeginTransaction())
    using(var updateCmd = new SQLiteCommand(updateBuilder.ToString(), conn, trans) {
        cmd.ExecuteNonQuery();
        trans.Commit();
    }
}   

Solution 2

Additional notes regarding some comments in this post/answer about transactions in SQLite. These apply to SQLite 3.x using Journaling and may or may not apply to different configurations - WAL is slightly different but I am not familiar with it. See locking in SQLite for the definitive information.

All transactions in SQLite are SERIALIZABLE (see the read_uncommitted pragma for one small exception). A new read won't block/fail unless the write process has started (there is an EXCLUSIVE/PENDING lock held) and a write won't start until all outstanding reads are complete and it can obtain an EXCLUSIVE lock (this is not true for WAL but the transaction isolation is still the same).

That is the entire sequence above won't be atomic in code and the sequence may be read(A) -> read(B) -> write(A) -> read(B), where A and B represent different connections (imagine on different threads). At both read(B) the data is still consistent even though there was a write in-between.

To make the sequence of code itself atomic a lock or similar synchronization mechanism is required. Alternatively, the lock/synchronization can be created with SQLite itself by using a locking_mode pragma of "exclusive". However, even if the code above is not atomic the data will adhere to the SQL serializable contract (excluding a serious bug ;-)

Happy coding

---

See Locking in SQLite, SQLite pragmas and Atomic Commit in SQLite

Comments

[MikeP]

yes and the ExecuteNonQuery call returns the correct number of modified rows.

[JeffreyABecker]

Lets take a step back: Why are you opening the transaction so early?

[MikeP]

The table being read and written is the same table and all updates to it must be sequential between different users. The app has to block additional reads until the current request has been written.

[JeffreyABecker]

Ahh, that isnt how SQLite transactions work. It wont block until it actually tries to write data.

[MikeP]

According to the sqlite documentation thats how exclusive transactions work. Granted I'm not using the built in sqlite transactions.

[JeffreyABecker]

Internally System.Data.SQLite just issues a "BEGIN TRANSACTION" command when you start a transaction. So you will be using the built in transactions. However just because you issue a "BEGIN TRANSACTION" statement doesn't mean you'll block other readers. (see sqlite.org/lockingv3.html)