A transaction is a set of SQL operations that are treated as a single, indivisible unit of work within a database.
- Either all operations are successfully completed, or none are applied.
- Transactions ensure data consistency and reliability, even in multi-user or failure-prone environments.
Transactions are fundamental in Database Management Systems (DBMS) to guarantee safe modifications, protecting data against errors, power loss, or concurrent access.
ACID Properties
Transactions must satisfy the ACID properties:
- Atomicity: all operations are executed, or none.
- Consistency: the database moves from one valid state to another.
- Isolation: each transaction behaves as if it is running alone.
- Durability: once confirmed, the transaction is permanent, even in case of system failures.
Example
Bank transfer between accounts:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
- If the transaction reaches
COMMIT→ both changes are saved. - If an error occurs and
ROLLBACKis issued → all changes are undone, and the database returns to its initial state.
See also
- COMMIT (SQL)
- ROLLBACK
- ACID properties
