Maybe an obvious quesiton, but can you catch SQLITE_BUSY and just loop and retry in your application code? Is there any reason not to retry after a second or so? Such application retry logic is pretty standard for transaction deadlock issues.
"David E.Wheeler" <da...@kineticode.com> 12/22/2010 05:26 PM To DBI Users Mailing List <dbi-users@perl.org> cc Subject SQLite Concurrency Issue My fellow database gurus, I love SQLite, but an issue with concurrency is driving me absolutely batshit. I have a program that runs every few minutes, updating an SQLite database from feeds. There can be some overlap in the runtime: a new instance may start while a previous instance is still running. In such a case, I nearly always get the dreaded "database is locked" error -- SQLITE_BUSY. Here's what I've done to try to avoid this error: * sqlite_use_immediate_transaction => 1 * PRAGMA journal_mode = WAL * $dbh->sqlite_busy_timeout(60_000); None of these has made a difference. Now, I recognize that there can be some lock contention, but I certainly would think that one process would try to wait for a bit for a lock to release before it gave up the ghost. But when I run this stuff, it fails *very* quickly -- the busy timeout obviously is not being used. So my question is, what other tactics can be used to improve the lock concurrency situation with SQLite? I understand that there have to be some pretty strong locks, but I'm perfectly happy for one process to wait for a lock to be freed. Why doesn't it just wait? It fails instantly! Or am I going to have to use PostgreSQL? Frustratedly, David Please consider the environment before printing this email.
