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?

Answer 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();
    }
}