Lock problem under MySQL InnoDB

background knowledge

Compared with InnoDB, MyISAM supports transactions and row level locks. When it comes to InnoDB locks, you have to mention transactions. So let's first understand some knowledge about transactions

Transaction and its ACID attribute

A transaction is a logical processing unit composed of a group of SQL statements. A transaction has the following four attributes, usually referred to as the ACID attribute of a transaction.

Atomicity (atomicity): after the transaction starts, all operations are either completed or not done. It is impossible to stop at the intermediate link. Errors in the execution of the transaction will roll back to the state before the transaction starts, and all operations will be as if they did not happen. That is to say, the transaction is an inseparable whole, like atoms learned in chemistry, and is the basic unit of material composition .
Consistency: before and after the transaction, the integrity constraints of the database are not destroyed. For example, when A transfers money to B, it is impossible that A deducts the money, but B does not receive it.
Isolation: at the same time, only one transaction is allowed to request the same data, and there is no interference between different transactions. For example, A is withdrawing money from A bank card, and B cannot transfer money to this card until the withdrawal process of A is completed.
Durability: after the transaction is completed, all updates of the transaction to the database will be saved to the database and cannot be rolled back.

Problems caused by concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources and improve the transaction throughput of database system, so as to support more users. However, concurrent transaction processing also brings the following problems.

Dirty read: transaction A reads the data updated by transaction B, and then transaction B rolls back the operation. Then the data read by transaction A is dirty data
Non repeatable reading: transaction A reads the same data multiple times, and transaction B updates and commits the data during the multiple reading of transaction A, resulting in inconsistent results when transaction A reads the same data multiple times.
Unreal reading: system administrator A changed the scores of all students in the database from specific scores to ABCDE level, but system administrator B inserted A record of specific scores at this time. When system administrator A found that another record had not been changed after the change, it was like an illusion. This is called Unreal reading.

Summary: non repeatable reading and phantom reading are easy to be confused. Non repeatable reading focuses on modification, and phantom reading focuses on addition or deletion. To solve the problem of non repeatable reading, you only need to lock the rows that meet the conditions, and to solve the phantom reading, you need to lock the table

Transaction isolation level

There are basically two ways to realize transaction isolation in MySQL database.

One is to lock the data before reading it to prevent other transactions from modifying the data
Without locking, a consistent data snapshot at the time point of data request is generated through a certain mechanism, and this snapshot is used to provide consistent reading at a certain level (statement level or transaction level). From the perspective of users, it seems that the database can provide multiple versions of agreed data. Therefore, this technology is called data multi version concurrency control (MVCC).

Four transaction isolation levels:

READ UNCOMMITTED: Transactions A and B operate on the same data. Transaction a can read the uncommitted data of transaction B, resulting in unreal reading, non repeatable reading and dirty reading
READ COMMITTED: Transactions A and B operate on the same data. Transaction a can read the data updated by transaction B, resulting in unreal reading and non repeatability
REPEATABLE READ: transaction A and transaction B operate on the same data. Transaction A cannot read the inserted data of transaction B, resulting in unreal reading
SERIALIZABLE: all transactions must be serially executed without dirty reads, unreal reads and non repeatability

Get InnoDB row lock contention

You can check InnoDB_row_lock state variable is used to analyze the contention of row locks damaged by the system:

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.04 sec)

If lock contention is serious, Innodb_row_lock_waits and Innodb_row_lock_time_avg has a high value. You can query information_ View the lock status through the related tables in the schema database, or set InnodDB Monitors to further observe the tables and data rows with lock conflicts and analyze the causes.

(1) By querying the innodb_locks table in the information_schema database, the waiting conditions for unlocking are:

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from innodb_locks;
Empty set, 1 warning (0.01 sec)

mysql>

(2) Observe lock conflicts by setting InnoDB Monitors:

mysql> create table innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

Then view it through the following statement:

mysql> show engine innodb status;
| Type   | Name | Status                                                                                                                                                                                                                                                                                           
| InnoDB |      |
...
------------
TRANSACTIONS
------------
Trx id counter 6076
Purge done for trx's n:o < 6071 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421657844005624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844004704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421657844006544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140182422546176, state: sleeping
Number of rows inserted 55250, updated 1240, deleted 376, read 22512
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.01 sec)

The monitor can be stopped by the following statement:

mysql> drop table innodb_monitor;
Query OK, 0 rows affected (0.02 sec)

After setting the monitor, there will be detailed information about the current lock waiting in the display of show innodb status, including table name, lock type, lock record, etc., so as to further analyze and locate the problem.

