preface

MySQL > select * from ‘for UPDATE’ where row or table is locked The answer is at the bottom.

The test environment in this article is MySQL 8.0.21

validation

MySQL for update when using the index to retrieve data, using the row lock, without using the index to retrieve data, is the table lock, here we come first experiments verify that.

Open two MySQL connections and close one of them for automatic commit transactions.

-- Query transaction commit mode
select @@autocommit;
-- Turn off auto-commit transactions
set autocommit = 0;
Copy the code

We now have a user table that stores the following data:

The index structure of the table is such that only the primary key is the clustered index.

1.1 Primary key indexes retrieve data

Connect 1

begin;

select * from user where id = "1" for update;
Copy the code

Connect the 2

update `user` set `name` = "feiyangyang" where id = "1";
Copy the code

Because connection 1 did not commit a transaction, the row with id=”1″ was locked, causing connection 2 to fail to write data.

update `user` set `name` = "feiyangyang" where id = "1"
> 1205 - Lock waittimeout exceeded; Time: 50.403s after the try Restarting transaction is restartedCopy the code

Update (id=”2″)

update `user` set `name` = "feiyangyang" where id = "2";
Copy the code
update `user` set `name` = "feiyangyang" where id = "2"> Affected Rows: 1 > Time: 0.002sCopy the code

What about the case where there is data, and what about the case where there is no data?

Connect 1

begin;

select * from user where id = "4" for update;
Copy the code

Connect the 2

update `user` set `name` = "feiyangyang" where id ="4".Copy the code

No lock found with no data

update `user` set `name` = "feiyangyang" where id = "4"> Affected Rows: 0 > time: 0sCopy the code

Select * from row where id=”1″;

Conclusion: When retrieving data from a primary key index, row lock (with data), no lock (with data)

1.2 Retrieve data based on primary key indexes and non-indexed fields

Connect 1

begin;

select * from `user` where id = "1" and `name` = "test" for update;
Copy the code

Connect the 2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";
Copy the code

Connection 2 update failed because connection 1 did not commit the transaction and the data row was locked

> 1205 - Lock waittimeout exceeded; Restarting > Time: 51.451sCopy the code

At this point, do not commit the transaction on connection 1, use connection 2 to query other row records, and the execution is successful.

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";
Copy the code

Now let’s look at the case where there’s no data

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id ="4".Copy the code
> Affected Rows: 0 > time: 0sCopy the code

Conclusion: Retrieve data based on primary key indexes and plain fields, row lock, no lock

1.3 Retrieving data by non-indexed fields

Connect 1

begin;

select * from `user` where `name` = "test" for update;
Copy the code

Connect the 2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
>Time:50.385s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> 1205 - Lock wait timeout exceeded; try restarting transaction
>Time:50.385s
Copy the code

The first SQL statement executed in connection 2 is the same row as the operation in connection 1. Connection 2 failed to update because connection 1 did not commit the transaction. But the second SQL statement is not the same row as connection 1, and the update still fails.

Let’s look at the case with no data:

Connect the 2

UPDATE `user` SET pwd = "feiyangyang" WHERE `name`= "xiyangyang"
Copy the code
> Affected Rows: 0 > Time: 0.001sCopy the code

Table lock (with data) no lock (without data)

1.4 Retrieving data according to a normal index

Add an index to the name field in 1.3

create index idx_name on `user`(`name`);
Copy the code

Connect 1

begin;

select * from `user` where `name` = "test" for update;
Copy the code

Connect the 2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
>Time:50.385s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> Affected rows: 1
>Time:0.013s
Copy the code

The case of no data is the same as above, omitted.

Conclusion: According to normal index search, row lock (data), no lock (no data)

1.5 Retrieve data based on unique index

Change the index of the name field to a unique index

drop index idx_name on `user`;
create unique index idx_name on `user`(name);
Copy the code

Connect 1

begin;

select * from `user` where `name` = "test" for update;
Copy the code

Connect the 2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
>Time:50.374s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> Affected rows: 1
>Time:0.05s
Copy the code

The case of no data is the same as above, omitted.

Conclusion: When retrieving data based on a unique index, row lock (with data), no lock (without data)

conclusion

When retrieving data against indexed columns, the lock level is the record row in the case of data; When data is retrieved by non-index fields, the lock level is the entire table in the presence of data.

MySQL does row lock or table lock only depending on whether an index is used or not. When you do something that invalidates an index, you do a table lock.

Why lock index fields exclusively for rows and non-index fields for the entire table?

The exclusive lock locks the index entries. In my personal understanding, it is the leaf nodes of the B+ tree. When writing operations to other leaf nodes of the same B+ tree, they do not affect each other. If the field is not indexed, there is no index tree structure, can only lock the entire table.