SQLite-Net and multiple threads

NicWise

Hi there

I'm using (System.Threading.)Tasks to create database records in the background, and reading in the main UI thread (sometimes - sometimes reading in threads too) to read from the database to get stuff on screen.

Most of the time, this is ok, but from time to time, I get errors saying the connection is being blocked, or I try to load a record, it fails (which is fine), I create it, and I get a consistency error because the record exists (which I just checked for....)

Is there anything special I need to do in sqlite-net / sqlite on Android? It appears to be worse on SGS3, but this happens from time to time on the emulator, too (but never on my old Nexus 7 or Samsung Ace)

On iOS, I "solved" this by making sure I open (and close) a connection within the thread, which I'm doing here too (so never open in one thread, then close or use in another). Is there a magic switch in sqlite to say "make it work multithreaded"? I found the .Config method, but I get a strange error when I use it:

//do this before I open a connection
SQLite3.Config(SQLite3.ConfigOption.MultiThread);

results in:

(21) misuse at line 112746 of [00bb9c9ce4]

Which SO tells me is usually related to threading.

Anyone have any ideas / experience?

Thanks

Nic

TomOpgenorth

IIRC, SQLite is thread safe, but two cannot be simultaneously accessed by multiple threads. Using SQLite in Multiple Threads

(Or as @jonp put it "You don't".)

NicWise

Thanks @topgenorth - That sounds about what I'm seeing.

Now to work out how I can block one while the other finishes. Shouldn't be too hard - mutex or semaphore should do if I can remember which one it is

TomOpgenorth

Or maybe create a service for writing to the database?

NicWise

yeah, that might work. It is all quite simple tho, and 99% of it is serialized - login, load a list, get the list, show the list. Add to the list, get the list, show the list etc.

So... it really shouldn't be failing at all, but it appears it is - one one or 2 models of phone.

ledz

From version 3.3.1 onward, it is safe to use the same database handle in different threads, as long as there are no locks.
What you cannot do is try to open the same database in different threads, but if you share the handle, it's ok. CoolStorage does this well. Context is initialized when process is started and handle is shared across threads.

The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized.

If you're interested in taking a look at CoolStorage
http://viciproject.com/wiki/projects/coolstorage/home

NicWise

Thanks @ledz - I might have a look at that. I need it to run in iOS too, and Iv'e used sqlite-net everywhere else so far - hence started with that.

I always thought it was safe to use (blocking/busy etc not withstanding) if you created and used and closed on the same thread.

I'll have a look at the CS storage and see what they do. Thanks

ledz

Yeah I also started thinking that, but ended bumping my head on it because opening a new connection locks the db file. But if you only open the file once and share the handle you should be ok.

NicWise

Well, on the plus side, thats a REALLY easy change to make - I only create the connection in one place.

NicWise

I ended up doing this:

For reading, I open a new connection most of the time, and close it right away.

For the writes (which also does a few reads), I open a connection once, then pass it around, which I wasn't doing before.

So far, so good. Also updated to the latest sqlite-net (thanks Frank!) which does UpdateOrInsert, which gets rid of my main issue.

Thanks for the help everyone

Le-royStaines.7824

Here's how I got it to work (and get around the random SIGSEV errors)..

Create a static property to access a single instance of the connection (yes; a STATIC instance.. read up about it here: touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection)

public class Data {
            private static SQLite.SQLiteConnection _connection;
            public static SQLite.SQLiteConnection connection
            {
                get {
                    if (_connection == null) {
                        SQLite3.Config (SQLite3.ConfigOption.Serialized);
                        _connection = new SQLite.SQLiteConnection (Data.PathToDatabase);
                    }
                    return _connection;
                }
            }
}

Then everywhere you have a call to the database, encapsulate that code with a lock.

...
lock (Data.connection) {
    return (from a in Data.connection where ... select a);
}
...

or..

...
lock (Data.connection) {
    Data.connection.Update(...);
}
...

You get the gist!

Hope this helps. -LR

AlexFourman

Wanted to add some input to Le-royStaines.6222 post.
SQLite3.Config (SQLite3.ConfigOption.Serialized); _connection = new SQLite.SQLiteConnection (Data.PathToDatabase);
Is probably not the right way to go because SQLite3.Config (SQLite3.ConfigOption.Serialized) is for start time mode selection which is not the case here.
Instead I think you should go with just
new SQLiteConnection (Path,SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex , true);

