How To Remove Sqlite Database is Locked Error Code 5

At times while performing transactions in Sqlite database, the users might encounter error where the database gets locked and the following error occurs:

“Error Code 5 Database is locked”

Reason Behind The Error

This error code is generated when the user tries to perform two incompatible operations on a database at the same instance and on the same database connection. The SQLITE_LOCKED code indicates that an operation could not be continued because of a conflict with a transaction that uses the same database connection or the transaction that uses a different database connection using a shared cache.

An example of this is when the user tries to run a DROP TABLE statement while a different thread is trying to read from the same table and that too on the same database connection. This is because when the DROP TABLE command is run. The table would be deleted and therefore, the other thread will not be able to read from it.

Scenarios of Sqlite Database Is Locked Error Code 5

There are many scenarios when user received error code 5 database is locked. Some of them are:

• When a user is in the process to CREATE or DROP an index or a table whilst the SELECT statement is in a pending state, the database gets locked. This is because the users think that the SELECT statement has been finished as Sqlite3_() has returned the value SQLITE_DONE. However, this is not the case since SELECT statement is not considered as complete until Sqlite3_reset() or Sqlite3_finalize() are called.

• When a user tries to write in a table on which the SELECT operation is still active.

• When the user tries to do 2 SELECT on the same table and at the same instant in a multithread application and the Sqlite has not been set to do the same, the database may get locked.

It is to be noted that SQLITE_LOCKED should not be confused with SQLITE_BUSY parameter. This is because SQLITE_LOCKED indicates to a condition when there is a conflict between two transaction running on the same database connection and the SQLITE_BUSY indicates that two transactions running on different database connection and in different processes have conflicted.

Resolution to Remove Sqlite Locked Error

In order to remove “Sqlite database is locked error code 5” the most comprehensive solution is to create a backup of the database, which will not have any locks on it and then replacing the database with its backup copy. Follow the below-mentioned script to do the same where .x.Sqlite is the Sqlite database file:

$Sqlite3 .x.Sqlite

Sqlite> .backup main backup.Sqlite

Sqlite> .exit

Now the next step is to have a file named backup.Sqlite in the same directory. Then swap your old database with the backup copy of the database. Since the backup copy will not have any locks, the Sqlite database is locked error code 5 will not be encountered.

$mv .x.Sqlite old.Sqlite

$mv backup.Sqlite .x.Sqlite

Once the above script is executed successfully, the users can again access the Sqlite database. Once you are sure that the database is allowing both the read and write operations to run successfully, you can delete the old Sqlite database file. By above mentioned process user can remove “error code 5 database is locked” error without any hassle.