Hi Heikki,
you're right. This bug is NOT present in 4.0.8.
Thanks again for the attention,
Marcos
>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:
_________________________________________________________________
MSN Messenger: converse com os seus amigos online.
http://messenger.msn.com.br
