patternsqlMinor
1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "
Viewed 0 times
1stwritesprocesslockeddatabaseonereadssqlite_busy2nd
Problem
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:
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
-
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?
-
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 lockedThe 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?
Solution
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:
WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently.
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.
Code Snippets
PRAGMA journal_mode=WAL;Context
StackExchange Database Administrators Q#314914, answer score: 4
Revisions (0)
No revisions yet.