0


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?

1
  • As I understand, those phenomena is caused by dirty reads. IIRC - InnoDB stores don't support dirty reads. May 16 '18 at 10:31
1

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.

1
  • Thanks for your advice. I knew how to demonstrate this. May 18 '18 at 1:05
0

May I have another question that I demo isolation level REPEATABLE READ

Transaction 1

start transaction;
select @parentName := S.parentalName
from `students` S
where S.studentID = '2';
do sleep(5);

set @parentName = CONCAT( @parentName, ' New ');

update `students`
set `parentalName` = @parentName
where `studentID` = '2';

commit;

Transaction 2

start transaction;
select @parentName := S.parentalName
from `students` S
where S.studentID = '2';
do sleep(2);

set @parentName = CONCAT( @parentName, ' New_1 ');

update `students`
set `parentalName` = @parentName
where `studentID` = '2';

commit;

As I expect the result is

parentName New New_1

But my result is parentName New_1. In this case Transaction 2 do not wait for Transaction 1 in select query. What should I explain in this case?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.