0

A process crashed while trying to commit an insert of 2-3 million rows. Now, I'm finding the database file is locked for all activity, including reading, dumping, and running an integrity check.

  1. Can I unlock this database in some way or get sqlite3 to recover the file?

  2. Is there any way of recovering the contents of this database file? I do have a backup from 24 hours ago, but a variety of other data will be lost if I have to do a complete rollback to an older version of the database.

Code below:

$ sqlite3 dbFile.sqlite
SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
sqlite> PRAGMA integrity_check;
Error: database is locked
sqlite> .tables
Error: database is locked
sqlite> SELECT * FROM dbTable LIMIT 1;
Error: database is locked

Running fuser on the database file indicates that no processes are currently attempting to access the file. There is a hot file in the same directory (i.e. dbFile.sqlite-journal exists).

Using stat, I see that the folder in question is of type panfs, which seems likely to be the cause of the issue. What can I do here?

Notably not a duplicate of this question, as the database is (1) not locked by any running process, as mentioned above, (2) locked for reading as well, and (3) due to the full read/write lock the .dump solution in this answer is inapplicable (as .dump fails).

Closest related question is this question, although that one is on a different NFS filesystem.

Suriname0
  • 527
  • 1
  • 8
  • 21

1 Answers1

0

Apparently, the PanFS server(s) did not notice the crash, and so still report the write lock.

You could try to unmount and re-mount the file system on this machine, if possible.

Alternatively, copy both files (database and journal) elsewhere. When you open that database, SQLite will roll back the partial transaction.

CL.
  • 173,858
  • 17
  • 217
  • 259