Bug #47436 Transaction isolation level SERIALIZABLE becomes REPEATABLE-READ in autocommit
Submitted: 18 Sep 2009 10:27 Modified: 2 Nov 2011 9:21
Reporter: Pavel Pushkarev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.38 OS:Linux
Assigned to: CPU Architecture:Any
Tags: autocommit, repeatable read, SERIALIZABLE, transaction

[18 Sep 2009 10:27] Pavel Pushkarev
The explicitly set transaction isolation level is changed inside a transaction.

Server version: 5.1.38 MySQL Community Server (GPL)

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

mysql> insert a values (1);
Query OK, 1 row affected (0.01 sec)

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

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

mysql> select @@tx_isolation;
| @@tx_isolation |
1 row in set (0.00 sec)

mysql> select * from a;
| a    |
|    1 | 
1 row in set (0.00 sec)

mysql> select @@tx_isolation;
| @@tx_isolation  |
1 row in set (0.00 sec)
[18 Sep 2009 10:49] Valeriy Kravchuk
Looks like a duplicate of bug #20837. Please, check.
[18 Sep 2009 11:20] Pavel Pushkarev
Yes it is, haven't found that one through the search.

If possible, set the #20837 priority to serious: the bug cannot be "non critical" if it changes between isolation levels that easily.
[18 Sep 2009 11:22] Susanne Ebrecht
This is a duplicate of bug #20837
[2 Nov 2011 9:21] Marko Mäkelä
This is not a duplicate of Bug #28029. SERIALIZABLE isolation level appears to be treated as REPEATABLE READ in InnoDB, for auto-commit transactions.

I tried this simple test case:

create table a(a int primary key)engine=innodb;
insert into a values(1),(2),(3);

select * from a where a=2;

The SELECT would not acquire any table or record lock, no matter what I do before it:

- start a new connection
- set global tx_isolation='SERIALIZABLE';
- set tx_isolation='SERIALIZABLE';
- set global transaction isolation level serializable;
- set transaction isolation level serializable;

I set a breakpoint in the following functions:


In row_search_for_mysql(), I ensured that prebuilt->trx->isolation_level == TRX_ISO_SERIALIZABLE. The two breakpoints in lock0lock.c were not hit until I added LOCK IN SHARE MODE or FOR UPDATE to the SELECT, or until I added BEGIN before the SELECT.

So, the bug is that auto-commit SELECT treats SERIALIZABLE as REPEATABLE READ.