innodb READ-COMMITTED:
table:
CREATE TABLE `test` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
CREATE TABLE `test1` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
insert into test values(1,1);
insert into test1 values(1,1);
case 1:
connection 1:
begin;
update test set key2=111 where key1 in (select key1 from test1);
// test1 lock with LOCK_NONE (no lock); (sql_command:SQLCOM_UPDATE )
then
connection 2:
update test1 set key2=9994;
Query OK, 1 row affected (0.091 sec)
Rows matched: 1 Changed: 1 Warnings: 0
case 2:
connection 1:
begin;
update test a inner join (select key1 from test1) b on a.key1=b.key1 set a.key2=111;
// test1 lock with LOCK_S (sql_command:SQLCOM_UPDATE_MULTI )
then
connection 2:
update test1 set key2=9994;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
in ha_innobase::store_lock, only SQLCOM_UPDATE was processed.
why SQLCOM_UPDATE_MULTI can‘t be LOCK_NONE ?,but SQLCOM_UPDATE with LOCK_NONE .
