📘 Transaction Control

Transaction control in SQL allows you to manage database operations by grouping them into logical units of work, ensuring data consistency and integrity. This section covers the essential transaction control commands: `BEGIN`, `COMMIT`, `ROLLBACK`, and `SAVEPOINT`.

• `BEGIN`: Starts a new transaction.
• `COMMIT`: Permanently saves all changes made in the current transaction.
• `ROLLBACK`: Discards all changes made in the current transaction, reverting to the state before `BEGIN`.
• `SAVEPOINT`: Creates a temporary checkpoint within a transaction that can be rolled back to without affecting previous savepoints.

💡 Key Transaction Properties (ACID)

• Atomicity: All operations in a transaction are completed, or none.
• Consistency: Transactions ensure data remains valid according to defined rules.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once committed, changes persist even after system failures.

✅ Example Transaction Workflow

BEGIN;

-- Perform database operations
e.g. INSERT INTO users (name) VALUES ('John');

SAVEPOINT my_savepoint;

-- Potentially problematic operation
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

ROLLBACK TO my_savepoint;

-- Try alternative approach
INSERT INTO transactions (amount, type) VALUES (100, 'debit');

COMMIT;

💡 Best Practices for Transactions

• Keep transactions short to minimize locking and maximize concurrency.
• Use savepoints for complex operations that may need partial rollback.
• Avoid long-running transactions, especially in OLTP systems.
• Test transaction logic thoroughly under various failure scenarios.

❌ Common Mistakes to Avoid

// Don't leave transactions open indefinitely
BEGIN;
-- Some operations
e.g. SELECT * FROM users; // This locks rows in some databases
// No commit or rollback issued -> leads to lock contention and database hang-ups

💡 Important Notes on Isolation Levels

• Read Committed: Prevents dirty reads (default in many databases).
• Repeatable Read: Prevents non-repeatable reads and phantom reads.
• Serializable: Full isolation, but may impact concurrency performance.
• Use the lowest isolation level that meets your application's needs.

✅ Handling Deadlocks

• Design your database schema to minimize contention.
• Order transactions consistently (e.g., always lock accounts in user_id order).
• Use timeouts to detect deadlocks and retry operations.
• Implement transaction recovery mechanisms for rolled-back operations.

📘 ACID Properties

ACID properties are fundamental to ensuring data integrity in relational databases. ACID stands for **Atomicity**, **Consistency**, **Isolation**, and **Durability**.

💡 Atomicity

Atomicity ensures that a database transaction is treated as a single unit of work. A transaction must either complete entirely or not happen at all.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;
COMMIT;

• `COMMIT` saves the transaction permanently.
• `ROLLBACK` undoes all changes made during a transaction.

START TRANSACTION;
UPDATE accounts SET balance = 100 WHERE account_id = 123;
ROLLBACK;

💡 Consistency

Consistency ensures that a transaction never leaves the database in an inconsistent state. If a transaction is completed, all data remains valid and consistent.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT CHECK (quantity > 0)
);

• Constraints ensure consistency.
• Transactions maintain consistent states.

💡 Isolation

Isolation ensures that concurrent transactions are isolated from each other. The isolation level determines how transactions interact with each other.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• Read committed: Prevents dirty reads.
• Repeatable read: Prevents non-repeatable reads and phantom reads.

💡 Durability

Durability ensures that once a transaction is committed, it remains in the database permanently. Even if there is a system crash or power failure.

COMMIT;

• WAL (Write-Ahead Logging) ensures durability.
• Data persists even after system restarts.

✅ Best Practices for ACID Transactions

• Use transactions to ensure data integrity.
• Keep transactions short and lightweight.
• Use appropriate isolation levels.

❌ Common Pitfalls to Avoid

• Avoid long-running transactions that block other operations.
• Don't neglect to test for concurrent access issues.

// Don't do this - long running transaction
START TRANSACTION;
... complex operations ...
SLEEP(10);
COMMIT;

📘 Isolation Levels

When working with database transactions, **isolation levels** determine how transaction data is isolated from other transactions. SQL defines four standard isolation levels: **Read Uncommitted**, **Read Committed**, **Repeatable Read**, and **Serializable**. Understanding these levels is crucial for managing concurrency and preventing issues like **dirty reads**, **uncommitted reads**, and **phantom reads**.

💡 The Four Isolation Levels

• **Read Uncommitted**: Allows reading uncommitted data (lowest isolation). Use with caution.
• **Read Committed**: Prevents dirty reads but allows other concurrency issues.
• **Repeatable Read**: Ensures consistent results for repeated queries within a transaction.
• **Serializable**: Full isolation, emulates sequential execution of transactions.

