locked
SQLite-Net and multiple threads RRS feed

  • Question

  • User59 posted

    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

    Tuesday, December 11, 2012 4:08 PM

All replies

  • User35 posted

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

    Tuesday, December 11, 2012 4:33 PM
  • User59 posted

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

    Tuesday, December 11, 2012 4:34 PM
  • User35 posted

    Or maybe create a service for writing to the database?

    Tuesday, December 11, 2012 4:38 PM
  • User59 posted

    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.

    Tuesday, December 11, 2012 4:44 PM
  • User1011 posted

    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

    Tuesday, December 11, 2012 5:13 PM
  • User59 posted

    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

    Tuesday, December 11, 2012 5:23 PM
  • User1011 posted

    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.

    Tuesday, December 11, 2012 5:30 PM
  • User59 posted

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

    Tuesday, December 11, 2012 5:35 PM
  • User59 posted

    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

    Wednesday, December 12, 2012 10:25 AM
  • User447 posted

    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

    Monday, April 28, 2014 4:15 AM
  • User36573 posted

    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.

    Wednesday, August 27, 2014 1:12 PM
  • User447 posted

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

    Thursday, September 25, 2014 11:49 PM
  • User36573 posted

    @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

    Sunday, September 28, 2014 7:41 AM
  • User5562 posted

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

    Monday, December 1, 2014 6:31 PM
  • User65556 posted

    @AlexFourman's answer:

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

    really fixed SIGSEGV without stacktrace.

    Wednesday, April 15, 2015 3:47 AM
  • User63445 posted

    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!

    Monday, May 18, 2015 8:51 AM
  • User79767 posted

    @AlexFourman's answer:

    Seems to work well. Thanks!!!

    Friday, September 11, 2015 3:57 PM
  • User133676 posted

    I solved it with:

    SQLiteOpenFlags.SharedCache

    Tuesday, February 2, 2016 12:46 PM
  • User36140 posted

    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());
            }
        }
    }
    
    Friday, July 22, 2016 1:29 PM
  • User57571 posted

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

    Monday, September 5, 2016 10:01 PM
  • User36140 posted

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

    Wednesday, September 28, 2016 10:47 AM
  • User161333 posted

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

    Thursday, April 13, 2017 2:14 PM
  • User361784 posted

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

    Wednesday, October 16, 2019 11:31 PM