How can we demo Lost update, Uncommitted data, Unrepeatable data, Phantom on 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?
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&gt; start transaction;
Query OK, 0 rows affected (0.00 sec)

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

session 1&gt; 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 &gt; set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

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

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

session2 &gt; 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.