(I know, I'm a bit late for discussion, but...)
Cecil Westerhof <cldwesterhof at gmail.com>
writes:
> 2015-12-14 12:40 GMT+01:00 Clemens Ladisch <clemens at ladisch.de>:
>
>> Cecil Westerhof wrote:
>> > I have a crontab job which uses a SQLite database. Sometimes this database
>> > is locked because I used SQLite DB Browser, but did not Write or Revert
>> > Changes. It looks like that when a database is locked there is a file with
>> > the database name with -journal appended to it. Can I count on this?
>>
>> The -journal file exists when there is some changed data that might
>> need to be rolled back. It is possible for the DB to be locked before
>> some changed data is actually written.
>>
>> In WAL mode, there is no journal. (And if your job is read only, it
>> then would not be blocked by concurrent writes.)
>>
>
> ?No, I need to write also.?
Still, if read-only concurrent transactions are more likely, it will get
rid of (some) waiting on locks.
>> > Then I could write a script to warn me about the lock.
>>
>> You could use SQLite to check whether the DB is locked:
>>
>> if ! sqlite3 my.db "begin immediate"; then
>> echo "is locked"
>> fi
>>
>
> ?Thanks, I improved a little on it:
> sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null
***[1]
> errorCode="${?}"
> if [[ "${errorCode}" -eq 5 ]] ; then
> printf "${DATABASE} is locked\n"
> elif [[ "${errorCode}" -ne 0 ]] ; then
> printf "Error ${errorCode} while accessing ${DATABASE}\n"
> else
> printf "${DATABASE} is free\n"
> fi
***[2]
This check is racy and must not be used (What will happen if you will
start concurrent queries in DB Browser somewhere between [1] and [2]? Right,
this check won't detect database is locked, but your later (actual) code
*will* fail on attempt to lock).
1) You should set reasonable sqlite3_busy_timeout (e.g. with `sqlite3
-cmd '.timeout 120000' ...` [for 2 minutes timeout]). By default, it is 0,
so even if database is only shortly locked, it will still fail, even though
waiting a bit would help.
2a) If you can express complete your transaction in pure SQL, then you
should wrap whole transaction in 'begin immediate; ... ; commit;' in *single*
sqlite3 invocation:
sqlite3 -cmd '.timeout 120000' -bail "$DATABASE" "begin immediate;
insert [....];
update [...];
delete [...];
commit;"
errorCode="${?}"
...
2b) If you cannot express your DB change as static SQL statement(s)
(e.g., if some statements use results obtained in previous SELECT
queries, so you cannot feed your whole SQL code into single sqlite3
invocation), you should use SQLite binding to some real program language
(perl, tcl, python,...) instead of sh and sqlite3.
> I saw that when it is locked I get back a 5. Is this always the case? In
> this way I can see the difference between a lock and another error.
>
> ?If this code is correct I rewrite it for crontab. ;-)?
