MySQL not showing updated data even after commit

Question

I am using two windows as I want to implement concept of transaction.

Window1: begin;

Window1: update employee set salary = 45000 where ssn = '123456789';

Window2: begin;

Window2: select * from employee where ssn = '123456789';

Here, this command shows me previous data which is correct.

Window1: commit;

Window2: select * from employee where ssn = '123456789';

Here, I should get the updated salary of 45000. But my window 2 is showing previous data only. Where am I doing the mistake?

Answer 1

You expectations are incorrect, that's all. What transactions see from each other's work is determined by the so called transaction isolation levels. By default, mysql uses repeatable read isolation level, which means:

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

You can change the isolation level to read committed to enable the behaviour you expect:

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.