Marcos,
I tested 4.0.8 on Linux and it seemed to work ok.
Are you aware that you must use
SET SESSION TRANSACTION ISOLATION LEVEL ...;
to change the isolation level of the session. Without the SESSION keyword it
only affects the current transaction.
Can you present an exact sequence of SQL statements which does not work
right in 4.0.8?
Regards,
Heikki
heikki@hundin:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.8-gamma-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.02 sec)
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
mysql> set transaction isolation level read_committed;
ERROR 1064: You have an error in your SQL syntax. Check the manual that
corresp
onds to your MySQL server version for the right syntax to use near
'read_committ
ed' at line 1
mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
################## HERE IS A BUGLET: the setting to read committed only
starts AFTER the next SQL statement in the same transaction:
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 5 |
| 7 |
+------+
7 rows in set (0.00 sec)
mysql> select * from marcos;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 5 |
| 7 |
+------+
7 rows in set (0.00 sec)
mysql>
In another window:
heikki@hundin:~/mysql-4.0/client> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.8-gamma-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.02 sec)
mysql> create table marcos (a int) type = innodb;
Query OK, 0 rows affected (0.11 sec)
mysql> insert into marcos values (1);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into marcos values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into marcos values (3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into marcos values (4);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into marcos values (5);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into marcos values (5);
Query OK, 1 row affected (0.00 sec)
mysql> insert into marcos values (7);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into marcos values (8);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
......................
Subject: Re: Could not set isolation level back to REPEATABLE-READ (4.0.6
win)
From: Marcos Chaves
Date: Sun, 12 Jan 2003 16:22:03 +0000
----------------------------------------------------------------------------
----
Hello,
just to remind you guys that this bug is still present in 4.0.8 (at least
for Windows).
I hope it helps,
Marcos
>Marcos,
>
>you are right. I should have tested this. There is a bug in 4.0 that InnoDB
>only adjusts the trx isolation level if you have set it to something else
>than REPEATABLE READ. That is why you are not able to switch it back to
>REPEATABLE READ once you have changed it!
>
>I have now fixed the bug in upcoming 4.0.8. Lenz will probably build 4.0.8
>soon after he returns from his vacation around Jan 20th.
>
>Thank you,
>
>Heikki
>Innobase Oy
>
>..................
>Subject: Re: Could not set isolation level back to REPEATABLE-READ (4.0.6
>win)
>From: Marcos Chaves
>Date: Mon, 30 Dec 2002 23:08:06 -0300 (ART)
>
>
>---------------------------------------------------------------------------
-
>----
>
>Hello Heikki,
>
> > 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.
>
>I still didn't get it, because I understood that what
>you explained is
>different from what is happening in my example.
>
>The SELECTs executed AFTER changing the isolation
>level of A back to REPEATABLE
>READ always return the latest 'commited' data
>available, working as if the
>isolation level was still READ COMMITED.
>
>I also tried to experiment with the SERIALIZABLE
>isolation level, and when I
>try to change it back to REPEATABLE READ, I still get
>the tables locked (I can,
>however, change it to other isolation levels). That's
>why I tought it might be
>a bug that does not allow me to change the isolation
>level back to REPEATABLE
>READ. Maybe I'm not understanding the transitions
>between different isolation
>levels, but the only way I could get back to the
>original isolation level is by
>opening a new connection.
>
>Thanks again,
>
>Marcos
>
>How-to-Repeat:
