MySQL Bugs: #17228: REPEATABLE READ doesn't work correctly in InnoDB tables?

Bug #17228	REPEATABLE READ doesn't work correctly in InnoDB tables?
Submitted:	8 Feb 2006 6:39	Modified:	8 Feb 2006 19:39
Reporter:	Dhruv Matani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.13	OS:	Linux (Suse 10 (x86-64))
Assigned to:		CPU Architecture:	Any

Description:
I use InnoDB tables in repeatable read isolation level, and say I have 2 connections to the DB. Now, when I do an insert from one of the connections(say 2 rows), and then from the other connection, I do an update where one of the rows is satisfied by the where in the UPDATE, it becomes visible in the TRX in which I do the update! I am always doing set autocommit=0; Also, if I do another update in the other TRX after the TRX that inserted the row has committed, the other row becomes visible! Isn't repeatable read supposed to like hide those rows, and then make them visible when I commit; the current trx?

How to repeat:
create table tab1(rn integer, name varchar(200));

TRX-1:
insert into tab1 values(1, 'aaa');
insert into tab1 values(2, 'bbb');
insert into tab1 values(101, 'zzz');

TRX-2:
update tab1 set rn=1002 where name='bbb';
[blocks]

TRX-1:
COMMIT;

TRX-2:
[unblocks]
select * from tab1;
[viola! there is a NEW ROW!!!!]
[however, the remaining 2 rows are still not visible]

Suggested fix:
dunno!!!!

Dhruv,

the docs say that a transaction always sees its own updates. That is the reason why the transaction sees the row that it updated.

This anomaly is one of the problems in REPEATABLE READ. I decided to do it like this, because users would be even more surprised if a transaction would NOT see a row that it has updated.

If you have a pure read-only transaction, then InnoDB's REPEATABLE READ does guarantee repeatable reads (except when the underlying table is DROPped TRUNCATEd or ALTERed).

Regards,

Heikki

Ok, but is there any way of getting the behaviour that I need and also not have to use the Serializable isolation level, because serializable does not give that much concurrency?

Dhruv,

why not do the SELECTs in a separate transaction? If they do not need to match the UPDATEs etc.

Regards,

Heikki

Hello Heikki,
  So, does that make this isolation level identical to READ COMMITTED when it comes to UPDATES? Except that it(the concept) seems highly inconsistent... maybe other's don't see anything wrong with it, but somehow something just doesn't seem right... esp. because if TRX2 has inserted 3 rows, and only 1 matches the WHERE of the UPDATE in TRX1, then only 1 new row shows up. It's like I have to keep this in mind... and it was the first thing I thought would happen intuitively when I first thought of such a situation.

What you say also makes sense, that the user should be able to see ROW(s) he/ she has updated, and that not showing them would probably be even worse.

However, what I'm saying is that that ROW should not be updated in the first place! You are saying that the user should know that the row that he/she has UPDATED. But, I'm saying that why should that row get updated in the first place?

So, assuming u have 2 TRXs:

TRX-1:
SELECT * from tab1;
[empty set]

TRX-2:
insert, insert, insert.
[table (tab1) ==> ]
1, aaa
2, bbb
101, zzz

TRX-1:
SELECT...
[empty set]
UPDATE where field1 = 101 set to 1002;
SELECT ...
[1 row]

....... [now, if u commit both TRXs, then the table will be:]

1, aaa
2, bbb
1002, zzz

but, I'm saying that it should be:

1, aaa
2, bbb
101, zzz

and the UPDATE should not update even a single row.

If we use the as-if rule, we can consider the INSERT to have happened AFTER the UPDATE.

Also, another that struck me as _different_ was that TRXs read the state of table starting from when the BEGIN; statement is issued, and NOT from the FIRST SELECT on that table. Is there any way of turning this off, and making it behave like in the latter case?

Thanks,
-Dhruv.

I think this deserves another look.  I can reproduce some odd behavior, using MySQL 5.1.47 with builtin InnoDB, and Percona Server 5.1.55 with XtraDB based on InnoDB 1.0.15-12.6.

create table tab1(rn integer, name varchar(200)) engine=innodb;

TRX-1:
begin;
show variables like 'tx_isolation';
REPEATABLE-READ

select * from tab1;
<empty set>

TRX-2:
begin;
show variables like 'tx_isolation';
REPEATABLE-READ

select * from tab1;
<empty set>

TRX-1:
insert into tab1 values(1, 'aaa');
insert into tab1 values(2, 'bbb');
insert into tab1 values(101, 'zzz');

TRX-2:
select * from tab1 for update;
[blocks]

TRX-1:
COMMIT;

TRX-2:
[blocking ends, returning rows:]
1, 'aaa'
2, 'bbb'
101, 'zzz'

select * from tab1
<empty set>

select * from tab1 for update
1, 'aaa'
2, 'bbb'
101, 'zzz'

select * from tab1
<empty set>

update tab1 set rn=1002 where name='bbb';
Rows matched:1  Changed: 1  Warnings: 0

select * from tab1;
1002, 'bbb'

This at least shows that REPEATABLE-READ doesn't actually give repeatable reads.  My uncommitted transaction 2 can see some rows committed since I began my transaction, depending on whether I get a lock on the rows.  And there's some very odd behavior related to "for update" queries.