Increase the lock timeout with sqlite, and what is the default values?

Well known issue when many clients query on a sqlite database : database is locked
I would like to inclease the delay to wait (in ms) for lock release on linux, to get rid of this error.

From sqlite-command, I can use for example (4 sec):

sqlite> .timeout 4000 sqlite> 

I've started many processes which make select/insert/delete, and if I don't set this value with sqlite-command, I sometimes get :

sqlite> select * from items where code like '%30'; Error: database is locked sqlite> 

So what is the default value for .timeout ?

In Perl 5.10 programs, I also get sometimes this error, despite the default value seems to be 30.000 (so 30 sec, not documented).
Did programs actually waited for 30 sec before this error ? If yes, this seems crasy, there is at least a little moment where the database is free even if many other processes are running on this database

my $dbh = DBI->connect($database,"","") or die "cannot connect $DBI::errstr"; my $to = $dbh->sqlite_busy_timeout(); # $to get the value 30000 

Thanks!

1 Answer

The default busy timeout for DBD::Sqlite is defined in dbdimp.h as 30000 milliseconds. You can change it with $dbh->sqlite_busy_timeout($ms);.

The sqlite3 command line shell has the normal Sqlite default of 0; that is to say, no timeout. If the database is locked, it errors right away. You can change it with .timeout ms or pragma busy_timeout=ms;.

The timeout works as follows:

The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. After at least "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY.

If you get a busy database error even with a 30 second timeout, you just got unlucky as to when attempts to acquire a lock were made on a heavily used database file (or something is running a really slow query). You might look into WAL mode if not already using it.