preface

Previous articles have covered what indexes are, how to use Explain to analyze index usage, how to optimize indexes, and show Profiles to analyze the resource consumption of SQL statement execution. Today we are going to talk about the various locks in MySQL, where we use InnoDB as the storage engine

The preparatory work

Create a table tb_innodb_lock

drop table if exists test_innodb_lock;
CREATE TABLE test_innodb_lock (
    a INT (11),
    b VARCHAR (20)
) ENGINE INNODB DEFAULT charset = utf8;
insert into test_innodb_lock values (1.'a'); insert into test_innodb_lock values (2.'b'); insert into test_innodb_lock values (3.'c'); insert into test_innodb_lock values (4.'d'); insert into test_innodb_lock values (5.'e'); Copy the code

Create indexes

create index idx_lock_a on test_innodb_lock(a);
create index idx_lock_b on test_innodb_lock(b);
Copy the code

MySQL lock demo

  • First change the automatic commit transaction to manual commit:set autocommit=0;
  • We start two session Windows, A and B, simulating that one is locked and the other is blocked.

Line lock (write & read)

  • A window execution
update test_innodb_lock set b='a1' where a=1;
Copy the code
SELECT * from test_innodb_lock;
Copy the code

We can see the A window and we can see the updated results

  • B Window execution
SELECT * from test_innodb_lock;
Copy the code

B = 1; B = 1; B = 1; B = 1; B = 1; B = 1; So window B is still seeing the old data. This is “read committed” in the MySQL isolation level.

  • In window A, perform commit
COMMIT;
Copy the code
  • Window B Query
SELECT * from test_innodb_lock;
Copy the code

At this point we find that window B has already read the latest data

Line lock (write & write)

  • Window A performs an update of A = 1 record
update test_innodb_lock set b='a2' where a=1;
Copy the code

There is no COMMIT and the lock is held by window A.

  • Window B also performs an update of a = 1 record
update test_innodb_lock set b='a3' where a=1;
Copy the code

As you can see, window B is always blocking because window A has not yet committed and still holds the lock. Window B cannot grab the lock on row A = 1, so it is blocking and waiting.

  • In window A, perform commit
COMMIT;
Copy the code
  • The change in window B

You can see that window B has successfully executed at this point

Table locks

When an index is invalid, a row lock can be upgraded to a table lock. One of the methods to invalidate an index is to convert the index automatically or manually. The a field itself is an INTEGER, so if we put it in quotes, it becomes a String, and then the index becomes invalid.

  • Window A updates the record for A = 1
update test_innodb_lock set b='a4' where a=1 or a=2;
Copy the code
  • Window B updates the record a = 2
update test_innodb_lock set b='b1' where a=3;
Copy the code

At this time, it is found that although the rows updated by window A and window B are different, window B is still blocked. Because the index of window A is invalid, the row lock is upgraded to A table lock, locking the entire table, and index window B is blocked.

  • In window A, perform commit
COMMIT;
Copy the code
  • The change in window B

You can see that window B has successfully executed at this point

Clearance lock

  • What is a clearance lock

InnoDB locks data within a range when we use a range condition. For example, if there are 4 pieces of data whose ids are 1, 3, 5, and 7, we look for data in the range 1-7. So 1-7 is locked. 2, 4, and 6 are also in the range 1-7, but these data records do not exist, and these 2, 4, and 6 are called gaps.

  • Hazards of clearance locks

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

  • Demonstration of clearance lock

Let’s first change the value of field A to 1, 3, 5, 7, 9

  • Window A updates data from A = 1 to 7
update test_innodb_lock set b='b5' where a>1 and a<7;
Copy the code
  • Window B inserts data at a = 2
insert into test_innodb_lock values(2."b6");
Copy the code

At this time, it is found that window B is waiting to update a = 2, because the data in the range of 1 to 7 is locked by gap. A = 2 in window B can be successfully updated only after window A performs commit

Row lock analysis

  • The SQL analysis command is executed
show status like 'innodb_row_lock%';
Copy the code

  • Variable_name instructions

    • Innodb_row_lock_current_waits: The number of locks currently waiting.

    • Innodb_row_lock_time: The lockout period since the system started.

    • Innodb_row_lock_time_avg: Average time spent waiting for a lock.

    • Innodb_row_lock_time_max: The maximum time it takes to wait for a lock since the system started.

    • Innodb_row_lock_waits: Total number of waits for a lock since the system started.

conclusion

You can use the Variable_name parameter to consider whether to optimize, if the lock time, the number of locks is too large, it is time to consider optimization. Optimization means can refer to the previous index optimization article.

IT brother

A senior Java developer in a big factory

Follow the wechat official account: IT Brother

Reply: Java full set of tutorials, you can get: Java foundation, Java Web, JavaEE all tutorials, including Spring Boot, etc

Reply: resume template, you can get 100 exquisite resume

Java Learning Route, you can get the latest and most complete a learning route

Reply: Java ebook, you can get 13 top programmers must read books