Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.8
-
None
-
None
Description
READ-COMMITTED:
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 |
|
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
- duplicates
-
MDEV-25208 why sub query need LOCK_R when SQLCOM_UPDATE_MULTI ?
-
- Closed
-