SQLite - System.Data.SQLite.SQLiteException - database is locked

Posted by Blake on 11/25/2014
)

Scenario:

You receive the following error:

   An exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll but was not handled in user code

   Additional information: database is locked

This can occur for a number of reasons but typically in my cases it's caused because I either didn't close a SQLiteDataReader or I didn't close a SQLiteConnection. The cases where this happens are usually when I'm developing the site and I have an Exception that interrupts the execution and it never gets to the code to close the data reader/connection (before I've added all of my sanity checks and exception handling). This can also occur in a case where multiple writers are hitting the database at the same time which is harder to avoid in a multi-tenant environment.

When the database gets into this state you can no longer write to it. In some cases you can kill the process like the webdev server, IIS express or recycle your app pool. When this doesn't work (and it just didn't for me) you can make a call to "SQLiteConnection.ClearAllPools()" and it should release the lock.

Solution:

VB.Net

   SQLiteConnection.ClearAllPools()

C#

   SQLiteConnection.ClearAllPools();

There maybe better ways to do this but this but this certainly works while you're working the kinks out of your code. Ideally, you want to get your code into a state where this doesn't occur but if you're using SQLite in a multi-tenant environment like an ASP.Net web site you may dot your I's and cross your T's and still run into a locked database every so often and need a way out. I use SQLite on my Azure hosting (because it's free and there are no gotcha fees with data storage for the database or data moving to and from your web-site to SQL Server if it's in a different zone for some reason, etc.). For my moderately low traffic site SQLite has worked out really well.