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
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".)
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
Or maybe create a service for writing to the database?
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.
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.
If you're interested in taking a look at CoolStorage
http://viciproject.com/wiki/projects/coolstorage/home
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
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.
Well, on the plus side, thats a REALLY easy change to make - I only create the connection in one place.
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
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)
Then everywhere you have a call to the database, encapsulate that code with a lock.
or..
You get the gist!
Hope this helps. -LR
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.
@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!
@AlexFourman's answer:
new SQLiteConnection (Path,SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.FullMutex , true);
really fixed SIGSEGV without stacktrace.
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!
@AlexFourman's answer:
Seems to work well. Thanks!!!
I solved it with:
SQLiteOpenFlags.SharedCache
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.
@JaviPintor Thanks for the info it helped me resolve the issue.
Forget that. SQLiteOpenFlags.SharedCache makes it far less likely that a lock problem will occur, but it can still happen.
Thanks @AlexFourman !!
It works for me as well .
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:
Shared:
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.