Transactions in SQL

Transactions in SQL

Section outline

• Introduces the concept of database operations being executed one at a time and atomically.
• Discusses the complexities of real-world scenarios where operations can fail mid-process, leaving the database in an inconsistent state.
• Sets the stage for discussing SQL tools that ensure data integrity during concurrent operations and system failures.

#1

read: Section 6.6: Transactions in SQL (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces the concept of database operations being executed one at a time and atomically.
- Discusses the complexities of real-world scenarios where operations can fail mid-process, leaving the database in an inconsistent state.
- Sets the stage for discussing SQL tools that ensure data integrity during concurrent operations and system failures.

Herein we discuss the concept of transactions in the Structured Query Language. We begin by examining the idealized model of database operations and contrast it with the complexities encountered in practical applications.

• The Model of Database Operations

  Our initial model for database operations is that of a single user executing queries or modifications. Operations are performed one at a time. The state of the database after one operation serves as the initial state for the next. We also assume that operations are “atomic”; they are carried out in their entirety, without the possibility of an intermediate failure leaving the database in an inconsistent state.

• Complications in Practice

  Real-world systems are more complex. Hundreds of operations per second may be initiated from thousands of sites. It is possible for two operations affecting the same data to overlap in time, leading to unforeseen interactions. Furthermore, hardware or software may fail during a modification, corrupting the database state.

• Serializability

  When operations execute concurrently, their constituent steps may be interleaved. This can lead to outcomes that would not be possible if the operations were executed serially, one after the other. The goal of serializability is to permit concurrent execution while ensuring that the outcome is equivalent to some serial execution order. Without this guarantee, two customers might book the same airline seat, as each reads the seat’s status as “available” before the other’s update is made permanent.

• Atomicity

  A single logical task, such as a bank transfer, may require multiple distinct update operations. An atomic operation is one that is indivisible; it either completes in its entirety, or it has no effect at all. If a system crashes after one part of a multi-part task has completed but before the other parts have, the database is left in an inconsistent state. For example, funds might be credited to one account but not debited from the source account. Atomicity prevents such partial executions.

• Transactions in SQL

  SQL introduces the concept of a transaction to solve the problems of serializability and atomicity. A transaction is a sequence of operations that is executed as a single, logical unit. SQL ensures that transactions are atomic and, by default, serializable, thus providing the programmer with the necessary tools to maintain data integrity.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:50:10
• Duration: 18.968214988708496
• Cummulative cost: $2.24e-02

Serializability

Section outline

• Explores the issues arising from concurrent operations in high-traffic applications like web services, banking, or airline reservations.
• Defines the problem of overlapping operations interacting in unexpected ways.
• Introduces the concept of serializability, where the outcome of concurrent transactions must be as if they were executed one after another in some order.

#2

read: Section 6.6.1: Serializability (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Explores the issues arising from concurrent operations in high-traffic applications like web services, banking, or airline reservations.
- Defines the problem of overlapping operations interacting in unexpected ways.
- Introduces the concept of serializability, where the outcome of concurrent transactions must be as if they were executed one after another in some order.

• Concurrent Database Operations

  In applications such as banking, airline reservations, or web services, a database may be subjected to hundreds of operations per second. These operations, initiated from numerous distinct sites, may affect the same data elements and overlap in their execution time.

• Anomalies from Interleaved Execution

  If a Database Management System (DBMS) does not constrain the order in which it executes concurrent operations, interactions among them may lead to an inconsistent database state. Consider two users attempting to book the same airline seat. Both may query the database, find the seat available, and proceed to book it. This interleaving of operations results in an incorrect state where the same seat is assigned to two different customers.

• Serializability as a Correctness Criterion

  The solution to these concurrency problems is to group operations into transactions and enforce serializable behavior. A system that guarantees serializability ensures that the outcome of any interleaved execution of transactions is identical to the outcome of some execution where the transactions are run serially, one after another, without any overlap. If the airline booking operations were run serially, the second customer would find the seat already occupied by the first.

Definition: Serializable Schedule

Let a set of transactions be \(T = \{T_1, T_2, \dots, T_n\}\). A schedule \(S\) over \(T\) is a sequence of the operations from the transactions in \(T\) that preserves the order of operations within each individual transaction.

1. A schedule is serial if, for every transaction \(T_i \in T\), all operations of \(T_i\) are executed consecutively in the schedule.

2. A schedule \(S\) is serializable if it is computationally equivalent to some serial schedule \(S'\). Equivalence implies that for any initial state of the database, the final state of the database after executing schedule \(S\) is the same as the final state after executing the serial schedule \(S'\).

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:50:27
• Duration: 16.646049976348877
• Cummulative cost: $1.93e-02

Atomicity

Section outline

• Discusses the problem of a single operation being interrupted by a hardware or software crash, which can leave the database in an inconsistent state.
• Defines atomicity as the ‘all-or-nothing’ property of transactions, meaning either all operations within a transaction are completed successfully, or none are.

#3

read: Section 6.6.2: Atomicity (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Discusses the problem of a single operation being interrupted by a hardware or software crash, which can leave the database in an inconsistent state.
- Defines atomicity as the 'all-or-nothing' property of transactions, meaning either all operations within a transaction are completed successfully, or none are.

• The Problem of Incomplete Operations

  In addition to concurrency issues, a single database operation, which may consist of several steps, can be interrupted. A hardware or software crash occurring mid-operation can leave the database in an inconsistent and invalid state. For instance, a fund transfer between two bank accounts involves two updates: a credit to one account and a debit from another. A failure between these two steps results in an incorrect total sum of money in the system.

• The Principle of Atomicity

  To prevent inconsistencies from partial executions, operations are grouped into transactions. Atomicity ensures that a transaction is an indivisible, “all-or-nothing” unit. Either all operations within the transaction are successfully completed and their results made permanent, or none are. If a transaction is interrupted, any partial changes are undone, and the database is restored to its state before the transaction began.

Definition: Atomicity

Let a transaction \(T\) be a sequence of operations \(T = \langle o_1, o_2, \dots, o_n \rangle\). Let \(S\) be the state of the database before \(T\) begins, and let \(S'\) be the state after \(T\) executes.

The property of atomicity dictates that the execution of \(T\) must be equivalent to one of two outcomes:

1. Commit: All operations \(\langle o_1, \dots, o_n \rangle\) are executed successfully. The database transitions from state \(S\) to state \(S'\).
2. Abort: The transaction fails to complete. The database remains in state \(S\), as if none of the operations were ever attempted.

There can be no intermediate state where only a subset of the operations \(\{o_i, \dots, o_j\} \subset T\) has been applied to the database.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:50:41
• Duration: 13.702900886535645
• Cummulative cost: $1.36e-02

Transactions

Section outline

• Presents transactions as the solution to the problems of serializability and atomicity.
• Defines a transaction as a group of database operations that must be executed atomically.
• Explains that SQL defaults to executing transactions in a serializable manner.

#4

read: Section 6.6.3: Transactions (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Presents transactions as the solution to the problems of serializability and atomicity.
- Defines a transaction as a group of database operations that must be executed atomically.
- Explains that SQL defaults to executing transactions in a serializable manner.

The problems of ensuring serializability and atomicity are addressed by grouping database operations into transactions.

• A transaction is a collection of operations on the database that are treated as a single, indivisible unit.

  This principle is known as atomicity. The transaction must either execute in its entirety or not at all. If a failure occurs midway, any changes made by the transaction are undone.

Definition: Transaction

A transaction is a collection of one or more operations on the database that must be executed atomically. This means either all operations within the transaction are successfully completed and their results made permanent, or none of them are.

• By default, SQL mandates that transactions execute in a serializable manner.

  This ensures that the concurrent execution of several transactions produces an outcome equivalent to some sequential execution of those same transactions. A Database Management System may permit the user to specify a less stringent constraint on the interleaving of operations.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:50:50
• Duration: 9.296241044998169
• Cummulative cost: $1.00e-02

Read-Only Transactions

Section outline

• Discusses transactions that only read data and do not modify it.
• Explains that read-only transactions allow for greater parallelism and can be optimized by the database system.
• Introduces the SET TRANSACTION READ ONLY statement to declare a transaction as read-only.

#5

read: Section 6.6.4: Read-Only Transactions (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Discusses transactions that only read data and do not modify it.
- Explains that read-only transactions allow for greater parallelism and can be optimized by the database system.
- Introduces the `SET TRANSACTION READ ONLY` statement to declare a transaction as read-only.

A transaction that only reads data, without performing any modifications, presents opportunities for optimization by the database system. Such transactions are not subject to the same serialization problems as those that write data, allowing for greater concurrency.

Definition: Read-Only Transaction

A transaction \(T\) is defined as read-only if its set of operations contains only read operations and no write operations (such as INSERT, UPDATE, or DELETE). Consequently, the state of the database before the execution of \(T\) is identical to the state after \(T\) commits.

• Increased Parallelism

  Transactions that are guaranteed not to alter the database can be executed in parallel with other transactions, including other read-only transactions and, in some cases, read-write transactions. The system does not need to establish stringent locks, as there is no risk of write conflicts.

• System Optimization

  When the SQL execution system is informed that a transaction is read-only, it can leverage this knowledge to bypass locking mechanisms and other overhead associated with maintaining serializability for write operations. This results in improved performance and system throughput.

• SQL Declaration

  To declare the intent for the next transaction to be read-only, SQL provides a specific statement. This statement must be executed before the transaction begins.

  SET TRANSACTION READ ONLY;

  The default behavior for a transaction is to be read-write, which can be explicitly stated with SET TRANSACTION READ WRITE;.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:51:04
• Duration: 14.230252027511597
• Cummulative cost: $1.47e-02

Dirty Reads

Section outline

• Defines ‘dirty data’ as data written by a transaction that has not yet committed.
• Defines a ‘dirty read’ as reading this uncommitted data.
• Discusses the risks of dirty reads, as the writing transaction might later abort, invalidating the data that was read.
• Mentions the trade-offs of allowing dirty reads, such as increased parallelism.

#6

read: Section 6.6.5: Dirty Reads (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Defines 'dirty data' as data written by a transaction that has not yet committed.
- Defines a 'dirty read' as reading this uncommitted data.
- Discusses the risks of dirty reads, as the writing transaction might later abort, invalidating the data that was read.
- Mentions the trade-offs of allowing dirty reads, such as increased parallelism.

• Dirty Data

  Data that has been written by a transaction that has not yet committed is referred to as “dirty data.”

• Dirty Read

  A “dirty read” occurs when a transaction reads dirty data that was written by another concurrent transaction.

Definition: Dirty Read

Let \(T_i\) and \(T_j\) be two distinct transactions. A dirty read phenomenon occurs if transaction \(T_j\) is permitted to read a data item \(x\) that has been modified by transaction \(T_i\), before \(T_i\) has committed. This situation can be represented by the following history:

\[... w_i(x) ... r_j(x) ...\]

where \(w_i(x)\) denotes a write operation on data item \(x\) by \(T_i\), and \(r_j(x)\) denotes a read operation on the same data item by \(T_j\). This sequence of operations occurs before \(T_i\) executes either a COMMIT or ROLLBACK operation.

• Risks of Dirty Reads

  The principal risk associated with a dirty read is that the transaction which wrote the data may subsequently abort. If an abort occurs, the dirty data is removed from the database as if it never existed. However, any transaction that read the dirty data may have already committed or taken actions based on this now-invalid data, leading to an inconsistent database state.

• Trade-offs

  Permitting dirty reads can be advantageous in certain contexts. It allows for a greater degree of parallelism, as transactions do not have to wait for other transactions to commit before reading their written data. Furthermore, it avoids the overhead incurred by the DBMS to implement the locking mechanisms necessary to prevent such reads. The READ UNCOMMITTED isolation level in SQL explicitly allows for dirty reads.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:51:17
• Duration: 12.361756324768066
• Cummulative cost: $1.36e-02

Other Isolation Levels

Section outline

• Introduces the four isolation levels provided by SQL: SERIALIZABLE, READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ.
• Explains how to set the isolation level for a transaction using the SET TRANSACTION statement.
• Describes the behavior and guarantees of each isolation level.

#7

read: Section 6.6.6: Other Isolation Levels (section)

model: pro
rules: bullet definition math presentation short


Present the following.  Use definition formal definition when it applies.

- Introduces the four isolation levels provided by SQL: `SERIALIZABLE`, `READ UNCOMMITTED`, `READ COMMITTED`, and `REPEATABLE READ`.
- Explains how to set the isolation level for a transaction using the `SET TRANSACTION` statement.
- Describes the behavior and guarantees of each isolation level.

SQL provides four isolation levels to manage the trade-off between concurrency and data consistency. A transaction’s isolation level dictates the degree to which it is isolated from the effects of other concurrently running transactions. The level for a given transaction is specified using the SET TRANSACTION statement.

• READ UNCOMMITTED

This is the lowest isolation level. A transaction operating at this level is permitted to read data that has been modified by other transactions but not yet committed. This phenomenon is known as a “dirty read.” It offers the highest concurrency but carries significant risks, as the read data may be rolled back and thus never officially exist in the database.

• READ COMMITTED

This level ensures that a transaction will only read data that has been committed. It prevents dirty reads. However, it does not guarantee that re-reading the same data within the same transaction will yield the same result; this is because another transaction might commit changes between the reads. This issue is termed a “non-repeatable read.”

• REPEATABLE READ

This level is more restrictive than READ COMMITTED. It guarantees that any data read by a transaction cannot be changed by other transactions until the first transaction finishes. This prevents non-repeatable reads. However, it does not prevent “phantom reads,” where new rows inserted by other transactions may appear in subsequent queries within the same transaction.

• SERIALIZABLE

This is the highest level of isolation. It ensures that the execution of concurrent transactions produces the same result as if the transactions were executed serially, one after the other. This level prevents dirty reads, non-repeatable reads, and phantom reads. It provides the strongest guarantee of consistency at the cost of reduced concurrency. This is the default isolation level in the SQL standard.

gemini-2.5-pro

• Version: 1
• Model: gemini-2.5-pro
• Created: 2025-09-20 13:51:30
• Duration: 13.496889114379883
• Cummulative cost: $1.42e-02