System.Data.SQLite

While trying to figure out why my SubSonic transactions were behaving strangely, I wrote some "tests" using raw ADO.NET and TransactionScope. I found that in one case the transaction is not rolled back like I would expect it to, and in another one an exception occurs.

Here are four simple examples that only differ very slightly - 1 & 2 use TransactionScope, 3 & 4 use BeginTransaction, and the order of opening the connection & creating the transaction is varied:

    // Example 1
    using (var conn = new SQLiteConnection(@"Data Source=P:\perforce1\users\rs\TestSubSonic\bugs.db"))
    {
        long countStart, countMid, countEnd;
        using (var ts = new TransactionScope())
        {
            conn.Open();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countStart = (long) cmd.ExecuteScalar();
            using (var cmd = new SQLiteCommand(@"INSERT INTO User (Email, RealName, PasswordHash) VALUES (""blah1"", ""X"", ""X"")", conn))
                cmd.ExecuteNonQuery();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countMid = (long) cmd.ExecuteScalar();
        }
        using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
            countEnd = (long) cmd.ExecuteScalar();
    }

    // rolled back OK  --  counts are 0, 1, 0 again

    // Example 2
    using (var conn = new SQLiteConnection(@"Data Source=P:\perforce1\users\rs\TestSubSonic\bugs.db"))
    {
        long countStart, countMid, countEnd;
        conn.Open();
        using (var ts = new TransactionScope())
        {
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countStart = (long) cmd.ExecuteScalar();
            using (var cmd = new SQLiteCommand(@"INSERT INTO User (Email, RealName, PasswordHash) VALUES (""blah2"", ""X"", ""X"")", conn))
                cmd.ExecuteNonQuery();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countMid = (long) cmd.ExecuteScalar();
        }
        using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
            countEnd = (long) cmd.ExecuteScalar();
    }

    // NOT rolled back  --  counts are 0, 1, 1

    // Example 3
    using (var conn = new SQLiteConnection(@"Data Source=P:\perforce1\users\rs\TestSubSonic\bugs.db"))
    {
        long countStart, countMid, countEnd;
        conn.Open();
        using (var t = conn.BeginTransaction())
        {
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countStart = (long) cmd.ExecuteScalar();
            using (var cmd = new SQLiteCommand(@"INSERT INTO User (Email, RealName, PasswordHash) VALUES (""blah3"", ""X"", ""X"")", conn))
                cmd.ExecuteNonQuery();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countMid = (long) cmd.ExecuteScalar();
        }
        using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
            countEnd = (long) cmd.ExecuteScalar();
    }

    // rolled back OK  --  counts are 1, 2, 1

    // Example 4
    using (var ts = new TransactionScope())
    {
        long countStart, countMid, countEnd;
        using (var conn = new SQLiteConnection(@"Data Source=P:\perforce1\users\rs\TestSubSonic\bugs.db"))
        {
            conn.Open();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countStart = (long) cmd.ExecuteScalar();
            using (var cmd = new SQLiteCommand(@"INSERT INTO User (Email, RealName, PasswordHash) VALUES (""blah4"", ""X"", ""X"")", conn))
                cmd.ExecuteNonQuery();
            using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
                countMid = (long) cmd.ExecuteScalar();
        }
        using (var conn = new SQLiteConnection(@"Data Source=P:\perforce1\users\rs\TestSubSonic\bugs.db"))
        using (var cmd = new SQLiteCommand("SELECT COUNT(*) FROM User", conn))
        {
            conn.Open();
            countEnd = (long) cmd.ExecuteScalar();
        }
    }

    // exception - database is locked

Now my question is - is what I am doing in examples 2 and 4 above inherently wrong, or is this a problem with System.Data.SQLite?

You should be able to copy & paste this code into a blank Console project, specify a path to an appropriate SQLite database file, and observe the record counts at the end of each example.