Introduction

This document explores transactions and concurrency control mechanisms in MySQL, focusing on how they guarantee data integrity and consistency in a multi-user environment. We'll cover the ACID properties, different concurrency control methods, and transaction isolation levels.

Transactions

A transaction is a logical unit of work that comprises one or more SQL statements executed as a single, indivisible operation. Either all operations within a transaction succeed, or all operations are rolled back to their original state.

Key Benefits of Transactions:

• Data Integrity: Ensures that data remains valid and accurate even when errors occur.
• Atomicity: Guarantees that a transaction is treated as a single unit.
• Consistency: Maintains the database's consistency rules and constraints.
• Isolation: Prevents transactions from interfering with each other.
• Durability: Ensures that changes made by a committed transaction are permanent, even in the event of a system failure.

ACID Properties

Transactions are characterized by the ACID properties:

• Atomicity: All operations in a transaction either complete successfully or are rolled back as if they never occurred.
• Consistency: A transaction transforms the database from one consistent state to another. It must adhere to all database rules and constraints.
• Isolation: Transactions are isolated from each other. Concurrent transactions should not interfere with each other's execution.
• Durability: Once a transaction is committed, its changes are permanent and will survive system failures.

Concurrency Control

Concurrency control is the process of managing simultaneous access to the database by multiple transactions to prevent data inconsistencies and conflicts. MySQL uses locking mechanisms to achieve concurrency control.

Locking Mechanisms

Locks are used to restrict access to database resources (e.g., rows, tables) by multiple transactions simultaneously.

• Shared Locks (Read Locks): Multiple transactions can hold shared locks on the same resource concurrently. They allow read access but prevent any transaction from acquiring an exclusive lock.
• Exclusive Locks (Write Locks): Only one transaction can hold an exclusive lock on a resource at a time. It allows both read and write access and prevents other transactions from acquiring any lock on the resource.

Deadlocks

A deadlock occurs when two or more transactions are blocked indefinitely, waiting for each other to release locks. MySQL can detect and resolve deadlocks by aborting one of the transactions (usually the one with the least amount of work done) and rolling it back.

Example of a deadlock:

1. Transaction A acquires a lock on row 1.
2. Transaction B acquires a lock on row 2.
3. Transaction A attempts to acquire a lock on row 2 but is blocked because Transaction B holds it.
4. Transaction B attempts to acquire a lock on row 1 but is blocked because Transaction A holds it.

Transaction Isolation Levels

Transaction isolation levels determine the degree to which transactions are isolated from each other. Higher isolation levels provide stronger consistency guarantees but can reduce concurrency and performance. MySQL supports the following isolation levels:

• READ UNCOMMITTED: The lowest isolation level. Transactions can see uncommitted changes made by other transactions ("dirty reads"). This level provides the highest concurrency but the lowest data integrity.
• READ COMMITTED: Transactions can only see changes made by other transactions that have already been committed. Prevents dirty reads.
• REPEATABLE READ: Transactions can see only the changes made by themselves and changes committed before the transaction started. Prevents dirty reads and non-repeatable reads (a scenario where a transaction reads the same row multiple times and gets different values due to other transactions committing changes in between). This is the default isolation level in MySQL.
• SERIALIZABLE: The highest isolation level. Transactions are completely isolated from each other. It prevents dirty reads, non-repeatable reads, and phantom reads (a scenario where a transaction executes a query that returns a set of rows, and another transaction inserts or deletes rows that match the query's criteria, causing the first transaction to see a different set of rows when it re-executes the query).

You can set the isolation level using the following SQL command:

Where level_name is one of the values listed above (e.g., READ COMMITTED).

Examples

Starting and Committing a Transaction

 START TRANSACTION;

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

COMMIT; 

Starting and Rolling Back a Transaction

 START TRANSACTION;

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

ROLLBACK; 

Using SAVEPOINT

 START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 456;

-- Rollback to savepoint sp1, undoing the second update
ROLLBACK TO sp1;

COMMIT;