[PERFORMANCE] SQLite WAL mode is not enabled, causing database lock errors under concurrent read and write requests

[anshul23102]

Problem

The SQLite database runs in default journal mode (DELETE/rollback journal). In this mode, SQLite allows only one writer at a time and blocks all readers while a write is in progress. The Node.js Express server handles HTTP requests concurrently. When two requests attempt database operations simultaneously (e.g., a task creation and a task list fetch arriving within milliseconds of each other), one request receives SQLITE_BUSY and typically fails with a 500 error.

Steps to Reproduce

1. Use a load-testing tool to send 10 concurrent requests to GET /api/tasks and POST /api/tasks simultaneously
2. Observe SQLITE_BUSY errors in the server logs and HTTP 500 responses

Proposed Fix

Enable Write-Ahead Logging (WAL) mode immediately after opening the database. WAL allows concurrent reads alongside a single writer:

const db = new Database("studyplan.db");
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000"); // retry for up to 5 seconds before failing

This is a one-time configuration change that eliminates lock contention in typical usage patterns.

Complexity: Level 2 | Program: GSSOC '26

[anshul23102]

/assign

Claiming this issue for GSSoC '26. Please assign it to me, I'll start working on it right away.

[aaniya22]

pls assign this to me under gssoc

[anshul23102]

@Charushi06 SQLite WAL mode optimization will significantly improve concurrent access. I can enable WAL and implement proper configuration.

/assign @anshul23102
/claim

[anshul23102]

Hi @Charushi06, I'd like to work on this. Plan: enable SQLite WAL mode (PRAGMA journal_mode=WAL) on connection setup to allow concurrent readers alongside a writer, eliminating the lock errors. Requesting assignment under GSSoC 2026.

/assign
/claim