Analysis of MySQL lock in MySQL database

preface

In his work, the author encountered inserting a piece of data into the MySQL table, but he couldn't insert it. And the same operation can be inserted in Oracle, so I specially studied MySQL 5 7 lock logic.

Introduction of lock

Before introducing locks, you need to understand the transaction isolation level: https://www.cnblogs.com/jssj/p/13437036.html

The main lock types of MySQL are as follows

(1) Shared and exclusive locks

Shared lock: Select lock in share mode; Unable to read in other transaction.

Exclusive lock: Select for update; Other transactions cannot be modified. Add and modify operations occur

(2) Intent locks

Is to upgrade from row lock to table lock.

(3) Record locks

A lock used to update record lines to prevent transaction 1 from updating and transaction 2 from updating, resulting in final data problems.

(4) Gap locks

When updating data in the range, the gap between the data in these ranges cannot be modified or inserted. For example, when updating the data before the primary key id 10 to 100, other transactions cannot insert records between id 10 and 100.

(5) Next key locks

Combine row lock and gap lock: lock the gap and all that are greater than the current index value, and do not allow other transactions to be inserted.

(6) Insert intent locks

Insert intent lock is a special gap lock, but unlike gap lock, it is only used for concurrent insert operations. If the gap lock locks an interval, the insertion intention lock locks a point.

(7) Auto Inc locks

AUTO-INC LOCKS is A table level lock. When auto exists in A table_ In the increment column, when A transaction attempts to insert A record into the table, it will use the special table level lock in the table. The simplest scenario is that when transaction A attempts to insert records into the table, other transactions trying to insert records into the table must wait for transaction A to obtain A continuous primary key for the records inserted by transaction A.

innodb_autoinc_lock_mode controls which algorithm Mysql uses for auto increment lock

Let's look at a case:

Create a table:

CREATE TABLE test(id int primary key not null auto_increment, name VARCHAR(128));

Insert some data

insert into test(name) VALUES('lili');
insert into test(name) VALUES('zhangsan');
insert into test(name) VALUES('leilei');
insert into test(name) VALUES('lisi');
insert into test(name) VALUES('wangwu');
insert into test(name) VALUES('zhaoliu');

Test: start the transaction in one place, then delete the table data and do not commit.

In addition, open a window to test the new statement:

The waiting time-out cannot be submitted. Before the waiting is over, let's take a look at the lock table information provided by mysql

select * from information_schema.innodb_trx t;           -- All currently running transactions 
select * from information_schema.innodb_locks t;         -- Current lock
select * from information_schema.innodb_lock_waits t;    -- Correspondence of lock waiting

 

innodb_trx shows the running of transactions. Obviously, there are two transactions running, and the sql of the locked transaction can be displayed.  

innodb_locks the important information in this table is that it can provide us with why two transactions are mutually exclusive, resulting in locks, such as field lock_mode refers to the specific lock type, X refers to mutex lock and GAP refers to GAP lock

 

innodb_ lock_ Wait this table shows me the relationship between mutually exclusive resources.

The meanings of the fields in these three tables are as follows:

innodb_trx

innodb_locks  

innodb_lock_waits

case

During the ddl operation of modifying the table, we found that the following steps will always be executed and will not stop.

Through: show processlist;

The following message will appear: Waiting for table metadata lock, waiting for the release of the lock.

You can query:

select * from information_schema.innodb_trx;

To find the running record and kill trx_mysql_thread_id ; Production needs to be used with caution, because we don't know what to kill here?

The following is a test case (the demonstration should not use third-party tools such as Navicat):

Delete data in one window without submitting.

In another window, when this record is updated, lock waiting will appear.

At this time, we query the 'performance' of mysql_ schema`. events_ statements_ The current table can see the execution of sql statements.

If there is a large amount of production data, it is actually not easy to check.

Associate mysql tables to find the transaction statements that cause locks and other waiting

SELECT r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

Take 449 in the figure as the condition of the following sql:

SELECT a.sql_text,
    c.id,
    d.trx_started
FROM `performance_schema`.events_statements_current a
JOIN `performance_schema`.threads b ON a.thread_id = b.thread_id
JOIN information_schema.`PROCESSLIST` c ON b.processlist_id = c.id
JOIN information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id
WHERE 1 = 1 and c.id = 449    -- blocking_thread number
ORDER BY d.trx_started;

In this way, we can find out what sql statements do not commit transactions, resulting in lock waiting.

summary

MySQL queries uncommitted transactions. At present, we know that we must wait for locks to be found, otherwise we will not get the execution of uncommitted transactions. If anyone knows, please share it with me. Thank you.

Tags: MySQL

Posted by ephmynus on Fri, 13 May 2022 07:43:21 +0300