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:
A) INSERT INTO test (id) VALUES (4);
Query OK, 1 row affected (0.00 sec)
A) commit;
Query OK, 0 rows affected (0.00 sec)
B) SELECT * FROM test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
B) COMMIT;
Query OK, 0 rows affected (0.01 sec)
B) SELECT * FROM test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
B) DROP TABLE test;
Query OK, 0 rows affected (0.05 sec)
Session isolation level in B worked as expected (repeatable read).
_________________________________________________________________
MSN Hotmail, o maior webmail do Brasil. http://www.hotmail.com
