Transaction Characteristics

MySQL transactions follow the ACID principles:

• Atomicity: All operations within a transaction either complete successfully or fail entirely. Implemented through undo logs.
• Consistency: Data remains in a valid state throughout the transaction. Maintained by the combination of all ACID properties.
• Isolation: Concurrent transactions don't interfere with each other. Prevents issues like dirty reads, non-repeatable reads, and phantom reads. Implemented through locks and MVCC.
• Durability: Completed transactions persist even after system failures. Implemented through redo logs.

Concurrency Issues

When multiple transactions operate on the same data simultaneously, several problems can arise:

• Dirty Write: One transaction overwrites uncommitted changes from another transaction.
• Dirty Read: A transaction reads data that hasn't been commtited by another transaction.
• Non-repeatable Read: The same query returns different results within the same transaction due to external modifications.
• Phantom Read: New rows appear in subsequent queries within the same transaction due to external insertions.

Transaction Isolation Levels

MySQL supports four isolation levels:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read (default)
4. Serializable

Checking the current isolation level:

SHOW VARIABLES LIKE 'transaction_isolation';

Setting the isolation level:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Lock Mechanisms

MySQL provides two main locking strategies:

Optimistic Locking

Optimistic locking assumes minimal conflicts and uses versioning to handle concurrent updates:

-- Select with version
SELECT version, balance FROM financial_accounts WHERE account_id = 101;

-- Update with version check
UPDATE financial_accounts 
SET balance = 25000, version = version + 1 
WHERE account_id = 101 AND version = 5;

Pessimistic Locking

Pessimistic locking assumes conflicts are likely and acquires locks upfront:

• Shared Lock (Read Lock): Multiple transactions can read simultaneously but prevent writes.

SELECT * FROM products WHERE product_id = 42 LOCK IN SHARE MODE;

24. Exclusive Lock (Write Lock): Prevents other transactions from reading or writing.

SELECT * FROM products WHERE product_id = 42 FOR UPDATE;

Lock Granularity

• Row Locks: Fine-grained locking on individual rows. Highest concurrency but potential for deadlocks.
• Table Locks: Coarse-grained locking on entire tables. Lower concurrency but no deadlocks.
• Page Locks: Intermediate granularity (only in BDB engine). Balance between row and table locks.
• Intent Locks: Internal locks that indicate the intention to acquire row or table locks.

MVCC (Multi-Version Concurrency Control)

MVCC enables concurrent access without traditional locking by maintaining multiple versions of data:

Undo Log Chains

Each transaction creates an undo log that records changes and can be used to roll back or reconstruct previous states.

Read View

A snapshot of the transaction state at query time that determines which data versions are visible:

• Minimum transaction ID of active transactions
• Maximum transaction ID at view creation
• List of uncommitted transaction IDs

Visibility rules:

1. If transaciton ID < minimum ID: visible
2. If transaction ID > maximum ID: invisible
3. If between minimum and maximum:
   • If in uncommitted list: invisible
   • If not in uncommitted list: visible

Isolation Level Differences

• Read Committed: New read view for each query
• Repeatable Read: Same read view for entire transaction

Transaction Monitoring

Monitoring lock contention:

SHOW STATUS LIKE 'innodb_row_lock%';

Viewing transaction and lock information:

-- Transaction information
SELECT * FROM information_schema.innodb_trx;

-- Lock information (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks;

-- Lock waits (MySQL 8.0+)
SELECT * FROM performance_schema.data_lock_waits;

Common Questions

• Why are INSERT/UPDATE/DELETE statements locked? To prevent conflicts like duplicate primary keys and ensure data integrity.
• How does Repeatable Read prevent phantom reads? Through gap locks that block inserts in ranges being queried.
• How to view and release table locks? Use SHOW OPEN TABLES and UNLOCK TABLES.