MySQL Bugs: #78793: Create as select put LOCK

Bug #78793	Create as select put LOCK_S when read record.
Submitted:	10 Oct 2015 8:48	Modified:	16 Oct 2015 13:20
Reporter:	Zhao Jianwei	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi, I has used  'create table t2 as select * from t1 where id=1' statement.

but this statement will block 'update t1 set col1=2 where id=1'.

My Env:

[my.cnf]
tx_isolation='REPEATABLE-READ'
binlog_format=row
autocommit=0

'create as select' statement has put LOCK_S lock on record [t1.id = 1],  but There seems to be no sense, It can replaced by consistent read Completely.

How to repeat:
[my.cnf]
tx_isolation='REPEATABLE-READ'
binlog_format=row
autocommit=0

[session1]
create table t1(id int primary key, col1 int)engine=innodb;
insert into t1 values(1, 1);
commit;

create table t2 select * from t1 where id=1;

Sorry. correct as below:

but this statement will be blocked by 'update t1 set col1=2 where id=1'

Hi!

Thank you for your bug report. After a study of the problem that you expounded here, I can inform you that it is not a bug. The UPDATE statement requires exclusive lock on the row(s) in the table where id = 1. On the other hand, the SELECT part of that CREATE statement requires a shared lock on the same set of rows, or a single row.

Upon committing first statement, the second one will pass, irrespective of the order.

Not a bug.

Hi

Sorry for again， I still have doubt on this case。

I have neither violate repeatable read， nor result in data unconsistent between master and slave if InnoDB didn't put on LOCK_S on row(s) when executing create as select statement。

If it is not a bug.  Could I report it as a feature request?

Actually it is not as simple as it seems.

The issue about CREATE...SELECT is that it is 2 transactions internally: one for the CREATE and another for INSERT...SELECT. Hence, a lock is necessary so that row is available for the INSERT part of the command. But, this statement will be fixed in WL#7743/WL#7141 to be a single transaction. Then, the improvement for the lock might be considered.

Verified as the feature request.