Setting FullMutex flag is like setting the Serialized ConfigOption as mentioned in the sqlite docs

One other thing, using a lock is an overkill, you could have sqlite in single thread mode if you are going to use it that way, imo it really hurts performance (on the other hand I can't be sure how good is the porting has been done to .Net). If you're doing everything right you should be able to do without locking yourself.

Since using it that way I was able to avoid all exceptions and conflicts.

Le-royStaines.7824

@AlexFourman using your option when I try to create the database it throws an error

Could not open database file: /data/data/.com....db (Misuse)

AlexFourman

@Le-royStaines.6222 well it's nothing I can reproduce by just creating a DB, maybe it's something that you do with the connection afterwards?
In any case here is a sample project that only creates the DB -> a table -> and inserts several records -> then prints them out. It works fine with my environment, maybe you could give it a look.
I would like to know if there is anything wrong with my approach as I'm very dependent on it now

The demo project: DatabaseDemo

ChristopherStaerkel

@AlexFourman: thanks a bunch - after trying countless combinations of locking connections and different connections flags, the problems seem to have disappeared with your solution!

JunZhang.3823

@AlexFourman's answer:

new SQLiteConnection (Path,SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex , true);

really fixed SIGSEGV without stacktrace.

BenJones

I used both to fix my problem with SIGSEGV issues. Regardless of what really fixes the issue I think it's good practice to lock your tables if you're imploring multiple threads todo database work. At the end of the day sqlite3 does not support concurrency, so locking is necessary.

Great answers guys. We all really appreciate the info!

Sabdullah

@AlexFourman's answer:

Seems to work well. Thanks!!!

JaviPintor

I solved it with:

SQLiteOpenFlags.SharedCache

PhilWilks

JaviPintor is correct, I think SQLiteOpenFlags.SharedCache is the right way to solve this.

The following code works like a charm, taking about 20 seconds to complete on an iPhone 6. Interestingly the main thread seems to be given priority, and you can see them having their little race in the console.

const int loops = 1000;

public void DatabaseThreadSafetyTest()
{
    var backgroundThread = new Thread(new System.Threading.ThreadStart(() =>
    {
        for (int i = 1; i <= loops; i++)
        {
            Console.WriteLine("Background thread loop " + i);
            using (var db = new SQLiteConnection(DbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache)) {
                db.Insert (new MyClass());
            }
        }
    }));
    backgroundThread.Start();

    for (int i = 1; i <= loops; i++)
    {
        Console.WriteLine("Main thread loop " + i);
        using (var db = new SQLiteConnection(DbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache)) {
            db.Insert (new MyClass());
        }
    }
}

15mgm15

@JaviPintor Thanks for the info it helped me resolve the issue.

PhilWilks

Forget that. SQLiteOpenFlags.SharedCache makes it far less likely that a lock problem will occur, but it can still happen.

RavindraKumar

Thanks @AlexFourman !!
It works for me as well .

Esaavedra89

@AlexFourman said:
...
new SQLiteConnection (Path,SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex , true);
...

I know that this post is old, but actually I have this problem and I can't find a solution to this in Android.

Originally, I was using:

var conn = new SQLite.SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache);

but, with this way I was getting the exception "locked" or "Database is locked".

Then I started to use the AlexFourman solution:

new SQLiteConnection (Path,SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex , true);

and after that I am getting the exception: "Busy"

So I'm really frustrating with this issue.

In my case, my app have concurrency with the DB because I have a service getting information from the API and updating the DB.

This is part of my code:

Android:

    public SQLite.SQLiteConnection ObtenerConexion()
            {
                // Nombre de la BD.
                var sqliteFileName = "Nbs.db3";
                // Ruta de la BD.
                string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                // Combina rutas.
                var path = Path.Combine(documentsPath, sqliteFileName);
                // Conexión.
                var conn = new SQLite.SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex, true);

                return conn;
            }

Shared:

public static SQLiteConnection ObtenerConexion()
        {
            // Obtenemos la conexión con las plataformas.
            conn = DependencyService.Get<AccesoDatosI>().ObtenerConexion();

            return conn;
        }

Actually I don't have a single connection, because I'm using BeginTransaction, Commit, Close and Dispose.

If I use a single connection I start to get the exc that can't create a new Transaction if I am in a Transaction.

So, I need some help here guys pls.