System.Data.SQLite

I thought that if one connection was writting to the database, no other connections could read the database. However I realized I was wrong. The following code demostrates it:

using (SQLiteConnection cn1 = new SQLiteConnection("Data Source=|DataDirectory|Test.db"))
using (SQLiteConnection cn2 = new SQLiteConnection("Data Source=|DataDirectory|Test.db"))
{
    cn1.Open();

    using (SQLiteCommand cmd = new SQLiteCommand())
    {
        cmd.Connection = cn1;
        cmd.CommandText = @"
                CREATE TABLE TestTable(Id INTEGER PRIMARY KEY, Value TEXT);
                INSERT INTO TestTable(Id, Value) VALUES (1, 'Value 1');
                INSERT INTO TestTable(Id, Value) VALUES (2, 'Value 2');
        ";
        cmd.ExecuteNonQuery();
    }


    cn2.Open();
    using (SQLiteTransaction tx1 = cn1.BeginTransaction())
    using (SQLiteCommand cmd1 = new SQLiteCommand("UPDATE TestTable SET Value=@Value WHERE Id = @Id", cn1))
    {
        cmd1.Parameters.AddWithValue("@Value", "Other value");
        cmd1.Parameters.AddWithValue("@Id", 1);
        cmd1.ExecuteNonQuery();

        using (SQLiteCommand cmd2 = new SQLiteCommand("SELECT Value FROM TestTable WHERE Id=@Id", cn2))
        {
            cmd2.Parameters.AddWithValue("@Id", 1);
            string value = (string)cmd2.ExecuteScalar();
            Console.WriteLine("Value: {0}", value);
        }

        tx1.Commit();
    }          

}

First I create a table and populate it with two rows:

CREATE TABLE TestTable(Id INTEGER PRIMARY KEY, Value TEXT);
INSERT INTO TestTable(Id, Value) VALUES (1, 'Value 1');
INSERT INTO TestTable(Id, Value) VALUES (2, 'Value 2');
 

Second I start tx1 transaction on connection cn1 and update first row:

UPDATE TestTable SET Value='Other Value' WHERE Id = 1 

Third, while tx1 is running, I read Value field of first record using cn2 connection,  and show the result on the console:

SELECT Value FROM TestTable WHERE Id=1 

 Amazingly, it shows 'Value1'. Not only it does not wait for the transaction to complete, but it reads the last committed value, i.e it behaves like in snapshot isolation level.

 Forth, I commit tx1 transaction.

What do you think ?

The same occurs when doing it on two threads.

 private void button2_Click(object sender, EventArgs e)
{
    using (SQLiteConnection cn1 = new SQLiteConnection("Data Source=|DataDirectory|Test.db"))
    {
        cn1.Open();

        using (SQLiteCommand cmd = new SQLiteCommand())
        {
            cmd.Connection = cn1;
            cmd.CommandText = @"
                    CREATE TABLE TestTable(Id INTEGER PRIMARY KEY, Value TEXT);
                    INSERT INTO TestTable(Id, Value) VALUES (1, 'Value 1');
                    INSERT INTO TestTable(Id, Value) VALUES (2, 'Value 2');
            ";
            cmd.ExecuteNonQuery();
        }


        using (SQLiteTransaction tx1 = cn1.BeginTransaction())
        using (SQLiteCommand cmd1 = new SQLiteCommand("UPDATE TestTable SET Value=@Value WHERE Id = @Id", cn1))
        {
            cmd1.Parameters.AddWithValue("@Value", "Other value");
            cmd1.Parameters.AddWithValue("@Id", 1);
            cmd1.ExecuteNonQuery();

            Thread t = new Thread(new ThreadStart(ReadValue));
            t.Start();

            MessageBox.Show("Thread started");

            tx1.Commit();
        }         

    }
}

void ReadValue()
{
    using (SQLiteConnection cn2 = new SQLiteConnection("Data Source=|DataDirectory|Test.db"))
    using (SQLiteCommand cmd2 = new SQLiteCommand("SELECT Value FROM TestTable WHERE Id=@Id", cn2))
    {
        cn2.Open();
        cmd2.Parameters.AddWithValue("@Id", 1);
        string value = (string)cmd2.ExecuteScalar();
        string msg = string.Format("Value: {0}", value);
        MessageBox.Show(msg);
    }
}

What will happen with two processes?

When you start a transaction with SQLite.NET and the BeginTransaction() call you get a "reserved" lock on the database  which means other calls can read data from the databases as it exists excluding your transaction, but no other calls can start a different transaction.  When you commit, and only for the duration of the commit, your "reserved" lock is upgraded to an "exclusive" lock and for this very brief time (usually less than 1ms) nobody can read from the database.

HTH,

Sam