Row lock mode and locking method of InnoDB

InnoDB implements two types of row locks

Share (S): allow a transaction to read a row and prevent other transactions from obtaining exclusive locks of the same dataset.
Exclusive lock (X): allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining shared read locks and exclusive write locks of the same data set.

In addition, in order to allow row locks and table locks to coexist and realize multi granularity locking mechanism, InnoDB also has two internal intention locks.

Intentional shared lock (IS): the transaction intends to add a shared lock to a data row. The transaction must obtain the IS lock of the table before adding a shared lock to a data row.
Intentional exclusive lock (IX): the transaction intends to add a row exclusive lock to a data row. The transaction must obtain the IX lock of the table before giving an exclusive lock to a data row.

InnoDB row lock mode compatibility list:

	X	IX	S	IS
X	conflict	conflict	conflict	conflict
IX	conflict	compatible	conflict	compatible
S	conflict	conflict	compatible	compatible
IS	conflict	compatible	compatible	compatible

If the lock mode requested by a transaction is compatible with the current lock, InnoDB will grant the requested lock to the transaction, otherwise the transaction will wait for the release of the lock.

Intent locks are automatically added by InnoDB. For update, delete and insert statements, InnoDB will automatically add exclusive locks (X) to the involved data sets; for ordinary SELECT statements, InnoDB will not add any locks; transactions can add shared locks or exclusive locks to record sets through the following statements.

S hared lock: select * from table_name where... lock in share mode;
Exclusive lock (X): select * from table_name where... for update;

Using lock in share mode to obtain a shared lock is mainly used to confirm the existence of a row of records when data dependencies are required, and ensure that no one updates or delete s the records. However, if the current transaction also needs to update the record, it is likely to cause deadlock.

Note: session1 and session2 are two clients connected to MySQL. The database used is downloaded from the MySQL official website. The download address is: http://downloads.mysql.com/docs/sakila-db.zip

The following is an example of using lock in share mode to add a shared lock:

session1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

Session1 to actor_ The record with id = 178 is added with the share mode shared lock, and session2 is also applicable to the actor_id=178, session1 and session2 can add shared locks, as follows:

session1
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session2
mysql> select * from actor where actor_id=178 lock in share mode;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

Next, session1 uodate 178 records. At this time, session1 will wait for the lock. At the same time, session2 also updates 178 records. At this time, session2 deadlocks and exits;

session1
mysql> update actor set last_name='monore t' where actor_id=178;
...wait for

session2
mysql> mysql> update actor set last_name='monore t' where actor_id=178;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> update actor set last_name='monore t' where actor_id=178' at line 1

session2 commits the transaction, session1 obtains the lock, and the update succeeds.

