MySQL Bugs: #11981: innodb repeatable read broke with alter table

Bug #11981	innodb repeatable read broke with alter table
Submitted:	16 Jul 2005 12:03	Modified:	21 Jul 2005 4:47
Reporter:	KimSeong Loh (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	all	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
Repeatable read isolation in InnoDB is broken when alter table is executed from a different connection.

How to repeat:
create table aa (i int) engine=innodb;
insert into aa values (1),(2),(3);

Connection 1.
begin;
select * from aa;
-> returns 3 rows: value 1, 2 and 3

Connection 2.
alter table aa add j int;

back to connection 1.
select * from aa;
-> returns 0 row

I was not able to repeat the bug on 4.1.13. Probably the bug was fixed.

Reproduces on version 4.1.12 and 5.0.9
Will retest again when newer version is released.

Verified on 5.0.10-beta-debug.

Note that if you use AUTOCOMMIT = 1 and do not use begin there is no problems:

mysql 1> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql 1> select * from aa;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql 2>set autocommit = 1;

mysql 2> alter table aa add j int;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 1> select * from aa;
+------+------+
| i    | j    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 | NULL |
+------+------+
3 rows in set (0.00 sec)

Bug could be caused the fact that if AUTOCOMMIT = 0 is used transaction has opened a consistent read view in the first select and tries to use this same read view again in the second select, but notes that earlier version of the row in the cluster index is needed i.e (gdb output):

3765                            view = trx->read_view;
(gdb)
3768                    if (trx->isolation_level == TRX_ISO_READ_UNCOMMITTED) {
(gdb)
3773                    } else if (index == clust_index) {
(gdb)
3780                            if (UNIV_LIKELY(srv_force_recovery < 5)
(gdb)
3784                                    err = row_sel_build_prev_vers_for_mysql(
(gdb) list
3779
3780                            if (UNIV_LIKELY(srv_force_recovery < 5)
3781                                && !lock_clust_rec_cons_read_sees(rec, index ,
3782                                                    offsets, view)) {
3783
3784                                    err = row_sel_build_prev_vers_for_mysql(
3785                                                    view, clust_index,
3786                                                    prebuilt, rec,
3787                                                    &offsets, &heap,
3788                                                    &old_vers, &mtr);

but no old version of the row can't be found

3795                                    if (old_vers == NULL) {
(gdb)
3799                                            goto next_rec;
(gdb) p old_vers
$3 = (rec_t *) 0x0

Hi!

The reason is that in MySQL, ALTER TABLE is performed as transactions that create a new table and insert the rows from the old table to the new table.

Now, when you reissue the consistent read, it will not see any rows in the new table, because they were inserted in a transaction that is not visible in the snapshot that the consistent read reads!

This strange behavior should be documented in the manual. It should be noted in the manual that a consistent read does not work over a DROP TABLE (because MySQL cannot use a table that has been dropped, and also because InnoDB destroys the table), and does not work over ALTER TABLE, for the reason described above.

I do not think it is worth the effort to fix this behavior.

I am assigning Jan Lindström to document this.

Regards,

Heikki

Jan has now documented this at
http://dev.mysql.com/doc/mysql/en/innodb-consistent-read.html