System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: e896d9554001c5f082bade8ad1ccad02fd747736
Title: Attempt to create read-only SQLiteBlob throws an exception when there are also writers on the same table
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Data_Reader Resolution: Works_As_Designed
Last Modified: 2018-02-19 18:38:17
Version Found In: 1.0.107.0
User Comments:
anonymous added on 2018-02-19 09:29:08:
You keep getting random 'database is locked' exceptions when SQLiteBlob.Create is invoked under certain conditions. I found that this happens when you have writer that tries to insert rows on different connection while you try to create a read-only SQLite blob.

Note that this only happens for read-only blobs (isWriteableBlob = false). 
Workaround for now is to avoid using read-only blobs or call dataReader.GetBlob inside a transaction.

The code below reproduce the issue


--------------------------------------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            SQLiteConnection connection = new SQLiteConnection(@"Data Source=c:\temp\mydb.db;Version=3;");
            connection.Open();
            string sql = string.Empty;
            bool isWriteableBlob = false;

            // comment this if you already have the table created
            CreateTestTable(connection);

            sql = "INSERT INTO Test(MyBlob, SecondBlob) VALUES(zeroblob(20000000), zeroblob(9000000))";
            using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
            {
                cmd.ExecuteNonQuery();
            }

            // SNIPPET HOW TO DO Incremenal IO with blob
            // NOTE: You must select RowID along with the blob column to work
            Task.Run(() =>
            {
                while (true)
                {
                    SQLiteConnection connection2 = new SQLiteConnection(@"Data Source=c:\temp\mydb.db;Version=3;");
                    connection2.Open();
                    var insert = "INSERT INTO Test(MyBlob, SecondBlob) VALUES(zeroblob(200), zeroblob(900))";
                    using (SQLiteCommand cmd = new SQLiteCommand(insert, connection2))
                    {
                        cmd.ExecuteNonQuery();
                    }
                    Thread.Sleep(10);
                }
            });

            for (int i = 0; i < 1000; i++)
            {
                sql = "SELECT Id, MyBlob FROM Test WHERE Id = 1";
                // Incremental READ
                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    using (var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                    {
                        reader.Read();
                        using (var blob = reader.GetBlob(1, isWriteableBlob))
                        {
                            byte[] byteArr = new byte[100];
                            blob.Read(byteArr, 100, 0);
                            blob.Close();
                        }
                    }
                }

                sql = "SELECT Id, SecondBlob FROM Test WHERE Id = 1";
                // Incremental READ
                using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
                {
                    using (var reader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                    {
                        reader.Read();
                        using (var blob = reader.GetBlob(1, isWriteableBlob))
                        {
                            byte[] byteArr = new byte[100];
                            blob.Read(byteArr, 100, 0);
                            blob.Close();
                        }
                    }
                }
            }

            connection.Close();
        }

        private static void CreateTestTable(SQLiteConnection connection)
        {


            string sql = @"CREATE TABLE Test (
    Id INTEGER PRIMARY KEY,
    MyBlob BLOB, SecondBlob BLOB)";

            using (SQLiteCommand cmd = new SQLiteCommand(sql, connection))
            {
                cmd.ExecuteNonQuery();
            }
        }
    }
}

mistachkin added on 2018-02-19 12:31:34:
At first glance, this seems like a case where "database is locked" is a
legitimate outcome.

I'm going to attempt to confirm my suspicions here; in the meantime, have you
tried using the WAL journal mode?

mistachkin added on 2018-02-19 14:04:40:
Based on my tests so far, it appears that this behavior is by design.

The writing thread is preventing the sqlite3_blob_open() API from opening a
cursor to the table.

It appears that opening the blob will sometimes succeed, depending on which
thread obtains the necessary locks first.

mistachkin added on 2018-02-19 14:14:33:
Confirmed, this is not a bug.

anonymous added on 2018-02-19 18:28:27:
What is the correct way to create read-only blob then? Having connection writing in another thread is very common scenario. I don't see how I can create read-only blob without a risk for getting database locked exception?

mistachkin added on 2018-02-19 18:38:17:
One possibility is to catch the SQLiteException, check if the error code is
"Busy", and retry the operation.