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" <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
>
_______________________________________________________________________
Busca Yahoo!
O melhor lugar para encontrar tudo o que você procura na Internet
http://br.busca.yahoo.com/
