MySQL Bugs: #20837: Apparent change of isolation level during transaction

Bug #20837	Apparent change of isolation level during transaction
Submitted:	4 Jul 2006 1:28	Modified:	26 Oct 18:11
Reporter:	Peter Gulutzan
Status:	In progress
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Magne Mæhre	Target Version:	5.1+
Tags:	Contribution
Triage:	Triaged: D2 (Serious)

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.

Is there any progress in this bug. It is critical bug for many applications. Our
application cannot be migrate to 5.1 from 5.0 because of this bug.

Bug #47436 is set as duplicate of this bug here

Wow, the bug's old.

It is still there in 5.1.38 and it switches to the default isolation level from all other
levels (in my case it was serializable).

If possible, please set the severity to serous at least: the bug is serous as it makes
all isolation level using environments impossible.

We just ran into this bug in class with 5.1.38 on Windows.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88132

3197 Magne Mahre	2009-10-26
      Bug #20837 Apparent change of isolation level during transaction
      
      SET TRANSACTION ISOLATION LEVEL is used to temporarily set
      the trans.iso.level for the next transaction.  After the
      transaction, the iso.level is (re-)set to value of the session
      variable 'tx_isolation'.
      
      This bug is caused by resetting the transaction isolation
      even on a statement commit.   The fix is to check if this
      is a full transaction commit/rollback, and only reset the
      value if so.