Unlocking and Journaling in SQLite (opens in new tab)

Hello, I’m Maneshwar. I’m working on FreeDevTools online currently building "one place for all dev tools, cheat codes, and TLDRs" — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.

In yesterday’s post, we followed the lock escalation path inside SQLite, all the way up to EXCLUSIVE locks, and saw how sqlite3OsLock carefully coordinates process wide and connection level state without ever letting native locks break isolation.

Today’s reading completes that picture in two important ways:

1. How locks are safely released or downgraded
2. How journaling guarantees atomicity and durability once writes begin

Together, these mechanisms close the loop on SQLite’s transaction safety model.

The sqlite3OsUnlock API: Controlled Lock Downgrades

Lock acquisition in SQLite is asymmetric — locks can only be strengthened using sqlite3OsLock. Releasing or weakening a lock is handled separately by the sqlite3OsUnlock API.

The function signature is:

int sqlite3OsUnlock(unixFile *id, int locktype);

On Unix platforms, this maps to posixUnlock in os_unix.c.

A key design constraint is that sqlite3OsUnlock can only reduce lock strength to: SHARED, or NOLOCK

Any attempt to increase lock strength must go through sqlite3OsLock.

Connection-Level vs Process-Level State (Again)

Just like lock acquisition, unlocking compares two states:

• id->eFileLock → lock held by this specific database connection
• id->inodeinfo->eFileLock → strongest lock held by the process on this inode

This distinction ensures that one connection cannot accidentally undo another’s locks and process-wide invariants remain intact

Early Exit: Nothing to Do

If the connection already holds a lock weaker than or equal to the requested downgrade

SQLite immediately returns success.

This avoids unnecessary work and keeps unlock paths fast.

Lock Downgrades from Stronger Modes

If the connection currently holds a lock stronger than SHARED, SQLite enters a lock downgrade path.

For example:

• EXCLUSIVE → SHARED
• RESERVED → SHARED

In this case:

• a read lock is placed on the SHARED_BYTES region
• write locks on the PENDING and RESERVED regions are cleared

This ensures that readers may safely proceed, writers are still excluded and no process-wide invariants are violated

Unlocking to NOLOCK: Releasing the File Completely

When the requested lock type is NOLOCK, SQLite is signaling that this connection is done with the file.

At this stage:

1. The shared-lock counter nShared in unixinodeinfo is decremented
2. If nShared reaches zero:

• all native locks on the file are cleared

1. The process-wide lock counter nLock is decremented
2. If nLock reaches zero:

• all lazy-close file descriptors are finally closed