System.Data.SQLite

1.0.36.1 has some issues with multiple writer threads, attached is a test harness that demostrates

Sqlite3.Step and Sqlite3.Reset should be checking for both 5 and 6 (SQLITE_LOCKED and SQLITE_BUSY) at the moment its only checking for SQLITE_LOCKED which is probably a less common condition

once this is fixed there is a serious starvation issue with multiple simultaneous writers, this can be resolved by following steps described in http://www.sqlite.org/cvstrac/wiki?p=MultiThreading

(perhaps sqlitetransaction should signal all waiting threads on commit to wake up and start processing)

I will try it out to see if it resolves this ...

I ran your test harness and found no errors, though a starvation problem was apparent.  This is beyond the control of the wrapper and is something that would either have to be addressed internally in the engine, or externally in your application code.  A few notes:

1. The binary is compiled with THREADSAFE=1
2. I've documented thoroughly that no connections are to be shared across threads
3. Windows doesn't require that a connection only be used in the thread it is created in -- and I don't compile with SQLITE_ENABLE_MEMORY_MANAGEMENT.
4. To my knowledge all the user-def functions of SQLite are threadsafe.
5. I don't need to check for SQLITE_BUSY, because I've already set the busy handler which will timeout after 30 seconds (default) without my having to check it and implement my own timeout handling.  I only need to spin on a SQLITE_LOCKED error.
6. In your test harness you explicitly call BEGIN IMMEDIATE and bypass the built-in transaction handling of the wrapper.  However, BEGIN IMMEDIATE is the default behavior of the wrapper.

Robert

Ok, I just had a look at the code

The api sqlite3_busy_timeout creates a vary naive sqlite3_busy_handler, its implementation is in sqliteDefaultBusyCallback, what it does is when you get a sqlite_busy it will sleep for (1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100) msecs (starting from  1 till 100)

The default handler does not have any smarts about what is going on in seperate threads or the db...


if we remove the sqlit_busy_timeout, and  instead implemented  some cross thread comms, and handle sqlite_busy then this can be avoided for single process apps (further more with global events it could be resolved for the entire machine)

what i did

1. comment out: int n = UnsafeNativeMethods.sqlite3_busy_timeout_interop(_sql, nTimeoutMS);
2. comment out: UnsafeNativeMethods.sqlite3_sleep_interop((uint)rnd.Next(1, 250));
3. create:  internal static AutoResetEvent resetEvent = new AutoResetEvent(false); (on sqlite3.cs)
4. add:  resetEvent.WaitOne(rnd.Next(1, 250),true); instead of the sleep interop
5. signal the reset event whenever a transaction is commited

this mostly resolves the stravation issue

whats left to do

1. instead of waiting random times, wait using the following backoff (1, 2, 5, 10, 15, 20, 25, 25,  25,  50,  50, 100)

2. create an array of signalling reset events, 1 for each database

3. consider if global signalling is worth looking into

4. register a sqlite3_commit_hook to handle the signalling

Hope this helps

Cheers
Sam

one more thing.

there is no way to set the timeout for a sqlitetransaction (and as we are starting transactions immediatly they acquire locks and may take time)

would recommend adding an overload 

The starvation issue is difficult and could only be partially mitigated ... only the writers on the same machine could be sync'd this way ... which admittedly will handle most scenarios.  Multiple machines hitting the same db could not be sync'd or prevented from starvation.

Another big issue is the multiple-database issue.  The wrapper has no way of knowing how many databases are attached to a connection and which database(s) your prepared statements are impacting.  Any mutex mechanism I'd implement would be even more draconian than SQLite's locking mechanisms.

One option would be to add this kind of event signaling code into the engine itself where some additional checks could be made.  I just hate adding more stuff to the engine and veering further away from the core codebase.

Robert

 

maybe we should ask richard hipp what he recommends?

I don't know the SQLite engine internals, nor SQLite C API. But I'm interested in knowing when a starvation problem may occur and how to avoid or mitigate it.

I'm developing a multithreaded application, an ASP.NET application which uses a SQLite database. The ASP.NET application runs on a single server, single process configuration, no web garden nor web farm. Is it possible that my application experiences starvation issues? In which circumstances?

Essentially the starvation issue can arise when two or more threads are trying to write to the database simultaneously.  One thread will be forced to wait while the other thread finishes, and there's no method of FIFO to fairly determine which thread gets the next write access.  Hence, whichever thread happens to have the CPU gets an unfair advantage in that it usually gets to hog the database until Windows finally manages to context switch at just the right time to allow the other waiting thread to have write access to the database.

So far the proposals designed to mitigate the starvation have been wrapper-centric, which doesn't work well at all and would probably make things worse.  The alternative is to modify the core engine, which I am hesitant to do unless I can find a clean way to implement those changes without disrupting the original source code too badly.

Robert

 

re: work around

a filthy work around is adding a sleep 250ms after commiting any transaction that writes to the db.

you  will only experience this issue if you are writing stuff to the db very aggressivley from two threads (while commiting multiple transactions)

So, if I have understood correctly, the starvation problem will occur only on heavy loaded systems. If only one thread is waiting, when the thread which is working finishes, the waiting thread gets the CPU and does its work, no starvation problem occurs. But, if while one thread A is waiting, another threads B comes in, it is possible that  thread B enters into the database before thread A.  The same may occurs with subsecuent threads, so it is possible that thread A never enters into the database.

What happens when a thread is starved? do you get a command timeout error?

Can I use synchonization primitives to avoid starvation?

For example, for a single machine, single process, mutithreaded application

static class SyncObjects
{
    public static object SomeDatabase= new object();
    public static object OtherDatabase= new object();
}

public class SomeDatabaseManagement
{

    public void WriteSomething()
    {
       using (DbConnection cn = CreateConnection())
       {
          // prepare commands
          ...

          lock (SyncObjects.SomeDatabase)
          {
              using (DbTransaction tx = cn.BeginTransaction())
             {
                  DoWork();
                  tx.Commit();
             }
          }
       }
    }
}

If I'm right, lock keyword uses a monitor internally. And a monitor object holds a FIFO list of all waiting threads. So no starvation will occur if I use this pattern. Am I right?

The question is:

Is it worth to syncrhonize yourself database transactions or is it acceptable to get some command timeouts? How many command timeouts will I get?. How heavy the load has to be to begin getting starved threads?

if a thread is starved you will get a timeout ... the locking will help somewhat, but you may need to lock around readers as well, otherwise your readers may starve casue of writers...

it really depends on you load, you will experience this if you have multiple writer threads writing aggresively - add logging around your timeouts (and maybe some profiling for the avg time spent for reads) and then you should see if it is affecting your app