[DBD-SQLite] Concurrency in SQLite

[Toby Corkindale]

Zbigniew Lukasiak wrote:
> Found this: http://www.reddit.com/r/programming/comments/8bl31/sqlite_can_actually_handle_quite_a_bit_of/
> on reddit today and tried to check that in Perl.
[snip]
> The errors file contains this line (multiplied):
> DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403
> at test.pl line 20.
[snip]


SQLite doesn't really do locking.. It just runs a sort of busy-wait 
loop, with a 30 second time-out. So if one process holds a transaction 
for >30 seconds, other processes will fail with the error you're seeing.

That said, I had a stress test for SQLite 1.18 which forked off a dozen 
processes which all ran quick actions, and it would still break rapidly 
(with the above error), despite none of the transactions taking more 
than milliseconds.

Also, if you continued to hit sqlite and cause the above error, then 
after a while you could finish the long running transaction, BUT 
dbd::sqlite still thought a transaction was in process, and so no more 
processes could ever get a lock again, until *everything* restarted.

I need to try and find that test and re-run it on 1.19 or 1.20_* to see 
how it goes; but I think I threw it out when cleaning up my home 
directory a while back.