Re: SQLite Concurrency Issue

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" <[email protected]> 
12/22/2010 05:26 PM

To
DBI Users Mailing List <[email protected]>
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.