these queries causes DIRTY READ
. I thought that REPEATABLE-READ
guarantees that the second select statement in trx2
doesn't return anything. But, the statement returns the row that inserted by trx1
even though the trx2
not committed yet
trx1:
begin;
select * from tableA A where A.id = 1 for update;
select * from tableB B where B.id =2 and B.col_1 = 3;
insert into tableB (id, col_1, created_at) values (2, 3, now());
update tableA set col_2 = 2 where id = 1;
commit;
trx2:
begin;
//waiting until trx1 is committed
select * from tableA A where A.id = 1 for update;
select * from tableB B where B.id =2 and B.col_1 = 3;
commit;
I expected the second selection in trx2
to return nothing.
to the additional... each transactions that be added the another select statement just next begin
doesn't show any DIRTY READ
trx1:
begin;
select * from tableC C where C.id = 3;
select * from tableA A where A.id = 1 for update;
select * from tableB B where B.id =2 and B.col_1 = 3;
insert into tableB (id, col_1, created_at) values (2, 3, now());
update tableA set col_2 = 2 where id = 1;
commit;
trx2:
begin;
select * from tableC C where C.id = 3;
//waiting until trx1 is committed
select * from tableA A where A.id = 1 for update;
select * from tableB B where B.id =2 and B.col_1 = 3;
commit;