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
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.1.38
• OS: Linux
• CPU Architecture: Any
• Tags: autocommit, repeatable read, SERIALIZABLE, transaction

[18 Sep 2009 10:27] Pavel Pushkarev

Description:
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 |
+----------------+
| SERIALIZABLE   | 
+----------------+
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  |
+-----------------+
| REPEATABLE-READ | 
+-----------------+
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);
commit;

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:

row_search_for_mysql
lock_rec_create
lock_table_create

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.