System.Data.SQLite

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

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?

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.

Confirmed, this is not a bug.

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?

One possibility is to catch the SQLiteException, check if the error code is
"Busy", and retry the operation.