Mysql locates Next-Key Lock in one minute. How many points do you need? 01/18 Update SLTechnology News&Howtos

Mysql locates Next-Key Lock in one minute. How many points do you need?

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

Connections and threads

View connection information show processlist

The non-enterprise version of mysql only supports one thread and one link

View the threading model show variables like 'thread_handling'

+-- +-+ | Variable_name | Value | +- -+ | thread_handling | one-thread-per-connection | + -- +

[transaction commit strategy]

There are two hidden transaction commit time points to pay attention to. The first is the autocommit=1 Mysql session-level autocommit variable. All transaction commit controls in ORM framework are affected by this field. By default, the current statement is automatically committed, but if it shows that begin transaction opens the transaction, you need to commit it manually. Sometimes the ORM framework sets autocommit to 0 based on some settings or policies.

The second is that the current transaction is implicitly committed before the DDL operation, and some scripts mix DML and DDL, which leads to consistency issues. DDL automatically commits the current transaction. Because DDL does not support transactional principle operations before 5.7s. (Mysql8.0 already supports DDL transactionality)

Next-Key Lock troubleshooting

Next-Key Lock occurs only under the RR (REPEATABLE-READ) isolation level.

There are many types of pairing locks in Mysql, including table locks, record lock, gap lock, intention sharing / exclusive locks, insert intention locks, metadata locks, Auto_Incr self-increment locks, excluding metadata locks and Auto_Incr self-increment locks, the remaining lock combinations are most commonly used under the RR isolation level.

The RR isolation level is the default transaction isolation level and is one of the strengths of Mysql, where transactions have maximum throughput and no phantom read problems occur. Next-Key Lock is to solve this problem, to put it simply, record lock+gap lock is Next-Key Lock.

The fundamental problem with _ phantom _ is that it appears on the boundary value of the record. For example, we count the number of people over 30 years old: the statement select count (1) peoples where age > 30 may get a different result set for each query, because as long as the record that conforms to age > 30 is entered into our peoples table, it will be hit by the query condition.

Therefore, in order to solve the problem of phantom reading, we not only do not allow the gap of the record to be inserted into the record, but also prevent the record from being modified twice, because if the two records are modified, the interval will become larger and there will be phantom reading.

Let's look at an example.

CREATE TABLE `peoples` (`id` int (11) unsigned NOT NULL AUTO_INCREMENT, `age` int (11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `peoples` (`age`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4+----+-+ | id | age | +-- +-+ | 1 | 20 | 2 | 30 | | 3 | 35 | 4 | 40 | +-+-- +

To facilitate debugging, increase the timeout for innodb to acquire locks.

Show variables like'% innodb_lock_wait%'set innodb_lock_wait_timeout=600

Open two sessions.

Session An id=8:beginselect count (1) from peoples where age > 30 for update;session B id=5:begininsert into peoples (age) values (31)

Show processlist found the connected id.

Select * from information_schema.innodb_trx\ G to view the transaction execution.

457240 transaction state is LOCK WAIT waiting for lock, 457239 transaction state is RUNNING execution, waiting for transaction commit.

Lock

Select * from information_schema.innodb_locks\ G check the occupancy of the lock.

The innodb_locks table contains the lock information that has been acquired and the information that requested the lock. The lock_index field represents the locked index, and record locking is based on the index.

According to the above transaction 457240 status is to acquire lock, lock_data | 35,7, indicates the requested data. Transaction 457239 occupies the current X lock.

Lock waiting

Select * from information_schema.innodb_lock_waits looks at lock waiting information.

* * [1. Row] * * requesting_trx_id | 457240requested_lock_id | 457240:131:4:4blocking_trx_id | 457239blocking_lock_id | 457239, 131, 14, 14, 14, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4.

The 457240 transaction needs to acquire the 131 4-lock, and the 457239 transaction occupies 131-1-4-4 lock.

Innodb monitor

Show engine innodb statusLIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 422032240994144, not started0 lock struct (s), heap size 1136, 0 row lock (s)-TRANSACTION 457240, ACTIVE 394sec insertingmysql tables in use 1, locked 1LOCK WAIT 5 lock struct (s), heap size 1136, 7 row lock (s), undo log entries 1MySQL thread id 5, OS thread handle 140556966967040 Query id 105172.17.0.1 root updateinsert into peoples (age) values (31)-TRX HAS BEEN WAITING 165SEC FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 131page no 4n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2 Compact format; info bits 00: len 4; hex 80000023; asc #; 1: len 4; hex 000007; asc;;-TRANSACTION 457239, ACTIVE 407 sec3 lock struct (s), heap size 1136, 5 row lock (s) MySQL thread id 8, OS thread handle 140556966696704, query id 104172.17.0.1 root

MySQL thread id 5 is preparing to insert an intention lock, which is essentially a gap lock to ensure maximum concurrent insertions, and unrelated row inserts are not mutually exclusive. Thread id 5 needs to ensure that a gap lock is added before insertion, mainly to prevent consistency problems caused by concurrent insertion.

Neither session 5 nor session 8 has a record of operating to id=3,age=35, but it is locked by X+Gap Lock. Only in this way can the problem of phantom reading be solved.

Author: Wang Qingpei (Qitoutiao Tech Leader)

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.