Bug #71164 an autocommit SELECT was not treated as autocommit non-lock trx

• Submitted: 17 Dec 2013 16:38
• Modified: 26 Dec 2013 20:27
• Reporter: zhai weixiang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.15
• OS: Any
• CPU Architecture: Any

[17 Dec 2013 16:38] zhai weixiang

Description:

autocommit = ON;

create table t1 (a int);

1.
executing :
insert into t1 values (1);

in function trx_commit_in_memory , trx->will_lock was set to 0;

quoted code :
1288         trx->will_lock = 0;
1289         trx->read_only = FALSE;
1290         trx->auto_commit = FALSE;

then close_thread_tables->mysql_unlock_tables->unlock_external->handler::ha_external_lock->ha_innobase::external_lock was called and set trx->will_lock to 1;

quoted code from ha_innobase::external_lock

12241         if (!trx_is_started(trx)
12242             && (prebuilt->select_lock_type != LOCK_NONE
12243                 || prebuilt->stored_select_lock_type != LOCK_NONE)) {
12244
12245                 ++trx->will_lock;
12246         } 

2
execute:
select * from t1;

since the trx object by the following auto commit SELECT, it’s not treated as a auto-commit non-lock trx (trx_is_autocommit_non_locking(trx)) because trx->will_lock = 1

Breakpoint 18, trx_start_low (trx=0x2b4e4c01be38) at /u01/project/branch/mysql-5.6.15/storage/innobase/trx/trx0trx.cc:824
824                                || thd_trx_is_auto_commit(trx->mysql_thd);
(gdb) p trx->will_lock
$14 = 1 

I don’t know if this is the expect behavior because I am not an expert of MySQL, So I filed this bug report.

How to repeat:
read the code 

Suggested fix:
N/A

[18 Dec 2013 2:06] zhai weixiang

correct Category

[26 Dec 2013 20:27] Sveta Smirnova

Thank you for the report.

Verified as described.

Workaround: run SELECT two times, second will be in READ ONLY mode.

[4 Sep 2014 2:56] Jeremy Cole

Wouldn't this be the correct fix?

--- storage/innobase/handler/ha_innodb.cc
+++ storage/innobase/handler/ha_innodb.cc
@@ -12758,7 +12758,7 @@ ha_innobase::external_lock(
                }
        }
 
-       if (!trx_is_started(trx)
+       if (lock_type != F_UNLCK && !trx_is_started(trx)
            && (prebuilt->select_lock_type != LOCK_NONE
                || prebuilt->stored_select_lock_type != LOCK_NONE)) {

[5 Sep 2014 2:44] liu hickey

Good catch! Definitely fixed.