SQLite3: Beware of Concurrency

Posted on by

SQLite3 is a very lightweight implementation of a SQL database.  I’ve been using it in conjunction with Python on a single-threaded tool.  This morning I started refactoring my tool to have multi-process support, but I was interrupted by the following error:

sqlite3.OperationalError: database is locked

After reading through the SQLite3 documentation, I found that SQLite3 does database-level locking when performing write operations.  This means that all hope of parallelizing SQLite3 write operations is lost, because SQLite rejects a concurrent write attempt to the same database.

And I thought MySQL‘s table-level locking was bad (unless you’re using InnoDB) …

Update: for the record, PostreSQL does row-level locking.

  • Max Aller

    That’s why unless you’re doing anything but read-heavy operations all the time, you use InnoDB with MySQL ;)

  • foo

    Just increase the lock timeout. See the patch at http://code.djangoproject.com/ticket/9409 for docs.

  • http://www.alexloddengaard.com Alex Loddengaard

    Increasing the lock timeout seems like a terrible solution that should absolutely not be used in production. You’re asking for deadlock.

  • Jordan W

    AAAHHHH.. This is so old but thanks for the heads up! Will stay clear of sqlite.

  • http://www.alexloddengaard.com Alex Loddengaard

    SQLite3 is great for read-only stuff. It’s ultra fast for single-table SELECTs.