SQLite - Transactions

SQLite - Managing Data Consistency with Transactions

A transaction is a sequence of operations performed as a single logical unit of work. In database systems, transactions are essential for maintaining data integrity, especially in multi-user environments or during complex, multi-step operations.

All operations within a transaction are treated as an “all or nothing” proposition: either all of them succeed, or none of them are saved to the database.

The ACID Properties

Transactions are defined by four standard properties, known by the acronym ACID:

• Atomicity: Guarantees that all commands in a transaction are completed successfully. If one fails, the entire transaction is rolled back, and the database is left unchanged.
• Consistency: Ensures that any transaction will bring the database from one valid state to another. All integrity constraints must be satisfied.
• Isolation: Ensures that concurrent transactions produce the same database state that would have been obtained if transactions were executed serially. Changes made in one transaction are not visible to others until it is committed.
• Durability: Guarantees that once a transaction has been committed, it will remain so, even in the event of a power loss, crash, or error.

Core Transaction Commands

• BEGIN TRANSACTION: Starts a new transaction. You can use the shorthand BEGIN.
• COMMIT: Saves all changes made during the transaction to the database, making them permanent. You can also use END TRANSACTION.
• ROLLBACK: Discards all changes made since the transaction began, restoring the database to its state at the start of the transaction.

These commands primarily apply to DML statements (INSERT, UPDATE, DELETE). DDL statements like CREATE TABLE or DROP INDEX operate in their own auto-committed transactions and cannot be rolled back.

Practical Example: COMMIT and ROLLBACK

Let’s see how transactions work. First, we will try to delete two rows but then ROLLBACK the changes.

-- Check initial state: We have 2 employees with AGE = 25
SELECT NAME, AGE FROM COMPANY WHERE AGE = 25;
-- NAME  | AGE
-- Allen | 25
-- Mark  | 25

BEGIN TRANSACTION;

-- Delete the employees
DELETE FROM COMPANY WHERE AGE = 25;

-- Inside the transaction, the rows are gone
SELECT COUNT(*) FROM COMPANY WHERE AGE = 25; -- returns 0

-- Now, undo the deletion
ROLLBACK;

-- After rollback, the data is restored
SELECT NAME, AGE FROM COMPANY WHERE AGE = 25;
-- NAME  | AGE
-- Allen | 25
-- Mark  | 25

Now, let’s perform the same deletion but COMMIT the changes to make them permanent.

BEGIN TRANSACTION;

DELETE FROM COMPANY WHERE AGE = 25;

COMMIT;

-- After commit, the deletion is permanent
SELECT COUNT(*) FROM COMPANY WHERE AGE = 25; -- returns 0

Advanced Control: Savepoints

For more complex operations, SQLite supports nested transactions through SAVEPOINT. This allows you to roll back to an intermediate point within a larger transaction.

• SAVEPOINT name;: Creates a new savepoint.
• ROLLBACK TO SAVEPOINT name;: Undoes changes made after the specified savepoint, but keeps the transaction active.
• RELEASE SAVEPOINT name;: Deletes a savepoint. If you COMMIT or ROLLBACK the main transaction, all savepoints are released automatically.

BEGIN TRANSACTION;

-- Initial change
UPDATE COMPANY SET SALARY = SALARY + 1000 WHERE NAME = 'Paul';

SAVEPOINT my_savepoint;

-- A second change, which we might want to undo
DELETE FROM COMPANY WHERE NAME = 'Teddy';

-- Something went wrong, let's undo the deletion but keep the salary update
ROLLBACK TO SAVEPOINT my_savepoint;

-- The salary update for Paul is still staged, but Teddy is back
COMMIT;
-- Now Paul's salary increase is permanent.