SQLite - Database is locked

Summary:SQLite is a great, free, embedable database. It's fast, tiny, and reliable, and has some cool features like support for NTFS compression of the database file. It's multithreaded, but its coarse locking can be a problem.

I first read about SQLite over at EggHeadCafe researching ASP.NET database cache dependencies. It's small, fast, free, and there's a good SQLite ADO.NET provider on SourceForge. More about it here (PPT). I decided to use it for an RSS aggregator pet project. It worked well for a while, but I ran into problems when I began using it in a multi-threaded application (one thread downloading feeds, another marking a feed read when viewed).

SQLite is technically thread safe, but a write operation locks the entire database (more):
- Any resultset being step()'d through uses a shared read-only lock. 
- Any insert/update being executed requires an exclusive write lock. 

You would think the provider would wrap this with some retries or a reader / writer lock (the Python Sqlite wrapper handles retries), but it just throws the Database is Locked exception. I've filed a bug on this, but I'm not too hopeful - it may be viewed as "that's just how SQLite works".

I contemplated wrapping my database access with a TimedLock, but in the end I decided this was a good time to give SharpHSQL a shot. I'm not certain it will handle the multithreaded access, either, but the idea of a pure c# embeded database in my c# app appealed to me.

I think SQLite with the SQLite ADO.NET provider is great for applications with simple data needs, but watch out if you're planning to perform concurrent updates or inserts.

8 Comments

  • What about from Perl code?

  • I came up with a great solution for this problem if all you want to do is read something like firefox history. Firefox locks the database when the browser is open, so just make a copy of the file programmatically and then read it.

  • > Tuesday, April 21, 2009 10:20 AM by David
    > I came up with a great solution for this problem...

    David, man, thanks a ton!

    /HS

  • David thanks a lot, closing firefox solved the problem for me.

  • what about sqlite database locked in c# application...? please help...

  • You can write a wrapper in Perl to catch the error and try again until the lock is gone, but it isn't practical. The lock lasts for about 30 seconds, even if the other process just connects, writes one row, and immediately disconnects. This is a very serious problem with SQLite.

  • what about sqlite database locked in c# application...? please help...

  • I had a similar problem (inserts worked, but updates were 'locked'). In my case the user that created the file was not the same that did the inserts/updates.
    I solved it by deleting the sqlite file and had the same user create new tables on it. (in other words: the same user needed to perform all operations on it)

    Hope this helps somebody

Comments have been disabled for this content.