Understanding EXCLUSIVE and WAL mode
(1) By aviben on 2024-07-25 14:37:53 [source]
I went through the documents about the benefits of WAL mode, but I am not sure how it is better.
In python say if I try to insert/update with 1 process and another process tries to read the database at the same time, reading is allowed.
If insert/update is done with BEGIN TRANSACTION EXCLUSIVE then readers are blocked if JOURNAL_MODE=DELETE, in WAL mode readers are allowed.
WAL mode in my custom scripts was slower than DELETE, if I changed the SYNCHRONOUS parameter to NORMAL or OFF the performance was better but JOURNAL_MODE=DELETE was always faster.
My question is:
Should we use EXCLUSIVE when inserting/updating to ensure that readers don't get inconsistent/wrong information?
Is WAL mode slower compared to JOURNAL_MODE=DELETE?
If WAL mode is being used is it better to do batch inserts like for eg if I have 500 rows(5 columns each) I do inserts in batches of 100 instead of using python available API executemany() ??
If I want to protect the database from Application crashes(mine alone) is it better I just use SYNCHRONOUS=OFF?
(2) By aviben on 2024-07-25 16:23:26 in reply to 1 [link] [source]
https://stackoverflow.com/questions/78793626/sqlite3-slow-writes-in-wal-mode-and-the-need-for-exclusive
Link to SO question with code example
(3.1) By sqlitening on 2024-07-28 14:31:10 edited from 3.0 in reply to 1 [link] [source]
With or without transaction makes no difference here.
WAL mode finishes 8-times sooner with this little test.
Edit: The StartReader thread is not needed.
GLOBAL gEndReaderFlag AS LONG
#INCLUDE ONCE "sqlitening.inc"
'---------------------------------------------------------------------
FUNCTION PBMAIN AS LONG
LOCAL StartTime,EndTime AS SINGLE
slOpen "junk.db3","C"
slexe "pragma journal_mode=delete" '16.3 seconds
slexe "pragma journal_mode=wal" ' 2.3 seconds
slexe "create table if not exists t1(num integer primary key)"
StartTime = TIMER
THREAD CREATE StartReader(0) TO x&:THREAD CLOSE x& TO x&
FOR x& = 1 TO 1000
slexe "insert into t1 values(null)"
NEXT
EndTime = TIMER
gEndReaderFlag = 1
? slSelStr("select count(*) from t1"),%MB_SYSTEMMODAL,USING$("#.#",EndTime-StartTime)
END FUNCTION
'---------------------------------------------------------------------
THREAD FUNCTION StartReader(BYVAL NotUsed AS LONG) AS LONG
slOpen "junk.db3"
DO
slSelStr("select count(*) from t1")
SLEEP 250
LOOP UNTIL gEndReaderFlag
END FUNCTION
(4.1) By sqlitening on 2024-07-28 14:42:09 edited from 4.0 in reply to 3.1 [link] [source]
slOpen "junk.db3","C" slexe "pragma journal_mode=wal" slexe "begin immediate" 'for use on a network 'insert records slexe "end"