Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)

[Heikki Tuuri]

Marcos,

----- Original Message -----
From: "Marcos Chaves" <mchvs@stripped>
To: <bugs@stripped>
Sent: Monday, December 30, 2002 4:08 AM
Subject: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)


> Hi.
>
> I did some tests with MySQL 4.0.6 under Windows 2000 and SET
> TRANSACTION ISOLATION LEVEL is not working as expected when I try to
> change the isolation level back to REPEATABLE-READ after changing it
> to another one. Is this a bug?
>
> How-to-repeat
>
>   Microsoft Windows 2000 [Version 5.00.2195]
>
>   C:\mysql\bin>mysql --version
>   mysql  Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
>
> Open two MySQL sessions (A and B). Run the following SQL commands for
> each session:
>
>   A) USE test;
>   A) SET AUTOCOMMIT=0;
>
>   B) USE test;
>   B) SET AUTOCOMMIT=0;
>
>   A) SELECT @@tx_isolation;
>   +-----------------+
>   | @@tx_isolation  |
>   +-----------------+
>   | REPEATABLE-READ |
>   +-----------------+
>   1 row in set (0.00 sec)
>
>   B) SELECT @@tx_isolation;
>   +-----------------+
>   | @@tx_isolation  |
>   +-----------------+
>   | REPEATABLE-READ |
>   +-----------------+
>   1 row in set (0.00 sec)
>
>   A) CREATE TABLE test (id INT) TYPE=INNODB;
>   Query OK, 0 rows affected (0.12 sec)
>
>   A) SELECT * FROM test;
>   Empty set (0.00 sec)
>
>   A) INSERT INTO test (id) VALUES (1);
>   Query OK, 1 row affected (0.00 sec)
>
>   B) SELECT * FROM test;
>   Empty set (0.00 sec)
>
>   A) COMMIT;
>   Query OK, 0 rows affected (0.00 sec)
>
>   A) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   +------+
>   1 row in set (0.00 sec)
>
>   B) SELECT * FROM test;
>   Empty set (0.00 sec)
>
>   B) COMMIT;
>   Query OK, 0 rows affected (0.00 sec)
>
>   B) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   +------+
>   1 row in set (0.00 sec)
>
> ...everything working fine up to now...
>
>   A) SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
>   Query OK, 0 rows affected (0.00 sec)
>
>   A) SELECT @@tx_isolation;
>   +----------------+
>   | @@tx_isolation |
>   +----------------+
>   | READ-COMMITTED |
>   +----------------+
>   1 row in set (0.00 sec)
>
>   A) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   +------+
>   1 row in set (0.00 sec)
>
>   B) INSERT INTO test (id) VALUES (2);
>   Query OK, 1 row affected (0.01 sec)
>
>   B) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   |    2 |
>   +------+
>   2 rows in set (0.00 sec)
>
>   B) COMMIT;
>   Query OK, 0 rows affected (0.00 sec)
>
>   A) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   |    2 |
>   +------+
>   2 rows in set (0.01 sec)
>
> ...working as expected...
>
>   A) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
>   Query OK, 0 rows affected (0.00 sec)
>
>   A) SELECT @@tx_isolation;
>   +-----------------+
>   | @@tx_isolation  |
>   +-----------------+
>   | REPEATABLE-READ |
>   +-----------------+
>   1 row in set (0.00 sec)
>
>   B) INSERT INTO test (id) VALUES (3);
>   Query OK, 1 row affected (0.01 sec)
>
>   B) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   |    2 |
>   |    3 |
>   +------+
>   3 rows in set (0.00 sec)
>
>   B) COMMIT;
>   Query OK, 0 rows affected (0.00 sec)
>
>   A) SELECT * FROM test;
>   +------+
>   | id   |
>   +------+
>   |    1 |
>   |    2 |
>   |    3 |
>   +------+
>   3 rows in set (0.00 sec)
>
> The last query retured the line where id = 3, that shouldn't be
> visible at this point in session A - it worked as if isolation level
> was not changed back from read commited to repeatable read. Let's
> check session B isolation level:


the algorithm is this: when the isolation level is READ COMMITTED, InnoDB
closes at the end of each SELECT the snapshot which it read in the
consistent read. Thus, the snapshot which A read above and saw (1 2) was
closed. When A does the same SELECT again, a new snapshot is opened and (1 2
3) are visible in that snapshot.

The read is 'repeatable' only for SELECTs executed AFTER you have set the
isolation level of A back to REPEATABLE READ. Only after that InnoDB keeps
the same snapshot open for all consistent reads in the transaction of A.
Note also that the isolation level of B does not affect at all what A sees
in a consistent read.

Thus, this is a feature, not a bug. It is not good to change this behavior
because Oracle users (Oracle uses READ COMMITTED) may not be used to
committing their plain SELECT transactions. If we would keep the snapshot
open to wait for a possible switch to REPEATABLE READ, then InnoDB would not
be able to purge the history if someone familiar with Oracle forgets his
plain SELECT transaction dangling.

...

Best regards,

Heikki
http://www.innodb.com