2015-12-27 18:11 GMT+01:00 Yuriy M. Kaminskiy <yumkam at gmail.com>:
> (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.
>
?I know and in a program where I am only reading there is no problem.
?
> >> > 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).
>
?You did not read the problem correctly. The program is only needed to
check if a database is inadvertently locked. In this way I can be notified,
so that the database is not locked when the cron job is going to send
proverbs and need to change the database.
?
> 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.
>
?My experience is that there is a wait of around two seconds. At least in
Bash, Python and Java.
?
> 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.
?Well, for just checking if a database is locked I think it is good enough.
But I also wrote a Java program for it:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
public class CheckProverbsLocked {
// We use this class as a program,
// so we make sure the class cannot be initiated
private CheckProverbsLocked() {
}
// Check if the database is locked.
// If it is give message and return 1
// On other error give message about the error and return 2
// Otherwise no message and return 0
public static void main(String [] args) throws Exception {
Connection conn;
Statement stmt;
Class.forName("org.sqlite.JDBC");
conn =
DriverManager.getConnection("jdbc:sqlite:proverbs.sqlite");
stmt = conn.createStatement();
try {
stmt.executeUpdate("BEGIN IMMEDIATE");
} catch (SQLException e) {
// In a stand-alone program the next two statements are not
necessary,
// but I prefer well written code, so I use them
stmt.close();
conn.close();
if (e.getMessage().equals("database is locked")) {
System.out.println("The database is locked.");
System.exit(1);
} else {
System.err.printf("Unexpected error: %s\n",
e.getMessage());
System.exit(2);
}
}
// In a stand-alone program these statements are not necessary,
// but I prefer well written code, so I use them
stmt.executeUpdate("ROLLBACK");
stmt.close();
conn.close();
}
}
?
?The reason I use getMessage instead of getErrorCode is that getErrorCode
return 0 instead of 5.?
I should generalise it, so it can be used for any SQLite database.
--
Cecil Westerhof
