Appearance
Read Committed
In the Read Committed isolation level, a transaction cannot read data that has not yet been committed by another transaction. However, it may encounter issues related to non-repeatable reads.
Non-Repeatable Read
A non-repeatable read occurs when a transaction reads the same data multiple times, and if another transaction modifies that data before the first transaction is completed, the results can be inconsistent across those reads.
Example
Let's prepare the students table with the following data:
sql
mysql> SELECT * FROM students;
+----+-------+
| id | name |
+----+-------+
| 1 | Alice |
+----+-------+
1 row in set (0.00 sec)We then open two MySQL client connections and execute Transaction A and Transaction B in sequence:
| Time | Transaction A | Transaction B |
|---|---|---|
| 1 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
| 2 | BEGIN; | BEGIN; |
| 3 | SELECT * FROM students WHERE id = 1; -- Alice | |
| 4 | UPDATE students SET name = 'Bob' WHERE id = 1; | |
| 5 | COMMIT; | |
| 6 | SELECT * FROM students WHERE id = 1; -- Bob | |
| 7 | COMMIT; |
In step 3, when Transaction B first executes the query, it retrieves the result as "Alice." Subsequently, in step 4, Transaction A updates the record and commits the change. When Transaction B executes the same query again in step 6, it now retrieves "Bob."
This illustrates that in the Read Committed isolation level, a transaction can experience non-repeatable reads, as the data read may change between multiple reads within the same transaction.