I am trying to see the insert performance of SQLite3 for a large number of inserts, I read about WAL mode, and how it is better at concurrency but I cannot follow on the concurrency part or see a speed improvement.

I have set JOURNAL_MODE=WAL, SYNCHRONOUS=NORMAL, OFF on a few runs but the database inserts in WAL mode are slower than the default JOURNAL_MODE=DELETE mode.
The benefit of WAL mode seems to be it allows multiple readers and 1 writer

Here is the code or the writer

import sqlite3
import time
import json
import pickle
import os
import random

REC_LIMIT = 100000

def getDBConn() -> sqlite3.Connection:
   conn = sqlite3.connect('test.db')
   # conn.execute('pragma journal_mode=memory')
   # conn.execute('pragma journal_mode=WAL')
   conn.execute('pragma synchronous=FULL')
   for row in conn.execute('pragma journal_mode').fetchall():
      print(row)
   for row in conn.execute('pragma synchronous').fetchall():
      print(row)
   conn.commit()
   return conn

def cleanup():
   if os.access('test.db', os.F_OK):
      os.unlink('test.db')
   time.sleep(2)

def initDB():
   with getDBConn() as conn:
      tbl_query = "CREATE TABLE IF NOT EXISTS test_table (eid INTEGER PRIMARY_KEY, username TEXT, json TEXT, pickled_json BLOB)"
      conn.execute(tbl_query)


def genusername() -> str:
   arr = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9']
   result = []
   for i in range(30):
      result.append(random.choice(arr))
   return "".join(result)

def gen_records() -> list:
   records = []
   str_dummy_payload = '''BIG JSON'''
   dummy_payload = json.loads(str_dummy_payload)
   dummy_payload = pickle.dumps(dummy_payload)
   for i in range(REC_LIMIT):
      records.append((genusername(), str_dummy_payload, pickle.dumps(dummy_payload)))
   print("Generated recordsn")
   return records

def batch_insert(records: list):
   cleanup()
   initDB()
   start = time.process_time()
   with getDBConn() as conn:
      cur = conn.cursor()
      stmt = "INSERT INTO test_table (username, json, pickled_json) VALUES (?,?,?);"
      for idx,row in enumerate(records):
         if idx % 1000 == 0:
            conn.commit()
         cur.execute(stmt, row)
      cur.close()
   print(f"nTime taken while insert in small batches {time.process_time() - start} n")

def long_write(records):
   start = time.process_time()
   with getDBConn() as conn:
      cur = conn.cursor()
      stmt = "INSERT INTO test_table (username, json, pickled_json) VALUES (?,?,?);"
      cur.execute(stmt, records[0])
      print("Begin long write....")
      time.sleep(50)
      cur.close()
   print(f"nTime taken while using single insert {time.process_time() - start} n")

def main():
   records = gen_records()
   batch_insert(records)
   long_write(records)
main()

Reader

import sqlite3
import time

conn = sqlite3.connect('test.db')
cur = conn.cursor()
stmt = "SELECT * from test_table LIMIT 10;"
for i in range(1000000000):
    cur.execute(stmt)
    for row in cur.fetchall():
        # print(row)
        pass
cur.close()
conn.close()
print("Read Completed!")

Say I create multiple readers and then call the writer I never come across the database-locked issue, similarly when the writer is going I call the readers it works fine no database-locked issue.

If I do insert/update from 2 processes, I do hit the database locked issue as expected.

The only scenario(multiple readers 1 writer) when I see a database-locked is
if I do

BEGIN TRANSACTION EXCLUSIVE

when doing inserts or updates

WAL is useful when I use EXCLUSIVE
Does it mean my reads are wrong/incorrect if I don’t use ‘exclusive’ during writes/updates?

If I don’t use exclusive I don’t see any benefit of WAL mode, the inserts are much slower as the number of records increases.
What is the right way to insert/update data in SQLite3?
How unsafe is it to set SYNCHRONOUS=OFF, from the docs it seems like unless the OS crashes or a power failure the database should be fine.

OperatingSystem: OpenSuse Leap 15.5
Python version: 3.6.15