### Dirty Reads, Uncommitted Reads, and Phantom Reads - **Dirty Read**: Reading uncommitted data that may be rolled back. - **Uncommitted Dependency**: Relying on data that might change before commit. - **Phantom Read**: New records appearing in a result set between transactions.

💡 Read Uncommitted (Lowest Isolation)

This level allows reading uncommitted data from other transactions. Use this when: - You need maximum concurrency and don't mind potential inconsistencies. - The data being read doesn't require consistency across transactions.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

**Example**: Reading analytics data where occasional inconsistency is acceptable.

💡 Read Committed (Default in Many Databases)

This level prevents dirty reads but allows other concurrency issues like **phantom reads** and **uncommitted dependencies**. It's a good balance between performance and consistency.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

**Example**: E-commerce applications where orders should reflect committed data but don't need full isolation.

💡 Repeatable Read (Default in PostgreSQL, MySQL)

Ensures that all repeated queries within a transaction return the same results. Prevents **dirty reads**, **phantom reads**, and **uncommitted dependencies** except for updates by higher-level transactions.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

**Example**: Financial applications where consistency is critical but some concurrency is still needed.

💡 Serializable (Highest Isolation)

This level ensures transactions are executed serially, one after another. Prevents all types of concurrency issues but can impact performance due to increased locking.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

**Example**: Applications requiring strict consistency, like banking systems or inventory management.

💡 Best Practices for Choosing Isolation Levels

• Always use the **lowest necessary isolation level** to maximize concurrency.
• Consider database-specific behaviors (e.g., PostgreSQL's implementation of Repeatable Read).
• Use explicit transactions and locks when dealing with critical operations.
• Test performance under different workloads before choosing an isolation level.

❌ Common Mistakes to Avoid

• Don't use **Read Uncommitted** unless absolutely necessary.
• Avoid using **Serializable** in high-concurrency environments without testing.
• Don't rely on default isolation levels without understanding their implications.
• Don't ignore concurrency issues that can arise with lower isolation levels.

💡 Example Scenarios

// Example of setting isolation level in a transaction
BEGIN TRANSACTION;
SET ISOLATION LEVEL REPEATABLE READ;

**Scenario**: A banking application transferring funds between accounts. The transfer must be atomic and consistent.

📘 Deadlocks and Locking

In SQL, **transaction management** is crucial for maintaining data integrity and consistency. One of the most critical aspects of transaction management is handling concurrency control through locking mechanisms. This chapter explores the concepts of **optimistic locking**, **pessimistic locking**, and how to prevent **deadlocks** in database transactions.

💡 Optimistic Locking

**Optimistic locking** assumes that multiple transactions can complete without conflicting with each other. It allows concurrent modifications and only checks for conflicts when committing the transaction.

BEGIN TRANSACTION;
UPDATE orders SET status = 'completed' WHERE order_id = 100;
COMMIT;

• Reduces locking overhead and improves concurrency.
• Can lead to **lost updates** if not handled properly.
• Commonly used in environments with low contention.

💡 Pessimistic Locking

**Pessimistic locking** assumes that conflicts are likely and places locks on resources as soon as they are accessed. This prevents other transactions from modifying the same data until the lock is released.

BEGIN TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET name = 'John' WHERE id = 1;
COMMIT;

• Ensures data integrity by preventing conflicts.
• Can lead to **resource contention** and reduced concurrency.
• Commonly used in environments with high contention.

💡 Deadlocks: What They Are & How to Prevent Them

A **deadlock** occurs when two or more transactions are waiting indefinitely for locks to be released by other transactions. This creates a situation where no transaction can proceed, leading to database unresponsiveness.

• Occurs due to **circular waits** between transactions.
• Can be caused by **poorly designed locking mechanisms** or long-running transactions.
• Prevent using **timeout limits**, **lock ordering**, and **deadlock detection**.

/* Example of a potential deadlock scenario */
-- Transaction 1
BEGIN TRANSACTION;
SELECT * FROM tableA WHERE id = 1 FOR UPDATE;
SELECT * FROM tableB WHERE id = 2 FOR UPDATE;

-- Transaction 2
BEGIN TRANSACTION;
SELECT * FROM tableB WHERE id = 2 FOR UPDATE;
SELECT * FROM tableA WHERE id = 1 FOR UPDATE;

💡 Best Practices for Locking & Deadlock Prevention

• Use **row-level locking** instead of table-level locking.
• Keep transactions short and minimize lock contention.
• Order locks consistently across all transactions.
• Implement proper error handling for deadlocks.
• Monitor database performance to identify potential deadlock risks.