MySQL Forums
Forum List  »  InnoDB

why for SQLCOM_UPDATE_MULTI, it lock with lock_s
Posted by: Tom ccc
Date: March 23, 2021 07:25AM

innodb READ-COMMITTED:

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 )

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


why SQLCOM_UPDATE_MULTI can‘t be LOCK_NONE ?

Options: ReplyQuote


Subject
Views
Written By
Posted
why for SQLCOM_UPDATE_MULTI, it lock with lock_s
98
March 23, 2021 07:25AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.