session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session1
Query OK, 1 row affected (49.29 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from actor where actor_id=178;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

The following is an example of using for update to add an exclusive lock:

session1 to actor_ The row record with id = 178 uses for update to add an exclusive lock. At this time, session2 adds an exclusive lock to 178 again. It will not obtain the lock and will wait.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where actor_id=178 for update;
...wait for

session1 commits the transaction and session2 obtains the lock.

session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

session2
mysql> select * from actor where actor_id=178 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      178 | LISA       | monore t  | 2021-09-02 12:47:50 |
+----------+------------+-----------+---------------------+
1 row in set (4.84 sec)

Implementation of InnoDB row lock

InnoDB row lock is realized by locking the index items of the index. If there is no index, InnoDB will lock the row through the hidden clustered index Row_id to lock records, and InnoDB row locking atmosphere.

Record Lock: Lock index entries.
Gap lock: lock the "gap" between index entries, the "gap" before the first record or the "gap" after the last record.
Next key lock: a combination of the first two, which locks the record and the gap in front of it

The row lock implementation feature of InnoDB means that if data is not retrieved through index conditions, InnoDB will lock all records in the table. The actual effect is the same as that of the lock table! In practical applications, pay special attention to the feature of InnoDB row lock, otherwise it may lead to a large number of lock conflicts and affect the concurrency performance.

When querying without index criteria, InnoDB will lock all records in the table. As follows, the amount field of the payment table has no index.

Session1 locks and queries the data with amount=8.99, and then session2 locks and queries the data with amount=3.99. At this time, session2 will wait for the lock. After session1 commit s, session2 obtains the lock and queries the data. It seems that session1 locks only the row with amount=8.99, but there is lock waiting. The reason is that InnoDB locks all records without index.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|         83 |           3 |        2 |   8.99 |

...
+------------+-------------+----------+--------+

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where amount=3.99 for update;

...wait for

Next, let's take a look at the situation when locking through index conditions, such as session1 locking query payment_id=62, session2 lock query payment_id=81； When the index is used, the lock is only added to the records that meet the index conditions, and there is no waiting for the lock.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=62 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=81 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         81 |           3 |        1 |   8.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)

Because the row lock of MySQL is a lock on the index, although different records are accessed, conflicts will occur if the same index key is used.

For example, the payment table staff_id has an index, amount has no index, session1 locks and queries staff_ For records with id = 1 and amount = 8.99, session2 locks and queries staff_ For records with id = 1 and amount = 3.99, session2 will wait to obtain the lock. Although different rows are accessed, lock conflicts will occur because the lock is added to the index. After session1 commit s, session2 acquires the lock successfully.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=8.99 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         62 |           3 |        1 |   8.99 |
|         81 |           3 |        1 |   8.99 |
|        122 |           5 |        1 |   8.99 |
|        188 |           7 |        1 |   8.99 |
...
+------------+-------------+----------+--------+

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 and amount=3.99 for update;
...wait for

When the table has multiple indexes, different transactions can use different indexes to lock different rows. Whether using primary key index, unique index or ordinary index, InnoDB will use row lock to lock the data.

Customer of payment table_ ID and staff_id is the index, session1 locks and queries customer_id=3, session2 lock query staff_ Line with id = 1, customer_ The data with id = 3 contains staff_ For data with id = 1, session2 will wait for the lock because session1 locks all customers_ Line with id = 3, including staff_id=1, so session2 or wait to acquire the lock.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where customer_id=3 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|         60 |           3 |        1 |   1.99 |
|         61 |           3 |        1 |   2.99 |
|         62 |           3 |        1 |   8.99 |
|         63 |           3 |        1 |   6.99 |
|         64 |           3 |        2 |   6.99 |
|         65 |           3 |        1 |   2.99 |
|         66 |           3 |        1 |   4.99 |
|         67 |           3 |        1 |   4.99 |
|         68 |           3 |        1 |   5.99 |
|         69 |           3 |        2 |  10.99 |
|         70 |           3 |        2 |   7.99 |
|         71 |           3 |        2 |   6.99 |
|         72 |           3 |        1 |   4.99 |
|         73 |           3 |        2 |   4.99 |
|         74 |           3 |        1 |   2.99 |
|         75 |           3 |        1 |   1.99 |
|         76 |           3 |        2 |   3.99 |
|         77 |           3 |        1 |   2.99 |
|         78 |           3 |        2 |   4.99 |
|         79 |           3 |        2 |   5.99 |
|         80 |           3 |        2 |   4.99 |
|         81 |           3 |        1 |   8.99 |
|         82 |           3 |        2 |   2.99 |
|         83 |           3 |        2 |   8.99 |
|         84 |           3 |        2 |   0.99 |
|         85 |           3 |        1 |   2.99 |
+------------+-------------+----------+--------+
26 rows in set (0.00 sec)

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where staff_id=1 for update;

...wait for

Even if the index field is used in the condition, MySQL determines whether to use the index to retrieve data by judging the cost of different execution plans. If MySQL thinks that the full table scanning efficiency is higher, for example, indexes will not be used for some small tables. In this case, InnoDB will lock all rows. Therefore, don't forget to analyze the sql execution plan when analyzing lock conflicts.

Next key lock

When retrieving data with range conditions instead of equal conditions and requesting shared locks or exclusive locks, InnoDB will lock the index entries of qualified data rows. For records whose key values are within the condition range but do not exist, it is called "GAP". InnoDB will lock this "GAP". This locking mechanism is the so-called next key lock.

For example, lock the query payment_ For data with ID > 16048, 16049 records will be locked, and the "gap" of records larger than 16049 (non-existent) will also be locked.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id>16048 for update;
+------------+-------------+----------+--------+
| payment_id | customer_id | staff_id | amount |
+------------+-------------+----------+--------+
|      16049 |         599 |        2 |   2.99 |
+------------+-------------+----------+--------+
1 row in set (0.00 sec)

session2
mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16050, 3, 2, 1.99);

... wait for

In particular, in addition to using the next key lock when locking through the range condition, InnoDB will also use the next key if it uses the equal condition to request the locking of a nonexistent record. For example, session1 requests payment for locking_ For the record with id = 16051 (the record does not exist), session2 inserts the record with payment_id=16051 and waits for the lock.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select payment_id, customer_id, staff_id, amount from payment where payment_id=16051 for update;
Empty set (0.01 sec)

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into payment (payment_id, customer_id, staff_id, amount) value (16051, 3, 2, 1.99);

