Mysql – How to we demo Lost update, Uncommitted data, Unrepeatable data, Phantom on MySQL

MySQL

I'm studying Database Management System and I have a project that I need to demo Lost update, Uncommitted data, Unrepeatable data, Phantom in a DMS I selected. I selected MySQL for my project. But after a couple of times trying to demonstrate I failed. This is a case I tried.

This is transaction T1

START TRANSACTION;
UPDATE roles SET roles.isAdmin = '0';

DO sleep(5);

ROLLBACK;

This is transaction T2

START TRANSACTION;

SELECT * FROM roles WHERE 1;

COMMIT;

Can you help me to figure out how can I demonstrate Lost update, Uncommitted data, Unrepeatable data, Phantom situation?

Best Answer

The different problems are prevented at different isolation levels. So you need to first set an isolation level that allows a problem to happen before you can demonstrate it.

Here's a table that explains which isolation level allows which problem to occur (taken from my post over at stackoverflow which also briefly explains the problems the isolation levels are solving).

                 | Lost updates | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------------------------------------------------------------------
Read Uncommitted | possible     | possible   | possible            | possible
Read Committed   | impossible   | impossible | possible            | possible
Repeatable Read  | impossible   | impossible | impossible          | possible
Serializable     | impossible   | impossible | impossible          | impossible

So, for the first example, the lost update problem, it would work like this:

session 1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

session 1> update t set a = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The session is not yet committed, but in session 2 you can already see the value, when you set the isolation accordingly.

session2 > set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

session2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session2 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

session2 > update t set a=3 where a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

As you can see the Changed value for session 2 is 0, because it read uncommitted values from session 1. When you commit both sessions now, no matter which one first, the values in column a will be 2. The update statement of session 2 got lost.

I think you get the idea, of how to demonstrate the problems. I got some work to do now. When you still have problems, I will update this post when I find the time.

Related Question