[#MDEV-25208] why sub query need LOCK_R when SQLCOM_UPDATE

CREATE TABLE `test_sharding` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
CREATE TABLE `test_sharding1` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;

update test_sharding set key2=111 where key1 in (select key1 from test_sharding1);
test_sharding1 lock with LOCK_NONE (no lock); (sql_command:SQLCOM_UPDATE )

but for update test_sharding a inner join (select key1 from test_sharding1) b on a.key1=b.key1 set a.key2=111;
test_sharding1 lock with LOCK_S (sql_command:SQLCOM_UPDATE_MULTI )

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;

[MDEV-25208] why sub query need LOCK_R when SQLCOM_UPDATE_MULTI ? Created: 2021-03-20 Updated: 2021-03-23 Resolved: 2021-03-23
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Fix Version/s:	N/A

[MDEV-25208] why sub query need LOCK_R when SQLCOM_UPDATE_MULTI ? Created: 2021-03-20 Updated: 2021-03-23 Resolved: 2021-03-23