Introduction to MySQL Database Transactions

When discussing transactions, you might think of ACID (Atomicity, Consistency, Isolation, Durability). In MySQL, transaction isolation levels correspond to the Consistency and Isolation properties of ACID. ACID refers to the four properties a database transaction should have.

• Atomicity: A transaction is an atomic operation that either fully completes or entirely rolls back to the initial state. MySQL’s transaction isolation levels are not directly related to atomicity but achieve it through transaction commit and rollback.
• Consistency: Transactions transition the database from one consistent state to another. In MySQL, transaction isolation levels can affect data consistency during transaction reads.
• Isolation: Concurrent transactions should be isolated from each other, ensuring one transaction’s operations do not affect others. MySQL’s transaction isolation levels control the degree of this isolation.
• Durability: Once a transaction is committed, its changes should be permanently saved, even in the event of a system failure. Durability is ensured by the database’s logging and recovery mechanisms, not directly by transaction isolation levels.

Today, we’ll focus on Isolation.

MySQL Transaction Isolation Levels

Before discussing isolation levels, it’s essential to understand that higher isolation typically means lower efficiency. Therefore, a balance often needs to be struck. The SQL standard defines the following isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. These levels define how transactions interact with each other when reading data. MySQL supports these isolation levels:

• READ UNCOMMITTED: The lowest isolation level, where transactions can read uncommitted changes from other transactions. This level can lead to dirty reads, non-repeatable reads, and phantom reads.
• READ COMMITTED: Transactions can only read committed data. Changes from other transactions are visible only after they commit. This level prevents dirty reads but may cause non-repeatable reads and phantom reads.
• REPEATABLE READ: Ensures that if a transaction reads the same data multiple times, it sees the same result each time. This level prevents dirty reads and non-repeatable reads but may still allow phantom reads.
• SERIALIZABLE: The highest isolation level, where transactions are executed sequentially, as if they were serialized. This level prevents dirty reads, non-repeatable reads, and phantom reads but significantly reduces concurrency.

Example of MySQL Isolation Levels

Assume a table T with a single column and one row with the value 1. The following example shows the behavior of two transactions executed in sequence.

CREATE TABLE T(c INT) ENGINE=InnoDB;
INSERT INTO T(c) VALUES(1);

In different isolation levels, the results of V1, V2, and V3 will vary.

• READ UNCOMMITTED: V1 is 2 because transaction A can see the uncommitted changes of B. Thus, V2 and V3 are also 2.
• READ COMMITTED: V1 is 1, V2 is 2. Changes from B are visible to A only after B commits, so V3 is 2.
• REPEATABLE READ: V1 and V2 are 1, V3 is 2. A consistent snapshot is maintained for the duration of A, ensuring V1 and V2 are the same.
• SERIALIZABLE: B will be blocked until A completes. From A’s perspective, V1 and V2 are 1, and V3 is 2.

This example demonstrates how different isolation levels affect the visibility of data changes during concurrent transactions.

Viewing MySQL Transaction Isolation Level

For MySQL 5.7 and above:

SHOW VARIABLES LIKE 'transaction_isolation';

or

SELECT @@transaction_isolation;

For MySQL 5.7 and below:

SHOW VARIABLES LIKE 'tx_isolation';

or

SELECT @@tx_isolation;

Setting MySQL Transaction Isolation Level

You can set the transaction isolation level in MySQL using:

• In the connection string:

  url

  jdbc:mysql://localhost/mydb?useUnicode=true&characterEncoding=UTF-8&transactionIsolation=READ_COMMITTED

• In an SQL statement:

  sql

  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

• In an application using a transaction manager, such as Spring’s PlatformTransactionManager.

Understanding and setting the appropriate transaction isolation level is crucial for balancing data consistency and system performance in MySQL.