4 ms·

Is SQLite capable of supporting multiple writes to the database from different processes at the same time? This question is purely around INSERTS - no UPDATES.

by oars 6y ago

Is SQLite capable of supporting multiple writes to the database from different processes at the same time? This question is purely around INSERTS - no UPDATES. Anything I should be aware of? Looking to write data from multiple services to an SQLite file.

ficklepickle 6y ago
From the FAQ: > Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. Sounds like you can, just not at the same moment as it locks the file during writes. It appears to retry if the file is locked, so not the end of the world. Faq- https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 Relevant SO post - https://stackoverflow.com/questions/15383615/multiple-access-to-a-single-sqlite-database-file-via-system-data-sqlite-and-c-sh#15402706 https://stackoverflow.com/questions/15383615/multiple-access...
itroot 6y ago
I think that this applies to all modifying operations - INSERTs, UPDATEs, DELETEs. It's perfectly ok to write to sqlite from different processes in the same time, but to achieve good results it's better to: * use WAL mode - so the readers and writers do not block (you can turn on it with `PRAGMA journal_mode=WAL;` in CLI, and it's better to add `PRAGMA main.synchronous=NORMAL;` also). * all concurrent writes will be queued by sqlite3 lib and done in sequential manner, and if any write attempt will wait longer that BUSY_TIMEOUT ( see https://www.sqlite.org/pragma.html#pragma_busy_timeout https://www.sqlite.org/pragma.html#pragma_busy_timeout ) , it will return error. Snippet # setting things up... itroot@l7490:/tmp$ grep -i pragma ~/.sqliterc PRAGMA journal_mode=WAL; PRAGMA main.synchronous=NORMAL; PRAGMA busy_timeout=1000; itroot@l7490:/tmp$ sqlite3 test.sqlite 'CREATE TABLE records (id INTEGER PRIMARY KEY, record TEXT);' > /dev/null 2>&1 # running 10 parallel processes that inserts numbers from 1 to 1000... itroot@l7490:/tmp$ echo {1..1000} | xargs -n1 -d' ' -P 10 -i% sqlite3 test.sqlite 'INSERT INTO records (record) VALUES (%);' >/dev/null 2>&1 # getting number of records itroot@l7490:/tmp$ sqlite3 test.sqlite 'SELECT count(*) FROM records;' count(*) = 1000

by oars 6y ago

ficklepickle 6y ago

From the FAQ: > Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however. Sounds like you can, just not at the same moment as it locks the file during writes. It appears to retry if the file is locked, so not the end of the world. Faq- https://www.sqlite.org/faq.html#q5 https://www.sqlite.org/faq.html#q5 Relevant SO post - https://stackoverflow.com/questions/15383615/multiple-access-to-a-single-sqlite-database-file-via-system-data-sqlite-and-c-sh#15402706 https://stackoverflow.com/questions/15383615/multiple-access...

itroot 6y ago

I think that this applies to all modifying operations - INSERTs, UPDATEs, DELETEs. It's perfectly ok to write to sqlite from different processes in the same time, but to achieve good results it's better to: * use WAL mode - so the readers and writers do not block (you can turn on it with `PRAGMA journal_mode=WAL;` in CLI, and it's better to add `PRAGMA main.synchronous=NORMAL;` also). * all concurrent writes will be queued by sqlite3 lib and done in sequential manner, and if any write attempt will wait longer that BUSY_TIMEOUT ( see https://www.sqlite.org/pragma.html#pragma_busy_timeout https://www.sqlite.org/pragma.html#pragma_busy_timeout ) , it will return error. Snippet # setting things up... itroot@l7490:/tmp$ grep -i pragma ~/.sqliterc PRAGMA journal_mode=WAL; PRAGMA main.synchronous=NORMAL; PRAGMA busy_timeout=1000; itroot@l7490:/tmp$ sqlite3 test.sqlite 'CREATE TABLE records (id INTEGER PRIMARY KEY, record TEXT);' > /dev/null 2>&1 # running 10 parallel processes that inserts numbers from 1 to 1000... itroot@l7490:/tmp$ echo {1..1000} | xargs -n1 -d' ' -P 10 -i% sqlite3 test.sqlite 'INSERT INTO records (record) VALUES (%);' >/dev/null 2>&1 # getting number of records itroot@l7490:/tmp$ sqlite3 test.sqlite 'SELECT count(*) FROM records;' count(*) = 1000