Transactions — BEGIN, COMMIT, ROLLBACK

Atomic batches of work

A transaction is a group of statements treated as one atomic unit: either all of them apply or none of them do. SQLite is fully ACID by default — even a single statement runs inside an implicit transaction.

Three explicit shapes:

BEGIN; ... COMMIT; — explicit transaction, commit on success.
BEGIN; ... ROLLBACK; — abort all changes since BEGIN.
SAVEPOINT — nested transaction-like checkpoint inside an outer transaction; RELEASE on success, ROLLBACK TO on failure.

SQLite's transaction modes:

DEFERRED (default) — locks acquired only when needed.
IMMEDIATE — write lock acquired at BEGIN; other writers block immediately.
EXCLUSIVE — exclusive lock; other readers and writers block.

Tip: For 'either I write everything or I write nothing' workflows where you might race another writer, use BEGIN IMMEDIATE. The default DEFERRED can fail at COMMIT time with SQLITE_BUSY if another writer beat you to it — IMMEDIATE fails fast at BEGIN.

Code

Explicit transaction in SQL·sql

BEGIN IMMEDIATE;

INSERT INTO conversations(title) VALUES ('New chat');
INSERT INTO messages(conversation_id, role, content)
VALUES (last_insert_rowid(), 'user', 'Hello');

-- If anything fails before COMMIT, the whole batch can be rolled back:
-- ROLLBACK;

COMMIT;

Python — context manager handles commit/rollback·python

import sqlite3

conn = sqlite3.connect('demo.db')
conn.execute('PRAGMA foreign_keys = ON')

try:
    with conn:                                  # implicit BEGIN/COMMIT
        conv_id = conn.execute(
            'INSERT INTO conversations(title) VALUES (?)',
            ('New chat',)
        ).lastrowid
        conn.execute(
            'INSERT INTO messages(conversation_id, role, content) VALUES (?,?,?)',
            (conv_id, 'user', 'Hello'),
        )
        # An exception here triggers ROLLBACK automatically.
except sqlite3.IntegrityError:
    print('rolled back')

Transactions — BEGIN, COMMIT, ROLLBACK

Atomic batches of work

Code

External links

Exercise

Progress

Comments 0