1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "

Question

I have 2 processes that use the same Sqlite3 database.

• 1st one -- in NodeJs, is a program which constractly writes data into a database, with the frequency of around 1 write / 1 second. It only writes data.

• 2nd one -- in Golang, is a web application which only reads that data.

However, once I launch the 2nd application, there'll always occur this error:

SQLITE_BUSY: database is locked

The 1st applicatation will then self-terminate, due to the error, writing some corrupted data.

The 2nd application will continue to work. Or sometimes it'll self-terminate too, due to corrupted data -- reading a NULL instead of a value.

• Why will the error occur in the first place? For fhere's only one writer. And even if there were multiple writers, they'd simply wait for each other.

• How to fix it?

Answer 1

The first problem is that your application is not handling the busy error. By default sqlite will NOT wait for the other processes to complete their read or write operation, but will return the error. If you want to wait for any amount of time, you have to register a callback function, as described here: https://www.sqlite.org/c3ref/busy_handler.html

Apparently, node.js doesn't handle the busy error itself.

As to why the error occurs, it's because sqlite allows multiple readers OR one writer, but not both at the same time. As you can check here (https://www.sqlite.org/lockingv3.html) when the writer needs to commit the changes to the database, it needs to acquire an EXCLUSIVE lock, which will prevent readers to read inconsistent data. If a reader is reading the db, the writer will get the BUSY error, which has to be managed by the application if you want to wait and retry.

A possible solution could be using a WAL journal instead of a ROLLBACK one. WAL mode (https://www.sqlite.org/wal.html) will allow for the writer to write without needing an exclusive lock. You can activate WAL with the following command:

PRAGMA journal_mode=WAL;

WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.

Answer 2

SQLite is not a multiuser database and should not be used as such. If you require more than one application working with the database at the same time, choose a proper multiuser DBMS.

Documentation comes with these warnings:

SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable.

They also have a section in the manual, called literally How To Corrupt Your Database Files, that lists additional warnings.

Given all these caveats, I wouldn't be surprised if there were more.

Some of the possible reason for the database file corruption are listed in the documentation: Things That Can Go Wrong and How To Corrupt Your Database Files. For example,

SQLite uses filesystem locks to make sure that only one process and database connection is trying to modify the database at a time. The filesystem locking mechanism is implemented in the VFS layer and is different for every operating system. SQLite depends on this implementation being correct. If something goes wrong and two or more processes are able to write the same database file at the same time, severe damage can result.

or

SQLite uses the fsync() system call on Unix [...] in order to sync the file system buffers onto disk oxide[...] Unfortunately, we have received reports that neither of these interfaces works as advertised on many systems. [...] Some historical versions of Linux contain versions of fsync() which are no-ops on some filesystems, we are told. Even on systems where FlushFileBuffers() and fsync() are said to be working, often the IDE disk control lies and says that data has reached oxide while it is still held only in the volatile control cache.

I've omitted stuff irrelevant for Linux from the quote above to make it shorter.

As to how to "fix" the error, you could try these suggestions (in no particular order]:

• Improve your software. Don't let it "self-terminate, due to the error, writing some corrupted data"; let it wait and retry.

• Follow the documented suggestion: "consider using a secondary locking mechanism to prevent simultaneous writes to the same database even if the native filesystem locking mechanism malfunctions."

• Better yet, use a proper multiuser database system for a multiuser application like yours.