System.Data.SQLite

It would be nice to have support for the shared server mode offered by recent SQLite version since it allows for table level locking. I'm using the attached patch in our application to accomplish this (but it's probably a bit incomplete).

I should have been more verbose about what I'm really asking for (and also partially implementing with the patch). As of version 3.3 SQLite engine gained a new feature - "Shared Cache Mode" - which is in detail described at http://www.sqlite.org/sharedcache.html. This feature allows to share the database cache for all connections from the same thread and among other things modifies the locking semantics. As a part of the this feature the base SQLite package contains an example of a Shared Cache Server implementation where a service thread makes certain SQLite calls and introduces sqlite3_client_* function which call into the service thread (some sort of request queue is involved). The whole point of this excercise is that you can transparently get table-level locking inside a context of a single application which turns out to be pretty usefull for a database englines like SQLite.

The attached patch is a full rewrite of the server code from the SQLite package to work with Windows thread APIs (instead of the POSIX ones). These APIs are exported with the same signatures and semantics as in the *nix version. The next thing it does is to add managed code to the ADO.NET provider to allow usage of these sqlite3_client_* functions instead of the standard sqlite3_* functions and also automatically managing the lifetime of the server thread. From the point of an application coder the only thing they need to change is to add "UseSharedServer=true" to the connection string.

Now let's get to the ugly part ... Since no one asked it, I will ask myself and answer it :-) ... What's the catch? ... The patch implements only the UTF8 version of the code. Adding the UTF16 version wouldn't be hard I guess, but we didn't need it for our application. The second catch is that during some of our testing we have got some issues with database locking in a specific scenario - one of threads tried to insert large number of records within a transaction and at the same time other thread attempted to access the database. At this point the connection timeout should have been enforced, but it wasn't. Exact cause of this bug wasn't determined yet (we didn't really have time to even try finding it to be honest).

If you managed to read this far, congratulations and thanks for listening. :-)

I confess I haven't looked too deeply at the proposed patch just yet, though I did quickly read over it.  There's quite a lot there to digest -- and lots of stuff that would need rigorous testing before incorporating it into the provider.

I will have a closer look at it as time allows, but I can't tell you for certain when the shared cache implementation might make it into the provider.

Robert

 

Just a minor update ... I will start looking at integrating this into the .37 or .38 version of the provider. 

Robert

 

Just a minor update from my side, after few months of testing of the patch one issue came to light. The handling of SQLITE_LOCKED needs to be adjusted to account for the behaviour described on http://www.sqlite.org/sharedcache.html. Otherwise issues like this one will happen: http://svn.emclient.com/mantis/view.php?id=26

Filip

Specifically this line "else if (r == 6 && stmt._command != null) // SQLITE_LOCKED" in SQLite3_SharedServer.Step needs to be changed...

... changed to what? 

Robert

 

Is this patch the best there is?

Right now I'm doing a database-heavy project. One thread is doing a heavy ingest of data while I have 3 others doing nothing but reading - and I don't care whether the readers get the absolute latest data as accuracy isn't important during ingest. I'm running into database locked if my datareader takes too long.

Aside from changing the timeout is there any way to avoid this other than this patch?

My usual reading structure is something along the lines of:

           using (DbTransaction dbTrans = conceptDatabaseConnection.BeginTransaction())

            {
                using (DbCommand cmd = conceptDatabaseConnection.CreateCommand())
                {
                    cmd.CommandText = "SELECT id FROM myTable WHERE myColumn= ?";
                    DbParameter myParam = cmd.CreateParameter();
                    cmd.Parameters.Add(myParam );
                    myParam.Value = myValue;

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            myVarList.Add(reader[0]);
                        }
                    }
                }
                dbTrans.Commit();
            }

From what I understand there's no way in the ADO wrapper to make the reader not lock out my writer. Is this true?

EDIT: On a sidenote - why am I manually having to type html in order to properly format a forum post...

JFYI, the patch in this thread is obsolete, the shared cache mode in the SQLite core now works across multiple threads.

navaraf:

the shared cache mode in the SQLite core now works across multiple threads.

In that case, how do I enable it?

bueller?

There's currently no public way to enable shared cache in the .NET provider.  There are some flags buried in the code to enable it, but shared cache mode has some limitations (namely lack of support for virtual tables) and so I haven't made it mainstream quite yet.

If you want to make it work yourself, you can declare the function yourself, then call it with a non-zero value to enable it per-process.

[DllImport("System.Data.SQLite")]
internal static extern int sqlite3_enable_shared_cache(int enabled);

 

Robert Simpson:

but shared cache mode has some limitations (namely lack of support for virtual tables) and so I haven't made it mainstream quite yet.

SQLite Release 3.6.17 On 2009 Aug 10 (3.6.17)

Remove the restriction on virtual tables and shared cache mode. Virtual tables and shared cache can now be used at the same time.

 

Can you support that method natively now?

 Now that the new version came out, is there a way to enable shared cache support?

 Something like

       "n = UnsafeNativeMethods.sqlite3_enable_shared_cache(1);
        if (n > 0) throw new SQLiteException(n, null);
        flags |= SQLiteOpenFlagsEnum.SharedCache;"

in 'SQLite3.cs Open()' should do the trick.

But we currently have a problem with this approach. Using an encrypted database with shared cache enabled leads to 'Access to protected memory' and 'Corrupted database' exceptions (with multiple threads of course).