Hey Greg, sorry to hear you’ve been grinding on this SQLite lock issue for 20 hours—nothing’s more frustrating than a production bug that’s reproducible but stubborn to fix. Let’s break this down based on what you’ve observed and how SQLite actually handles concurrency:

为什么会出现这个问题？ #

First off, let’s get to the core of SQLite’s limitation: it’s a single-file embedded database, which means it relies on file-system level locks to keep data consistent. When a write operation (like inserting a new user during registration) runs, SQLite grabs an exclusive lock on the database file. Until that lock is released, every other write request (and even some read requests) has to wait in a queue.

Your scenarios directly hit this bottleneck:

• 100+ users registering in 10 seconds: That’s a sudden flood of write requests. The lock queue piles up fast, and if any request waits longer than SQLite’s default busy timeout (which is 5000ms/5 seconds), it throws the database is locked error.
• JMeter reproducing the error when timeouts exceed 5s: This perfectly lines up with that default timeout setting—your test is exactly mirroring the production lock wait scenario.

Fixes you can try (from quick band-aids to long-term solutions) #

1. Tweak the busy timeout (quick temporary fix) #

If you can’t swap out SQLite right away, you can increase the SQLITE_BUSY_TIMEOUT to give requests more time to wait for the lock. Run this pragma when your app initializes the database connection:

PRAGMA busy_timeout = 10000; -- Set to 10 seconds instead of 5

Fair warning: This just buys you time—it won’t fix the underlying concurrency limit. You’ll still get slow responses under heavy load.

2. Optimize your write operations #

• Batch inserts: Instead of sending 100 separate INSERT statements for 100 users, bundle them into one bulk insert. Like this:

  INSERT INTO users (email, username) VALUES ('user1@test.com', 'user1'), ('user2@test.com', 'user2'), ...;
  

  This cuts down on the number of lock acquisitions and releases, reducing contention.
• Shorten transaction duration: Make sure each registration transaction only does the bare minimum—no calling external APIs, running heavy calculations, or other non-db work inside the transaction. The faster you release the lock, the less time other requests have to wait.

3. Switch to a client-server database (long-term fix) #

Let’s be real: SQLite wasn’t built for high-concurrency write scenarios. The official docs even note it’s best suited for cases with fewer than 10-20 concurrent writes per second. If your registration traffic is going to stay high (or grow), switching to PostgreSQL, MySQL, or another client-server DB is the only way to fully eliminate these lock issues. These databases are designed to handle hundreds/thousands of concurrent writes without file-level lock bottlenecks.

4. Add application-level throttling or queuing #

If you need to stick with SQLite for a bit longer, buffer the load at the app level:

• Rate limiting: Add a throttle to your registration API (e.g., max 50 registrations per second) to prevent overwhelming the database.
• Async queuing: Use a message queue (like Redis or a simple in-memory queue) to queue registration requests, then process them in batches in the background. This turns a flood of concurrent writes into a steady stream of low-concurrency writes, drastically reducing lock conflicts.

How to verify your fixes #

After making changes, fire up JMeter again:

• Test the adjusted busy timeout to see if the error now triggers at 10 seconds instead of 5.
• For batch inserts or queuing, check if the error rate drops to zero and response times stay consistent under load.

内容的提问来源于stack exchange，提问作者Greg