...wait for

When is the watch lock used

Transactions need to update most or all of the data. The table is relatively large. If row locks are used, not only the execution efficiency of this transaction is low, but also other transactions may wait for locks for a long time and conflict with locks. In this case, table locks can be considered to improve the execution efficiency of transactions.
Transactions involve multiple tables and are complex, which may cause deadlock and cause a large number of transaction rollback. In this case, you can also consider locking the tables involved in the transaction at one time, so as to avoid deadlock and reduce the overhead caused by transaction rollback.

Of course, there should not be too many transactions in these two cases. Otherwise, you should consider using the MyISAM table.

deadlock

Deadlock example: session1 locks and queries the payment table payment_id=15866 record, session2 lock query actor table actor_id=200 record, and then session1 locks and queries the actor table actor_id=200. At this time, because session2 holds the lock, session1 waits for the lock, and then session2 locks and queries the payment table payment_id=15866 record. At this time, InnoDB detects a deadlock, session2 exits, and session1 queries the actor_ Record with id = 200.

session1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from payment where payment_id=15866 for update;
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
|      15866 |         592 |        1 |     11410 |   8.99 | 2005-08-02 19:29:01 | 2006-02-15 22:23:29 |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
1 row in set (0.00 sec)

session2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from actor where actor_id=200 for update;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.01 sec)

session1
mysql> select * from actor where actor_id=200 for update;

... Waiting lock

session2，InnoDB Deadlock detected, exit transaction
mysql> select * from payment where payment_id=15866 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


session2 Exit the transaction, session1 Get lock
mysql> select * from actor where actor_id=200 for update;

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      200 | THORA      | TEMPLE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (6.53 sec)

It can be seen from the examples that InnoDB can automatically detect deadlocks, and make one transaction release the lock and fallback, and another transaction can continue to execute normally after obtaining the lock. However, when external locks and table locks are involved, InnoDB can not fully automatically detect deadlocks, so it is necessary to set the timeout of lock waiting_ lock_ wait_ Timeout. This parameter is not only used to solve the deadlock problem. In the case of high concurrency, if a large number of transactions are suspended because they cannot obtain the required locks immediately, it will occupy a lot of computer resources, cause serious performance problems, and even bring down the database. This can be avoided by setting an appropriate lock waiting threshold.

Common methods to avoid deadlock

In application, if different programs will access multiple tables concurrently, it should be agreed to access tables in the same order as far as possible, which can greatly reduce the chance of deadlock.
When the program processes data in batch, if the data is sorted to ensure that each thread processes records in a fixed order, the possibility of deadlock can also be greatly reduced.
In a transaction, if you want to update a record, you should directly apply for a lock of sufficient level, that is, an exclusive lock. You should not apply for a shared lock first, and then apply for an exclusive lock during update, so as to prevent other transactions from obtaining a shared lock during update, resulting in lock conflict or even deadlock.
At the REPEATABLE-READ isolation level, if two threads lock the same record with select... for update at the same time, both threads will lock successfully without matching the record. When the program finds that the record does not exist, it tries to insert a new record. If both threads do so, a deadlock will occur.
When the isolation level is read committed, if both threads execute select... for update first, judge whether there are qualified records. If not, insert the records. At this time, only one thread can be successfully inserted, and the other thread will wait for a lock. After the first thread submits, the second thread will make repeated errors due to the primary key. Although an error occurs, it will obtain an exclusive lock! At this time, if the third thread applies for an exclusive lock again, a deadlock will also occur.

summary

InnoDB implements row locking based on indexes. If data is not accessed through index locking, InnoDB will lock all data.
At different isolation levels, InnoDB's locking mechanism and consistent reading strategy are not working.
This paper introduces the next key lock mechanism and the reason why InnoDB uses the next key lock.
Carefully design the index and try to use the index to access data, so as to make the granularity of locking smaller, so as to reduce the chance of lock conflict.
Select a reasonable transaction, and the probability of deadlock of small transactions is small.
Try to use equal conditions to access data to avoid the impact of next key lock on concurrent insertion.
Some common methods to avoid deadlock
For some specific transactions, table locks can be used to improve processing speed
Do not apply for more than the actual lock level; Do not display locks when querying unless necessary.

reference resources

MySQL database development, optimization, management and maintenance (2nd Edition)

Keywords: Database MySQL index innodb

Added by techbinge on Tue, 14 Dec 2021 21:28:57 +0200

Programming VIP