SQLite-Net and multiple threads

NicWiseNicWise NZInsider, University mod

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

Posts

  • TomOpgenorthTomOpgenorth CAXamarin Team Xamurai

    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".)

  • NicWiseNicWise NZInsider, University mod

    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 :)

  • TomOpgenorthTomOpgenorth CAXamarin Team Xamurai

    Or maybe create a service for writing to the database?

  • NicWiseNicWise NZInsider, University mod

    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.

  • ledzledz USMember
    edited December 2012

    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

  • NicWiseNicWise NZInsider, University mod

    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

  • ledzledz USMember

    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.

  • NicWiseNicWise NZInsider, University mod

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

  • NicWiseNicWise NZInsider, University mod
    edited December 2012

    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.7824Le-royStaines.7824 NZMember ✭✭✭

    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

  • Le-royStaines.7824Le-royStaines.7824 NZMember ✭✭✭

    @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)

  • @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

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

  • BenJonesBenJones GBMember

    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!

  • SabdullahSabdullah USMember ✭✭

    @AlexFourman's answer:

    Seems to work well. Thanks!!!

  • JaviPintorJaviPintor USMember

    I solved it with:

    SQLiteOpenFlags.SharedCache

  • PhilWilksPhilWilks GBMember

    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());
            }
        }
    }
    
  • 15mgm1515mgm15 USMember ✭✭✭

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

  • PhilWilksPhilWilks GBMember

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

  • RavindraKumarRavindraKumar USMember ✭✭

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

Sign In or Register to comment.