SQLite.NET PCL Busy Exception

Question

We are using the SQLite.NET PCL in a Xamarin application.

When putting the database under pressure by doing inserts into multiple tables we are seeing BUSY exceptions being thrown.

Can anyone explain what the difference is between BUSY and LOCKED? And what causes the database to be BUSY?

Our code uses a single connection to the database created using the following code:

var connectionString = new SQLiteConnectionString(GetDefaultConnectionString(), 
                                                      _databaseConfiguration.StoreTimeAsTicks);
var connectionWithLock = new SQLiteConnectionWithLock(new SQLitePlatformAndroid(), connectionString);

return new SQLiteAsyncConnection (() => { return connectionWithLock; });

Anabasis

Answer 1

So our problem turned out to be that although we had ensured within the class we'd written that it only created a single connection to the database we hadn't ensured that this class was a singleton, therefore we were still creating multiple connections to the database. Once we ensured it was a singleton then the busy errors stopped

What I've take from this is:

Locked means you have multiple threads trying to access the database, the code is inherently not thread safe.

Busy means you have a thread waiting on another thread to complete, your code is thread safe but you are seeing contention in using the database.

Anabasis

How did you do that - code example please? sorry if this is a dumb question! – Malicious

Answer 2

...current operation cannot proceed because the required resources are locked...

I am assuming that you are using async-style inserts and are on different threads and thus an insert is timing out waiting for the lock of a different insert to complete. You can use synchronous inserts to avoid this condition. I personally avoid this, when needed, by creating a FIFO queue and consuming that queue synchronously on a dedicated thread. You could also handle the condition by retrying your transaction X number of times before letting the Exception ripple up.

SQLiteBusyException is a special exception that is thrown whenever SQLite returns SQLITE_BUSY or SQLITE_IOERR_BLOCKED error code. These codes mean that the current operation cannot proceed because the required resources are locked.

When a timeout is set via SQLiteConnection.setBusyTimeout(long), SQLite will attempt to get the lock during the specified timeout before returning this error.

Ref: http://www.sqlite.org/lockingv3.html

Ref: http://sqlite.org/capi3ref.html#sqlite3_busy_timeout

Hector

Answer 3

I have applied the following solution which works in my case(mobile app).

1. Use sqlitepclraw.bundle_green nugget package with SqlitePCL.
2. Try to use the single connection throughout the app.
3. After creating the SQLiteConnection.

Apply busytime out using following call.

var connection = new SQLiteConnection(databasePath: path);
SQLite3.BusyTimeout(connection.Handle, 5000); // 5000 millisecond.

Semiporcelain