Description:
At
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html
regarding INSERT INTO T SELECT ... FROM S WHERE ...
"
If innodb_locks_unsafe_for_binlog is enabled or the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks).
"
This is not exactly true for both 5.0 and 5.1.
How to repeat:
See source code in ha_innodb.cc, ha_innobase::store_lock
5.0.85:
if (srv_locks_unsafe_for_binlog &&
trx->isolation_level != TRX_ISO_SERIALIZABLE &&
(lock_type == TL_READ || lock_type == TL_READ_NO_INSERT) &&
(thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
thd->lex->sql_command == SQLCOM_UPDATE ||
thd->lex->sql_command == SQLCOM_CREATE_TABLE)) {
5.1.37:
if ((srv_locks_unsafe_for_binlog
|| isolation_level == TRX_ISO_READ_COMMITTED)
&& isolation_level != TRX_ISO_SERIALIZABLE
&& (lock_type == TL_READ || lock_type == TL_READ_NO_INSERT)
&& (sql_command == SQLCOM_INSERT_SELECT
|| sql_command == SQLCOM_UPDATE
|| sql_command == SQLCOM_CREATE_TABLE)) {
Or use script below and see locked structs in INNODB STATUS (try different innodb_locks_unsafe_for_binlog at startup and tx_isolation levels in both 5.0 and 5.1):
create table t1 (id int PRIMARY KEY, a int) engine=innodb;
create table t2 (a int) engine=innodb;
set @n=0;
insert into t1
select @n:=@n+1, floor(rand()*1000)
from mysql.help_topic a, mysql.help_topic b
limit 100000;
set tx_isolation='read-committed';
start transaction;
insert into t2
select a from t1
where a = 995;
show engine innodb status\G
rollback;
Suggested fix:
For 5.0 it should be (change is quoted in /**/):
"
If innodb_locks_unsafe_for_binlog is enabled /*and the transaction isolation level is not SERIALIZABLE*/, InnoDB does the search on S as a consistent read (no locks).
"
For 5.1 :
"
If /*transaction isolation level is READ COMMITTED or */ innodb_locks_unsafe_for_binlog is enabled and the transaction isolation level is not SERIALIZABLE, InnoDB does the search on S as a consistent read (no locks).
"
