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

Answer 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

友情链接