Is MySQL for update row lock / table lock

preface

When MySQL uses for update to query, is row or table locked? See the end of the text for the answer.

The environment tested in this paper is MySQL 8.0.21

verification

When MySQL for update uses the index to retrieve data, it uses row lock instead of table lock. Let's verify this statement through experiments.

Open two MySQL connections and close one of them to automatically commit transactions.

-- Query transaction submission method
select @@autocommit;
-- Turn off auto commit transactions
set autocommit = 0;

Now there is a user table. The data stored in the table is as follows:

The index structure in the table is a clustered index with only the primary key.

1.1 primary key index retrieval data

Connection 1

begin;

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

Connection 2

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

Because connection 1 did not commit a transaction, the row record with id="1" was locked, resulting in data writing failure of connection 2.

update `user` set `name` = "feiyangyang" where id = "1"
> 1205 - Lock wait timeout exceeded; try restarting transaction
> time: 50.403s

Then update the data with id="2"

update `user` set `name` = "feiyangyang" where id = "2";
update `user` set `name` = "feiyangyang" where id = "2"
> Affected rows: 1
> time: 0.002s

If there is data in the above experimental test, what if there is no data?

Connection 1

begin;

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

Connection 2

update `user` set `name` = "feiyangyang" where id = "4";

It is found that there is no lock without data

update `user` set `name` = "feiyangyang" where id = "4"
> Affected rows: 0
> time: 0s

Now it is determined that the record with id="1" is row lock ed. It is concluded that:

Conclusion: when retrieving data according to the primary key index, row lock (with data) and no lock (without data)

1.2 retrieve data according to primary key index and non index fields

Connection 1

begin;

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

Connection 2

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

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

> 1205 - Lock wait timeout exceeded; try restarting transaction
> time: 51.451s

At this time, do not commit the transaction of connection 1. Use connection 2 to query other row records. The execution is successful.

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

Let's look at the situation without data

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "4";
> Affected rows: 0
> time: 0s

Conclusion: retrieve data according to the primary key index and common fields, row lock (with data) and no lock (without data)

1.3 retrieving data from non indexed fields

Connection 1

begin;

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

Connection 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

The first sql statement executed in connection 2 is the same record line as the operation in connection 1. Because connection 1 does not commit a transaction, connection 2 failed to update; However, the second sql statement is not the same record line as connection 1, and the update still fails.

Let's look at the situation without data:

Connection 2

UPDATE `user` SET pwd = "feiyangyang" WHERE `name`= "xiyangyang"
> Affected rows: 0
> time: 0.001s

Conclusion: data can be retrieved according to non indexed fields, table lock (with data) and no lock (without data)

1.4 retrieve data according to general index

Index the name field in 1.3

create index idx_name on `user`(`name`);

Connection 1

begin;

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

Connection 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

The situation without data is the same as above, omitted.

Conclusion: according to the general index, row lock (with data) and no lock (without data)

1.5 retrieving data according to 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);

Connection 1

begin;

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

Connection 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

The situation without data is the same as above, omitted.

Conclusion: when retrieving data according to the unique index, row lock (with data) and no lock (without data)

summary

When retrieving data from an index column, if there is data, the lock level is a record row; When retrieving data based on non index fields, the lock level is the whole table when there is data.

Whether MySQL performs row lock or table lock only depends on whether the index is used. When performing some operations that invalidate the index, table lock is performed naturally.

That's the problem. Why do you add an exclusive lock to index fields, which locks row records, and lock non index fields, which locks the entire table?

An exclusive lock is an index item. In my personal understanding, it is the leaf node of a B + tree. When writing to other leaf nodes of the same B + tree, it does not affect each other. If it is a non indexed field, it has no index tree structure and can only lock the whole table.

Tags: Database MySQL

Posted on Sun, 19 Sep 2021 01:22:15 -0400 by jon2s