Updated 2018-08-24 15:00:09 by pooryorick

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