Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
None
Description
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 )
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;
|
}
|
|
why SQLCOM_UPDATE_MULTI can‘t be LOCK_NONE ?
Attachments
Issue Links
- is duplicated by
-
MDEV-25210 why sub query need LOCK_R when SQLCOM_UPDATE_MULTI ?
-
- Open
-