piccolo: sqlite timeout handling

Hi, I often get sqlite3.OperationalError: database is locked, what would be the best way to evade this?

I thought about increasing the lock timeout, but am not sure how to correctly do this when using this library.

Here https://stackoverflow.com/a/39265148 the pragma-statement PRAGMA journal_mode=WAL is recommended. How would I use this with piccolo? I know I can execute raw sql, but doesn’t this need to be re-executed for each connection?

Thank you for your help!

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 24 (15 by maintainers)

Most upvoted comments

@sinisaos OK, cool.

There’s basically two things causing the locks:

If a transaction is open too long, which can happen with asyncio with lots of concurrent connections (passing in timeout helps with this)
If we use BEGIN instead of BEGIN IMMEDIATE, and the first query in the transaction is a SELECT, then SQLite turns it into a read transaction. If we then try and do something like an INSERT within the transaction, SQLite tries to upgrade it to a write transaction, but if several connections try doing this, we get the lock error.

The problem with BEGIN IMMEDIATE is it’s less efficient is someone just wants to do SELECT queries in the transaction. So we will have to allow the user to specify which transaction type they want.

dantownsend on Nov 27, 2022

@sinisaos Hmm, strange.

What query are you running inside the transaction? Are you also passing timeout to SQLiteEngine?

dantownsend on Nov 27, 2022