MySQL Forums
Forum List  »  InnoDB

Re: why for SQLCOM_UPDATE_MULTI, it lock with lock_s
Posted by: Tom ccc
Date: March 24, 2021 01:40AM

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

```

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: why for SQLCOM_UPDATE_MULTI, it lock with lock_s
105
March 24, 2021 01:40AM


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.