Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand MySQL row lock, table lock, gap lock". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preparatory work

Create table tb_innodb_lock

Drop table if exists test_innodb_lock; CREATE TABLE test_innodb_lock (an INT (11), b VARCHAR (20)) ENGINE INNODB DEFAULT charset = utf8; insert into test_innodb_lock values (1); insert into test_innodb_lock values (2); insert into test_innodb_lock values (3); insert into test_innodb_lock values (4); insert into test_innodb_lock values (5)

Create an index

Create index idx_lock_a on test_innodb_lock (a); create index idx_lock_b on test_innodb_lock (b)

Demonstration of various locks in MySQL

First change the autocommit transaction to manual commit: set autocommit=0

We start two session windows An and B to simulate that one grabs the lock and the other is blocked.

Row lock (write & read)

A window execution

Update test_innodb_lock set baked goods A1 'where a1th select * from test_innodb_lock

We can see the updated result in A window.

B window execution

SELECT * from test_innodb_lock

We can see that window B cannot see the updated result, but still sees the old data, because the row of a = 1 is locked by the SQL statement executed by window A, and the commit commit operation is not performed. So window B sees the same old data. This is the "read submitted" in the MySQL isolation level.

Window A performs commit operation

COMMIT

Window B query

SELECT * from test_innodb_lock

At this time, we found that window B had read the latest data.

Line lock (write & write)

Window A performs an update of a = 1 record

Update test_innodb_lock set breadwinner a2' where astat1

There is no commit submission at this time, and the lock is held by window A.

Window B also performs updating the record of a = 1.

Update test_innodb_lock set breadth A3 'where axi1

As you can see, window B has been blocked because window A has not yet executed the commit and still holds the lock. Window B cannot grab the lock for the line a = 1, so it keeps blocking the wait.

Window A performs commit operation

COMMIT

Changes in window B

You can see that window B has been executed successfully at this time.

Watch lock

When the index fails, the row lock will be upgraded to a table lock. One of the methods of index failure is to manually change the index automatically by or. The a field itself is integer, and when we put quotation marks on it, it becomes String, and the index becomes invalid.

Window A updates the record of a = 1

Update test_innodb_lock set baked goods 4 'where a4' or 2

Window B updates the record of a = 2

Update test_innodb_lock set bachelorette b1' where astat3

At this time, it is found that although the updated rows of window An and B are not the same, window B is still blocked because the index of window An is invalid, which causes the row lock to be upgraded to a table lock, locking the whole table and blocking the index window B.

Window A performs commit operation

COMMIT

Changes in window B

You can see that window B has been executed successfully at this time.

Gap lock

What is a gap lock?

When we use range conditions to query data, InnoDB locks the data in that range. For example, if there are four pieces of data with an id of 1, 3, 5 and 7, we look for data in the range of 1-7. Then 1-7 will be locked. 2, 4, 6 are also in the range of 1-7, but these data records do not exist, and these 2, 4, 6 are called gaps.

The harm of clearance lock

When looking for a range, all the data in the whole range will be locked, and even some data that does not exist in this range will be innocently locked. For example, if I want to insert 2 in 1, 3, 5 and 7, 1-7 will be locked at this time. I can't insert 2 at all. Can have a significant impact on performance in some scenarios

Gap lock demonstration

Let's change the value of field a to 1, 3, 5, 7, 9.

Window A updates data in the range of a = 1x7

Update test_innodb_lock set bounded b5' where a > 1 and a

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.