Updated 2018-01-23 00:53:12 by CecilWesterhof

Created by CecilWesterhof.

Reason for the script:

I am using a lot of SQLite databases. The problem is that I sometimes do things in a DB browser, but do not write or revert changes. This can give problems with my cron scripts that use the locked database.

Because of this I wrote a script that accept a series of databases as argument and check all those databases for being locked and print a line for the databases that are locked. So when no databases are locked, there is no output.

I have a cron entry that runs this script twice an hour, so I will be notified if I have to unlock a database.

Of-course I can also call it from the command line.

Functionality:

  • It opens every database (without creating it) and sets the timeout to ten seconds. (In case that another process is busy with the database.)
  • It tries to execute a BEGIN IMMEDIATE.
  • If this goes wrong because the database is locked: print it.
  • If something goes wrong for any other reason the program is terminated.
  • If the BEGIN IMMEDIATE was successful do a ROLLBACK.
  • Close the database.

The code:
#!/usr/bin/env tclsh


package require sqlite3


set timeout [expr {10 * 1000}]


foreach database ${argv} {
    sqlite db ${database} -create False
    db     timeout ${timeout}
    if {[catch {db eval "BEGIN IMMEDIATE"} SQLError]} {
        if {${SQLError} ne "database is locked"} {
            error "UNEXPECTED ERROR: ${SQLError}"
        }
        puts "The database ${database} is locked."
    } else {
        db eval "ROLLBACK"
    }
    db close
}

As always: comments, tips and questions are appreciated.


Category SQLite[Category System Maintenance][Category Utilities]