Locking, Savepoints, and In-Memory Databases in SQLite

Hello, I'm Maneshwar. I'm building git-lrc, an AI code reviewer that runs on every commit. It is free, unlimited, and source-available on Github. Star Us to help devs discover the project. Do give it a try and share your feedback for improving the product.

As we go deeper into SQLite, things start getting less about syntax and more about how the database behaves under real workloads.

Features like triggers and autovacuum shape behavior internally, but now we’re stepping into how SQLite handles concurrency, transactions, and performance tradeoffs.

This is where concepts like table-level locking (or the lack of it), savepoints, and in-memory databases become important.

Table-Level Locking (Or Why SQLite Feels Different)

One of the most important things to understand about SQLite is this:

• SQLite does not support table-level locking
• It uses file-level locking instead

This means the entire database file is treated as a single unit when it comes to locking.

What This Means in Practice

If one process is writing to the database:

• Other writes are blocked
• Reads may also be restricted depending on the lock state

So unlike larger databases:

• You cannot have multiple writers working on different tables at the same time
• Concurrency is limited at the file level

This design keeps SQLite simple and reliable, but it also introduces limitations in high-write scenarios.

A Clever Workaround: Splitting the Database

If you really need something closer to table-level locking, SQLite gives you a workaround.

You can:

• Store different tables in different database files
• Then combine them using the ATTACH command

ATTACH DATABASE 'orders.db' AS orders_db;
ATTACH DATABASE 'users.db' AS users_db;

Now your application can treat them like one logical database.

Why This Improves Concurrency

Because each file is locked separately:

• One process can write to users.db
• Another can write to orders.db

At the same time.

So while SQLite doesn’t support table-level locking directly, you can simulate it by splitting tables across files.

The Tradeoffs

This approach works—but it’s not free.

You introduce:

• Multiple database files to manage
• Multiple rollback journals
• A master journal for multi-database transactions
• Increased memory usage (each file has its own cache)

Also, transactions spanning multiple databases:

• Are still ACID
• But can be slower due to coordination overhead

So this is a tradeoff between concurrency and complexity.

Savepoints: Fine-Grained Control Inside Transactions

Transactions in SQLite are usually all-or-nothing.

But sometimes you don’t want to roll back everything,you just want to undo part of it.

That’s where savepoints come in.

What is a Savepoint?

A savepoint is a named checkpoint inside a transaction.

You create one like this:

SAVEPOINT sp1;

Now SQLite remembers the current state.

Rolling Back Partially

If something goes wrong, you can roll back just part of your work:

ROLLBACK TO sp1;

This:

• Undoes changes made after sp1
• Keeps the transaction active

This is very different from a full ROLLBACK, which cancels everything.

Releasing a Savepoint

Once you’re happy with changes, you can finalize them:

RELEASE sp1;

This commits that portion of the transaction.

Why Savepoints Matter

Savepoints are especially useful when:

• You have complex operations
• You want partial recovery
• You don’t want to restart an entire transaction

They give you granular control, which is something basic transactions don’t offer.

A Small but Important Detail

Savepoints can be:

• Nested
• Reused (same name overrides previous one)

Also, SQLite keeps extra journal data while savepoints exist, because it needs that information to support partial rollbacks.

In-Memory Databases: Maximum Speed, Maximum Risk

Now let’s talk about performance.

SQLite allows you to create a database that lives entirely in memory:

sqlite3_open(":memory:", &db);

This creates a database with:

• No files
• No disk I/O
• Everything stored in RAM

Why It’s So Fast

Because:

• No disk reads/writes
• No file system overhead

Operations become extremely fast, making this ideal for:

• Testing
• Temporary data
• High-speed processing

The Limitations

This speed comes with serious tradeoffs.

1. Data is Not Persistent

Once the application closes:

• The entire database is gone

2. Not Shareable Across Processes

Each in-memory database:

• Exists only within one connection
• Cannot be accessed by other processes

3. Risk of Data Loss

If:

• The app crashes
• The system crashes

All data is lost instantly.

4. Memory Usage

Everything lives in RAM, so:

• Large datasets require large memory
• Not suitable for heavy storage

When Should You Use In-Memory Databases?

They are great for:

• Caching
• Temporary computations
• Unit testing
• Prototyping

They are not suitable for:

• Persistent storage
• Critical systems
• Large-scale datasets

*AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.*

Any feedback or contributors are welcome! It's online, source-available, and ready for anyone to use.

⭐ Star it on GitHub:

HexmosTech / git-lrc

AI Micro Code Reviews That Run on Commit

| 🇩🇰 Dansk | 🇪🇸 Español | 🇮🇷 Farsi | 🇫🇮 Suomi | 🇯🇵 日本語 | 🇳🇴 Norsk | 🇵🇹 Português | 🇷🇺 Русский | 🇦🇱 Shqip | 🇨🇳 中文 |

git-lrc

AI Micro Code Reviews That Run on Commit

 

     

---

AI agents write code fast. They also silently remove logic, change behavior, and introduce bugs -- without telling you. You often find out in production.

git-lrc fixes this. It hooks into git commit and reviews every diff before it lands. 60-second setup. Completely free.

See It In Action

See git-lrc catch serious security issues such as leaked credentials, expensive cloud operations, and sensitive material in log statements

git-lrc-intro-60s.mp4

Why

• 🤖 AI agents silently break things. Code removed. Logic changed. Edge cases gone. You won't notice until production.
• 🔍 Catch it before it ships. AI-powered inline comments show you exactly what changed and what looks wrong.
• 🔁 Build a habit,…

View on GitHub