Test SQLite locking with two connections

SQLite allows many readers at once, but only one connection can write at a time. Reading about that rule is one thing. Watching it happen in two terminal windows makes it stick.

Set up two connections

Open notes.db in two terminals. In both shells, set a three-second busy timeout so a blocked write waits instead of failing instantly:

.timeout 3000

The timeout is in milliseconds. Without it, a second writer gets database is locked on the first conflict.

Claim the write lock

In terminal A, start a write transaction and hold it open:

BEGIN IMMEDIATE;
UPDATE notes SET title = 'Held by terminal A' WHERE id = 1;

BEGIN IMMEDIATE asks for the write lock right away. Do not run COMMIT yet. Terminal A now owns the only write slot.

In terminal B, try the same thing:

BEGIN IMMEDIATE;

Terminal B waits. After about three seconds it prints database is locked because terminal A still holds the lock.

Go back to terminal A and run:

COMMIT;

Now terminal B can retry BEGIN IMMEDIATE and succeed.

What this means for your application

A busy timeout handles brief conflicts: two requests hitting the database at nearly the same moment. It does not fix long transactions.

Never open a write transaction, call a remote API, wait for user input, and then commit. That holds the write lock for seconds while every other writer queues up or fails. Keep transactions short and wrap only the database work.

Try it yourself with the two-terminal setup above. The wait you see in terminal B is exactly what your application users experience when one connection holds a transaction open too long.