How do transactions work in SQLite and what is the difference between DEFERRED, IMMEDIATE, and EXCLUSIVE?
SQLite transactions ensure ACID compliance through locking states on the database file:
• `BEGIN DEFERRED` (default): No lock is acquired when the transaction begins. Locks are acquired on the first read (`SHARED`) or write (`RESERVED`). Can cause `SQLITE_BUSY` if another writer intervened. • `BEGIN IMMEDIATE`: Acquires a `RESERVED` lock immediately. Other connections can read, but no other connection can begin a write transaction. • `BEGIN EXCLUSIVE`: Acquires an `EXCLUSIVE` lock immediately, blocking all other connections from both reading and writing.
-- Immediate transaction prevents write lock race conditions
BEGIN IMMEDIATE TRANSACTION;
UPDATE inventory
SET quantity = quantity - 1
WHERE item_id = 505 AND quantity > 0;
COMMIT;Using `BEGIN IMMEDIATE` guarantees that the writer reserves write access right away, preventing unexpected database busy errors mid-transaction.
Ready for Data Types in SQLite?
Take a quick test to evaluate yourself, or move straight to the next module.