Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 30.12.2002 03:08:20 von Marcos Chaves
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
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13377@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 30.12.2002 15:28:24 von Heikki Tuuri
Marcos,
----- Original Message -----
From: "Marcos Chaves"
To:
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
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13380@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 31.12.2002 03:08:06 von mchvs
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
> Marcos,
>
> ----- Original Message -----
> From: "Marcos Chaves"
> To:
> 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
>
____________________________________________________________ ___________
Busca Yahoo!
O melhor lugar para encontrar tudo o que você procura na Internet
http://br.busca.yahoo.com/
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13384@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 31.12.2002 10:55:12 von Heikki Tuuri
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:
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13385@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 12.01.2003 17:22:03 von Marcos Chaves
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
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13463@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 12.01.2003 23:25:22 von Heikki Tuuri
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:
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13468@lists.mysql.com
To unsubscribe, e-mail
Re: Could not set isolation level back to REPEATABLE-READ (4.0.6 win)
am 14.01.2003 02:35:44 von Marcos Chaves
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
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread13477@lists.mysql.com
To unsubscribe, e-mail