1 Introduction to Transactions Explained

Key Concepts

1. Transaction Definition
2. ACID Properties
3. Transaction States
4. Transaction Control Language (TCL)
5. Concurrency Control
6. Isolation Levels

1. Transaction Definition

A transaction is a single unit of work that is performed against a database. It can include one or more database operations, such as insert, update, or delete. Transactions ensure that all operations within the transaction are completed successfully, or none of them are, maintaining data integrity.

2. ACID Properties

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably.

• Atomicity: Ensures that a transaction is treated as a single unit, which either succeeds completely or fails completely.
• Consistency: Ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants.
• Isolation: Ensures that concurrently executing transactions do not interfere with each other.
• Durability: Ensures that once a transaction has been committed, it will remain so, even in the event of a system failure.

3. Transaction States

Transactions can be in various states:

• Active: The initial state when a transaction is executing.
• Partially Committed: After the final statement has been executed.
• Committed: After successful completion.
• Failed: After the discovery that normal execution can no longer proceed.
• Aborted: After the transaction has been rolled back and the database restored to its state before the transaction began.
• Terminated: The final state after the transaction has been committed or aborted.

4. Transaction Control Language (TCL)

TCL commands are used to manage transactions by controlling the changes made by DML (Data Manipulation Language) statements. The primary TCL commands are:

• COMMIT: Saves all work done in the transaction.
• ROLLBACK: Restores the database to the last committed state.
• SAVEPOINT: Sets a savepoint within a transaction.
• ROLLBACK TO SAVEPOINT: Rolls back to a savepoint.

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456;
SAVEPOINT TransferSavepoint;
-- Some other operations
ROLLBACK TO TransferSavepoint;
COMMIT;

5. Concurrency Control

Concurrency control ensures that multiple transactions can execute concurrently without leading to inconsistencies in the database. Techniques include locking, timestamping, and multiversion concurrency control.

6. Isolation Levels

Isolation levels define the degree to which one transaction must be isolated from the effects of other transactions. Common isolation levels include:

• Read Uncommitted: Allows dirty reads.
• Read Committed: Prevents dirty reads but allows non-repeatable reads.
• Repeatable Read: Prevents non-repeatable reads but allows phantom reads.
• Serializable: Prevents all anomalies.

Example of setting isolation level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM Accounts WHERE AccountID = 123;
-- Some other operations
COMMIT;

Understanding transactions and their properties is crucial for maintaining data integrity and ensuring reliable database operations. By mastering these concepts, you can handle complex database tasks with confidence and efficiency.