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);

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

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

store_lock

		if (sql_command == SQLCOM_CHECKSUM

		    || sql_command == SQLCOM_CREATE_SEQUENCE

		    || (sql_command == SQLCOM_ANALYZE && lock_type == TL_READ)

		    || ((srv_locks_unsafe_for_binlog

			|| trx->isolation_level <= TRX_ISO_READ_COMMITTED)

			&& trx->isolation_level != TRX_ISO_SERIALIZABLE

			&& (lock_type == TL_READ

			    || lock_type == TL_READ_NO_INSERT)

			&& (sql_command == SQLCOM_INSERT_SELECT

			    || sql_command == SQLCOM_REPLACE_SELECT

			    || sql_command == SQLCOM_UPDATE

			    || sql_command == SQLCOM_CREATE_SEQUENCE

			    || sql_command == SQLCOM_CREATE_TABLE))) {

			/* If we either have innobase_locks_unsafe_for_binlog

			option set or this session is using READ COMMITTED

			isolation level and isolation level of the transaction

			is not set to serializable and MySQL is doing

			INSERT INTO...SELECT or REPLACE INTO...SELECT

			or UPDATE ... = (SELECT ...) or CREATE  ...

			SELECT... without FOR UPDATE or IN SHARE

			MODE in select, then we use consistent read

			for select. */

			m_prebuilt->select_lock_type = LOCK_NONE;

			m_prebuilt->stored_select_lock_type = LOCK_NONE;

		} else {

			m_prebuilt->select_lock_type = LOCK_S;

			m_prebuilt->stored_select_lock_type = LOCK_S;

		}