A database transaction groups database operations into a unit that can be committed or rolled back. Transaction atomicity means that its updates take effect together or not at all. Transaction durability concerns retaining an acknowledged committed result after a failure; transaction isolation controls which effects concurrent database transactions may observe.[ref]
Transactions in the PostgreSQL documentation. version 18, section 3.4, all-or-nothing updates, permanent recording and visibility
For example, transferring seven units from an account with balance twenty to one with balance five should produce balances thirteen and twelve. A failure after only the debit must not leave thirteen and five as a completed transfer. In SQL, a transaction block uses
BEGIN, then the related updates, then COMMIT; ROLLBACK abandons the uncommitted changes.[ref]Isolation is a separate question from grouping statements. Two concurrent transfers can each read an old balance before either writes. A correct application must select suitable transaction isolation or locking and handle possible conflicts; merely putting the statements in a database transaction does not establish every desired invariant. The corresponding database schedule records their interleaving.
Transaction isolation specifies which effects concurrent database transactions may observe and which interleavings are permitted to commit. Serializable isolation requires the combined result to be equivalent to some serial ordering, even when execution actually overlaps. Weaker levels admit particular anomalies; their names require implementation-specific interpretation.[ref]
For example, two doctors are on call. Each transaction reads that both are available and removes its own doctor, intending to leave someone on call. If each sees an old snapshot and they update different rows, both may commit under a snapshot-based level that does not prevent this write skew. No serial ordering would let both checks pass. PostgreSQL Serializable can reject such conflicting executions, so the application must be prepared to retry a whole transaction. Isolation protects a stated concurrency model; it does not repair a faulty business rule within one transaction.
Transaction durability means that an acknowledged committed transaction survives failures covered by the database's durability model. A successful statement that has not committed is not yet such a promise. The failure model and storage configuration matter: surviving a process crash does not automatically mean surviving destruction of every storage replica.
Write-ahead logging provides one mechanism. Before reporting a durable commit, persist the required log records. Modified data pages can be written later because recovery can replay their committed changes from the log. The log-before-data rule also prevents a page from reaching persistent storage without the recovery information needed to interpret it.[ref]
Write-Ahead Logging in the PostgreSQL documentation. §28.3, WAL ordering and REDO
Durability therefore concerns persistence of the committed result, whereas transaction atomicity concerns which parts of a transaction may take effect. Faster asynchronous commit settings can deliberately weaken the interval covered by the acknowledgment.Transaction atomicity gives a database transaction an all-or-nothing effect: either its committed updates take effect together or its partial updates do not remain as a completed result. This can be implemented with undo information, version visibility, recovery logging or combinations of these mechanisms. It does not require every physical write to happen at the same instant.
For example, transferring five units between accounts requires a debit and a credit. If an error occurs after the debit, aborting the transaction must not leave only that debit as the final database result. SQL transaction blocks use
BEGIN, COMMIT and ROLLBACK to express the boundary.[ref] Atomicity alone does not say how transactions interact before completion; transaction isolation governs that. Nor does it make a committed result survive a crash; that is transaction durability. External effects such as a sent email require coordination beyond rolling back the database changes.An ordering of operations of several database transactions that preserves the operation order within each database transaction.
A database schedule that can be turned into a serial database schedule by swapping adjacent operations of different database transactions that do not conflict. Reads and writes of the same object conflict when at least one is a write.
A directed graph with a vertex for each database transaction and an edge from the earlier to the later participant in each conflicting pair of operations. A database schedule is a conflict-serializable database schedule exactly when this directed graph has no directed cycle.
A database schedule in which every database transaction runs as one contiguous group, without interleaving another database transaction.
Articles by others on the same topic
There are currently no matching articles.