Description:
When I start a transaction as READ UNCOMMITTED, and I update an
InnoDB table, I see an apparent change to REPEATABLE READ. This
is understandable (READ UNCOMMITTED transaction should be READ
ONLY), but the isolation level should not automatically change
after the transaction has started.

In fact InnoDB won't read uncommitted data even if the isolation
level was initially set to READ UNCOMMITTED, so this is not a
serious matter. But people who select @@tx_isolation will get
the impression that isolation level really changes, wrongly.

How to repeat:
mysql> create table t1 (s1 int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| s1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

mysql> insert into t1 values (-1);
Query OK, 1 row affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

Thank you for a problem report. Verified just as described with 5.1.12-BK
(ChangeSet@1.2237, 2006-07-03 23:17:53-04:00).

patch: http://lists.mysql.com/internals/35146

This happens with SELECT as well:

--source include/have_innodb.inc

drop table if exists test1;
CREATE TABLE test1
(
  id smallint not null,
  primary key (id)
) engine=innodb;

insert into test1 values (1),(2),(3);

set transaction isolation level read uncommitted;
SELECT @@tx_isolation;
begin;
SELECT @@tx_isolation;
select * from test1;
SELECT @@tx_isolation;

Result:

drop table if exists test1;
Warnings:
Note    1051    Unknown table 'test1'
CREATE TABLE test1
(
id smallint not null,
primary key (id)
) engine=innodb;
insert into test1 values (1),(2),(3);
set transaction isolation level read uncommitted;
SELECT @@tx_isolation;
@@tx_isolation
READ-UNCOMMITTED
begin;
SELECT @@tx_isolation;
@@tx_isolation
READ-UNCOMMITTED
select * from test1;
id
1
2
3
SELECT @@tx_isolation;
@@tx_isolation
REPEATABLE-READ

Same for read committed:

drop table if exists test1;
Warnings:
Note    1051    Unknown table 'test1'
CREATE TABLE test1
(
id smallint not null,
primary key (id)
) engine=innodb;
insert into test1 values (1),(2),(3);
set transaction isolation level read committed;
SELECT @@tx_isolation;
@@tx_isolation
READ-COMMITTED
begin;
SELECT @@tx_isolation;
@@tx_isolation
READ-COMMITTED
select * from test1;
id
1
2
3
SELECT @@tx_isolation;
@@tx_isolation
REPEATABLE-READ

It does not just switch to REPEATABLE-READ, it switches to the global transaction
isolation level, whatever it is, in any direction. The example below shows a switch from
REPEATABLE-READ to READ-UNCOMMITTED.

Moreover, the switch indeed changes the behavior -- see the comment at the end of the
example.

# connect for the first time

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

drop table if exists t;
create table t ( i int ) engine = innodb;

set global transaction isolation level read uncommitted;

# Need to reconnect to get the new global level work

exit;
# Connect again

select @@tx_isolation;
# +------------------+
# | @@tx_isolation   |
# +------------------+
# | READ-UNCOMMITTED |
# +------------------+

set transaction isolation level repeatable read;

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

begin;

select @@tx_isolation;
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

select * from t;
Empty set (0.00 sec)

select @@tx_isolation;
# +------------------+
# | @@tx_isolation   |
# +------------------+
# | READ-UNCOMMITTED |
# +------------------+

# It indeed behaves as uncommitted read. If you insert something into t 
# from another connection with autocommit = 0, you'll see the result right away

select * from t;
# +------+
# | i    |
# +------+
# |    1 |
# +------+

# etc.

Even more precisely, it switches to SESSION isolation level -- e.g. if global transaction
isolation level is READ-COMMITTED, session level is READ-UNCOMMITTED, and transaction
level is set REPEATABLE-READ, in the examples above the level will switch to
READ-UNCOMMITTED.

This bug still exists in 5.1.31 for solaris sparc 32 and 64 bit.  The severity of this bug
should be increased.  A transaction relying on serializable semantics could cause data
corruption if the isolation level is unintentionally decreased.

Assigning this bug to Vasil.

Vasil, please find out what is wrong and how to fix it.

--Heikki

Is there any progress for this bug? It is pretty ugly bug.

This problem is also in 5.1 version.