SQL Transactions
Summary
A transaction is a group of related operations to the database that ensures “all or nothing” execution, providing isolation from other transactions.
See transaction management for more in depth.
Details
Definition: A transaction groups related operations, ensuring that all operations complete successfully or none at all. We have transactions because the server can crash and if that happens, we want to make sure that the database is stable.
Basic SQL Syntax:
BEGIN;
COMMIT;
ROLLBACK;
Savepoints allow partial rollbacks within transactions. They are only executed if an error occur.
SAVEPOINT <name>;
ROLLBACK TO SAVEPOINT <name>;
If a crash occurs during operations without transactions, executed states may remain, but not all operations may succeed. Transactions allow rolling back to a clean state.
Transactions in PostgreSQL
-
By default, every SQL statement is a transaction.
-
To override this behavior:
BEGIN; ... COMMIT;/ROLLBACK; -
Some DDL statements implicitly commit transactions.
-
Calling a function starts a transaction, meaning functions have transactional properties. Errors will abort the transaction, erasing all previous operations.
Errors and Exception Handling
- Errors inside functions cannot simply invoke
ROLLBACK; exceptions must be raised and handled appropriately.
Interleaving Transactions
Interleaving transactions is when multiple transactions are executed simultaneously, with their operations mixed together. This improves system performance but requires careful management to avoid issues like data inconsistencies or conflicts. Techniques like locking and isolation are used to ensure transactions don’t interfere with each other.
| Transaction 1 | Transaction 2 |
|---|---|
| Reads and updates a user’s account balance. | Reads and updates the same user’s account balance. |
| If both transactions are interleaved, their operations might be mixed, with some steps from Transaction 1 happening between steps of Transaction 2. This requires careful locking and transaction management to prevent issues like double-spending or incorrect calculations. |
Examples
-- Start a transaction
BEGIN;
-- Create a savepoint before any changes are made
SAVEPOINT before_transfer;
-- Subtract 200 from Alice's account
UPDATE Accounts
SET balance = balance - 200
WHERE account_name = 'Alice';
-- Add 200 to Bob's account
UPDATE Accounts
SET balance = balance + 200
WHERE account_name = 'Bob';
-- Something could go wrong here (e.g., wrong transfer amount)
-- Rollback to the savepoint to undo all changes
ROLLBACK TO before_transfer; -- This runs if an error occur
-- Commit the transaction (with no changes, since we rolled back)
COMMIT; -- This runs if no errors occur