SQLite.Net ORM with Task Parallel Library in Xamarin

I've recently had the opportunity to work on a Xamarin.iOS project with Envoy. Having previously built iOS applications with Objective C I've been enjoying the chance to investigate techniques to share code across platforms.

With Xamarin, you can share some common code between Android and iOS projects if you structure it in a certain way. One technique that is commonly used to share code is to use the SQLite.NET ORM with SQLite rather than using Core Data or other device specific data access API's. Xamarin themselves have used this for an application which they've open sourced as example code: MWC 2012 

In that application, they create a subclass of SQLiteConnection with a static constructor that initialises a static variable to hold a reference to itself, as a kind of singleton. I followed this pattern in my code, however I've since discovered that this is perhaps not the best approach in all circumstances.

My application makes use of the Task Parallel Library to do some web service calls and subsequent database updates on a background thread. Much like the MWC sample I wrapped my calls to the database with a lock so that multiple threads won't update the database at once, and the application appeared to work correctly. However at various stages during my testing I came across seemingly random crashes, where the application which had been working fine would crash out of the blue with an error similar to the following:

mono-rt: Stacktrace:

mono-rt: at <0xffffffff>

mono-rt: at (wrapper managed-to-native) SQLite.SQLite3.Prepare2 (intptr,string,int,intptr&,intptr)

mono-rt: at SQLite.SQLite3.Prepare2 (intptr,string)

mono-rt: at SQLite.SQLiteCommand.Prepare ()

mono-rt: at SQLite.SQLiteCommand.ExecuteNonQuery ()

mono-rt: at SQLite.SQLiteConnection.Execute (string,object[])

This isn't particularly helpful and originally made me worried that perhaps the SQLite.NET ORM wasn't stable. However after some more research I've discovered that others were having the same problem and that they believed it was caused by the SQLite connection being accessed on different threads, even if it's not at the same time. When you think about it, of course this is an issue - the subclass of SQLiteConnection shouldn't be a singleton, it's a connection similar to those seen in ADO.NET and should be thread specific and closed when not needed. I believe the MWC application musn't have ran into this issue as they were only doing updates to the database on certain threads, not leaving it up to the Task Parallel Library.

So now, instead of having a singleton in my SQLite Connection, I'm using it in the IDisposable fashion it is designed to be used so that a connection is opened and closed with each query.

using (var db = MyDatabse.NewConnection())
{
    db.Insert(itemToInsert);
}

I've since stopped having the random application crashes. Hopefully this helps someone out who is having the same issue.