A logical unit of work that performs one or more database operations (reads/writes). Ensures ACID properties. Consistency might be temporarily lost while a transaction is running. Multiple transactions can run in parallel.
Transaction States
| State | Description | Possible Next States |
|---|---|---|
| Active | Initial state. Executing. | Partially Committed, Failed |
| Partially Committed | Final operation done. Waiting to commit. | Failed, Committed |
| Failed | Error occurred. Cannot proceed normally. | Aborted |
| Aborted | Rolled back; database restored to previous state. | Active (if retrying) |
| Committed | Completed successfully. | - |
If the failure is temporary, can retry.
SQL Commands
BEGIN; -- start a transaction
-- ... operations ...
COMMIT; -- persist all changes
ROLLBACK; -- undo all changes since BEGIN
SAVEPOINT sp1; -- set a named savepoint
ROLLBACK TO SAVEPOINT sp1;-- rollback to savepoint onlyConcurrency Anomalies
Problems that arise when multiple transactions run concurrently without proper isolation.
- Dirty Read
Transaction reads uncommitted data written by another transaction. Data may later be rolled back. - Non-Repeatable Read
Transaction reads the same row twice and gets different values because another transaction modified and committed that row in between. - Phantom Read
Transaction re-executes a query and gets a different set of rows because another transaction inserted or deleted rows in between.
Isolation Levels
Higher isolation prevents more anomalies at the cost of reduced concurrency.
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | possible | possible | possible |
| Read Committed | prevented | possible | possible |
| Repeatable Read | prevented | prevented | possible |
| Serializable | prevented | prevented | prevented |