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

This article mainly introduces the type and principle of MySQL lock, which has certain reference value and can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

First, divide the mysql lock:

According to the granularity of locks: row locks, table locks, page locks are divided according to the way locks are used: shared locks, exclusive locks (an implementation of pessimistic locks) and two ideological locks: pessimistic locks and optimistic locks. There are several types of row-level locks in InnoDB: Record Lock, Gap Lock, Next-key LockRecord Lock: lock on index records Gap Lock: gap lock Next-key Lock:Record Lock+Gap Lock table lock

Table-level lock is the most granular lock in MySQL lock, which means that the current operation locks the whole table with less resource overhead than row lock, and there is no deadlock, but there is a high probability of lock conflict. Supported by most mysql engines, both MyISAM and InnoDB support table-level locks, but InnoDB defaults to row-level locks.

Table locks are implemented by MySQL Server, which usually locks the entire table when executing DDL statements, such as ALTER TABLE and so on. When you execute a SQL statement, you can also explicitly specify that a table be locked.

Table locks use one-time locking technology, that is, use the lock command at the beginning of the session to lock all the tables that need to be used later. before the table is released, only these locked tables can be accessed, not other tables, until all table locks are released through unlock tables.

In addition to using the unlock tables display to release locks, executing lock table statements when the session holds other table locks releases the locks previously held by the session; when the session holds other table locks, executing start transaction or begin opens the transaction, it also releases the locks previously held.

Shared lock usage:

LOCK TABLE table_name [AS alias_name] READ copy code

Exclusive lock usage:

LOCK TABLE table_name [AS alias_name] [LOW_PRIORITY] WRITE copy code

Unlock usage:

Unlock tables; copy Line Lock

Row-level locks are the finest-grained locks in Mysql, indicating that locks are only applied to the rows of the current operation. Row-level locks can greatly reduce conflicts in database operations. The locking granularity is the smallest, but the locking overhead is also the largest. There may be a deadlock. According to the mode of use, row-level locks are divided into shared locks and exclusive locks.

The row lock implementation of different storage engines is different, and there is no special description later, then the row lock specifically refers to the row lock implemented by InnoDB.

Before we understand the locking principle of InnoDB, we need to have some understanding of its storage structure. InnoDB is a clustered index, that is, the leaf node of a B+ tree stores both the primary key index and data rows. While the leaf node of the secondary index of InnoDB stores the primary key value, so when querying data through the secondary index, we also need to take the corresponding primary key to cluster the index to query again. For more information about MySQL indexes, see the underlying data structures and algorithms of MySQL indexes.

Let's take the execution of two SQL as examples to explain the locking principle of InnoDB for single-row data.

Update user set age = 10 where id = 49th update user set age = 10 where name = 'Tom'; copy code

If the first SQL uses the primary key index to query, you only need to add a write lock to the primary key index of id = 49.

The second SQL uses a secondary index to query, first add a write lock on the index name = Tom, and then because you need to query again according to the primary key index to use the InnoDB secondary index, you also need to add a write lock on the primary key index of id = 49, as shown in the figure above.

In other words, using a primary key index requires a lock, while using a secondary index requires adding a lock on the secondary index and a lock on the primary key index.

According to the locking principle of updating single row data based on the index, what if the update operation involves multiple rows, such as the following SQL execution scenario.

Update user set age = 10 where id > 49; copy code

The release of the lock in this scenario is more complex, and there are many ways to optimize it. I don't know about this yet. Please leave a message below to explain.

Page lock

Page-level lock is a lock whose granularity is between row-level lock and table-level lock in MySQL. The speed of table-level lock is fast, but the conflict is more, the row-level conflict is less, but the speed is slow. So take the eclectic page level and lock the adjacent set of records one at a time. BDB supports page-level locks.

Shared lock / exclusive lock shared lock (Share Lock)

A shared lock, also known as a read lock, is a lock created by a read operation. Other users can read data concurrently, but no transaction can modify the data (acquire exclusive locks on the data) until all shared locks have been released.

If transaction T adds a shared lock to data A, other transactions can only add a shared lock to A, not an exclusive lock. Transactions that are allowed to share locks can only read data and cannot modify it.

Usage

SELECT... LOCK IN SHARE MODE

Adding LOCK IN SHARE MODE,Mysql after the query statement adds a shared lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for a shared lock, otherwise it will be blocked. Other threads can also read tables that use shared locks, and these threads read the same version of data.

Exclusive lock (eXclusive Lock)

An exclusive lock is also called a write lock. If transaction T adds an exclusive lock to data A, other transactions can no longer add any kind of lock to A. Transactions that are granted an exclusive lock can both read and modify data.

Usage

SELECT... FOR UPDATE

Adding FOR UPDATE,Mysql after the query statement adds an exclusive lock to every row in the query result. When no other thread uses an exclusive lock on any row in the query result set, you can successfully apply for an exclusive lock, otherwise it will be blocked.

Optimistic lock and pessimistic lock

As mentioned in the database locking mechanism, the task of concurrency control in database management system (DBMS) is to ensure that when multiple transactions access the same data in the database at the same time, the isolation and unity of transactions and the unity of the database will not be destroyed.

Optimistic concurrency control (optimistic lock) and pessimistic concurrency control (pessimistic lock) are the main technical means of concurrency control.

Either pessimistic lock or optimistic lock is a concept defined by people and can be regarded as a kind of thought. In fact, it is not only the concept of optimistic lock and pessimistic lock in relational database systems, but also similar concepts such as memcache, hibernate, tair and so on.

Different concurrency control methods should be chosen for different business scenarios. Therefore, do not regard optimistic concurrency control and pessimistic concurrency control as concepts in DBMS in a narrow sense, let alone confuse them with the locking mechanisms provided in data (row locks, table locks, exclusive locks, shared locks). In fact, in DBMS, pessimistic locking is realized by using the locking mechanism provided by the database itself.

Pessimistic lock

In relational database management systems, pessimistic concurrency control (also known as "pessimistic lock", Pessimistic Concurrency Control, abbreviation "PCC") is a method of concurrency control. It prevents one transaction from modifying data in a way that affects other users. If an operation performed by a transaction applies a lock to a row of data, other transactions can perform operations that conflict with the lock only when the transaction releases the lock. Pessimistic concurrency control is mainly used in environments where data contention is fierce and where the cost of using locks to protect data in the event of concurrency conflicts is lower than the cost of rolling back transactions.

Pessimistic lock, as its name suggests, refers to a conservative (pessimistic) attitude towards the modification of data by the outside world (including other current transactions of the system, as well as transactions from external systems), so that the data is locked throughout the data processing process. The implementation of pessimistic locking often depends on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can really ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in this system, there is no guarantee that the external system will not modify the data)

The specific process of pessimistic locking attempts to add an exclusive lock (exclusive locking) to any record before modifying it; if locking fails, it means that the record is being modified, then the current query may have to wait or throw an exception. The specific response mode is decided by the developer according to the actual needs; if the lock is successfully added, the record can be modified and unlocked after the transaction is completed. In the meantime, if there are other operations to modify the record or add an exclusive lock, it will wait for us to unlock or directly throw an exception. Advantages and disadvantages of pessimistic lock

Pessimistic lock actually adopts the strategy of "lock in access first", which ensures the security of data processing, but in terms of efficiency, it generates additional overhead due to the additional locking mechanism and increases the chance of deadlock. It also reduces concurrency; when one thing has a row of data, other things must wait for the transaction to commit before manipulating that row of data.

Optimistic lock

Optimistic concurrency control (also known as "optimistic lock", Optimistic Concurrency Control, abbreviation "OCC") is a method of concurrency control in relational database management systems. It assumes that multi-user concurrent transactions will not affect each other, and that each transaction can process that part of the data affected by each other without creating a lock. Before committing the data update, each transaction checks to see if any other transaction has modified the data after the transaction has read the data. If other transactions are updated, the transaction being committed will be rolled back.

Optimistic lock (Optimistic Locking) compared with pessimistic lock, optimistic lock assumes that the data will not cause conflict in general, so when the data is submitted for update, it will formally detect whether the data conflict or not. If a conflict is found, the user will return the error information and let the user decide what to do.

Compared with pessimistic locks, optimistic locks do not use the locking mechanism provided by the database when dealing with the database. The general way to implement optimistic locking is to record the data version.

Data version, an additional version identification for the data. When reading the data, the value of the version identification is read out together, and each time the data is updated, the version identification is updated at the same time. When we submit the update, we judge that the current version information recorded in the database table is compared with the version identification taken out for the first time, and if the current version number of the database table is equal to the version identification value taken out for the first time, it will be updated, otherwise it is considered out-of-date data.

Advantages and disadvantages of optimistic lock

Optimistic concurrency control believes that the probability of data contention between transactions (data race) is relatively small, so do it as directly as possible and do not lock it until commit time, so there are no locks or deadlocks. However, if you simply do this directly, you may still encounter unexpected results, such as when both transactions read a row in the database and write it back to the database after modification.

Intention sharing lock / intention exclusive lock

Because table locks and row locks have different locking ranges, but they conflict with each other. So when you want to add a table lock, you must first traverse all the records of the table to determine whether there is an exclusive lock. This traversal check is obviously an inefficient way, and MySQL introduces intention locks to detect conflicts between table locks and row locks.

Intention lock is also a table-level lock, which can also be divided into reading intention lock (IS lock) and freehand intention lock (IX lock). When a transaction wants to add a read lock or write lock to a record, it should first add an intention lock to the table. In this way, it is very simple to determine whether there is a record lock in the table, just look at whether the lock is intentional on the following table.

There is no conflict between intention locks, nor with AUTO_INC table locks. It only blocks table-level read locks or table-level write locks. In addition, intention locks do not conflict with row locks, and row locks only conflict with row locks.

The intention lock is automatically added by InnoDB and does not require user intervention.

For insert, update, delete,InnoDB will automatically add an exclusive lock to the data involved (X)

For normal Select statements, InnoDB does not add any locks, and transactions can add shared or exclusive locks to the display through the following statement.

Intention shared lock (Intention Shared Lock)

Intention shared lock (IS): indicates that the transaction is ready to add a shared lock to a data row, that is, a data row must acquire the IS lock of the table before adding a shared lock

Intention exclusive lock (Exclusive Lock)

Intention exclusive lock (IX): similar to the above, it indicates that the transaction is ready to add an exclusive lock to the data row, indicating that the transaction must acquire the IX lock of the table before adding the exclusive lock to the data row.

Record lock (Record Lock)

Record locks are the simplest row locks, and there is nothing to say. The lock described above in the principle of InnoDB locking is a record lock, locking only the record of id = 49 or name = 'Tom'.

When the index cannot be used by the SQL statement, a full table scan is performed, and MySQL adds a record lock to all rows of data in the entire table, which is filtered by the MySQL Server layer. However, when filtering in the MySQL Server layer, if it is found that the WHERE condition is not met, the lock of the corresponding record is released. In doing so, it is guaranteed that only the locks on records that meet the conditions will be held in the end, but the locking operation for each record cannot be omitted.

Therefore, the update operation must be operated according to the index, when there is no index, it will not only consume a lot of lock resources, increase the cost of the database, but also greatly reduce the concurrent performance of the database.

Gap lock (Gap Lock)

When we use range conditions instead of equality conditions to retrieve data and request shared or exclusive locks, InnoDB will lock the index entries of existing data records that meet the conditions; for records whose key values are within the condition but do not exist, InnoDB will also lock this "gap". This locking mechanism is the so-called gap lock.

A gap lock is the interval in the lock index record, or the range before the first index record, or after the last index record.

The only function of gap lock in InnoDB is to prevent the insertion of other transactions, so as to prevent the occurrence of phantom reading, so gap lock makes no difference between shared lock and exclusive lock.

To disable gap locks, you can lower the isolation level to read submitted, or turn on the parameter innodb_locks_unsafe_for_binlog

Show variables like 'innodb_locks_unsafe_for_binlog'; copy code

Innodb_locks_unsafe_for_binlog: default

The value is OFF, which means the gap lock is enabled. Because this parameter is read-only, you need to modify my.cnf (windows is my.ini) to restart if you want to disable the gap lock.

# add [mysqld] innodb_locks_unsafe_for_binlog = 1 copy code case 1: unique index gap lock in [mysqld] in my.cnf

Test environment:

MySQL5.7,InnoDB, default isolation level (RR)

Sample table:

CREATE TABLE `my_ gap` (`id`int (1) NOT NULL AUTO_INCREMENT, `name` varchar (8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `my_ gap` VALUES ('1Qing,' Zhang San'); INSERT INTO `my_ gap` VALUES ('5Qing,' Li Si'); INSERT INTO `my_ gap` VALUES ('7Qing,' Wang Wu'); INSERT INTO `my_ gap` VALUES ('11Qing,' Zhao Liu'); copy the code

Before testing, let's take a look at the hidden gaps in the my_gap table:

(- infinity, 1] (1,5] (5,7] (7,11] (11, + infinity) only use record locks (row locks), and there is no gap lock / * open transaction 1 * / BEGIN;/* query id = 5 data and add record locks * / SELECT * FROM `my_ gap`WHERE `id` = 5 FOR UPDATE;/* delay execution for 30 seconds to prevent lock release * / SELECT SLEEP (30) # Note: the following statements are not executed in a single transaction, but are executed several times separately. In each transaction, there is only one add statement / * transaction 2 inserts a name = 'Jay' data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (4, 'Jay') # normal execution / * transaction 3 insert a name = 'Xueyou' data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (8, 'Xueyou'); # normal execution / * commit transaction 1, release transaction 1 lock * / COMMIT; replication code

In the above case, because the primary key is a unique index, only one index query is used, and only one record is locked, only record locks (row locks) are added to the data with id = 5, without a gap lock.

Generate gap lock

Restore the initialized 4 records and continue to test the following on the id unique index column:

/ * start transaction 1 * / BEGIN;/* query id data in the range of 7-11 and add record locks * / SELECT * FROM `my_ gap`WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;/* delay 30 seconds to prevent lock release * / SELECT SLEEP (30) # Note: the following statements are not executed in a single transaction, but are executed several times separately. In each transaction, there is only one add statement / * transaction 2 inserts a data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (3, 'Sicong 3') in each transaction. # normal execution / * transaction 3 inserts a piece of data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (4, 'Sicong 4'); # normal execution / * transaction 4 inserts a piece of data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (6,' Sicong 6') of id = 6 my_ name = 'Sicong 6' # blocking / * transaction 5 inserts a data of id = 8, name = 'Sicong 8' * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (8,' Sicong 8'); # blocking / * transaction 6 inserts a data of id = 9, name = 'Sicong 9' * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (9,' Sicong 9') # blocking / * transaction 7 inserts a data of id = 11, name = 'Sicong 11' * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (11,' Sicong 11'); # blocking / * transaction 8 inserts a data of id = 12, name = 'Sicong 12' * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (12, 'Sicong 12') # execute / * commit transaction 1 normally, release the lock of transaction 1 * / COMMIT; replication code

As can be seen from the above, data can not be inserted in the two intervals (5, 7) and (7, 11), and data can be inserted normally in other intervals. So it can be concluded that when we add a lock to this interval, we will lock the interval (5d7) and (7d11).

Restore the initialized 4 records, and let's test what happens if the data that doesn't exist is locked.

/ * Open transaction 1 * / BEGIN;/* query the non-existent data id = 3 and add a record lock * / SELECT * FROM `my_ gap`WHERE `id` = 3 FOR UPDATE;/* delays execution by 30 seconds to prevent lock release * / SELECT SLEEP (30) # Note: the following statements are not executed in a single transaction, but are executed several times separately. In each transaction, there is only one add statement / * transaction 2 inserts a piece of data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (2, 'Xiao Zhang'). # blocking / * transaction 3 inserts a piece of data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (4, 'Xiaobai'); # blocking / * transaction 4 inserts a piece of data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (6, 'Xiaodong') # normal execution / * transaction 5 insert an id = 8, name ='Da Luo 'data * / INSERT INTO `my_ gap` (`id`, `name`) VALUES (8,' Da Luo'); # normal execution / * commit transaction 1, release transaction 1 lock * / COMMIT; replication code

As you can see from the above, when you specify that a record is queried, a gap lock occurs if the record does not exist.

Conclusion for the locked statement that specifies to query a record, if the record does not exist, a record lock (row lock) and a gap lock will be generated; if the record exists, only a record lock (row lock) will be generated; for query statements within a certain range, a gap lock will be generated. Case 2: gap lock of general index

Sample table: id is the primary key, and a normal index is created on number.

# Note: number is not the only value CREATE TABLE `my_ gap1` (`id`int (1) NOT NULL AUTO_INCREMENT, `number`int (1) NOT NULL COMMENT 'digit', PRIMARY KEY (`id`), KEY `number` (`number`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `my_ gap1` VALUES (1,1); INSERT INTO `my_ gap1` VALUES (5,3); INSERT INTO `my_ gap1` VALUES (7,8); INSERT INTO `my_ gap1` VALUES (11,12)

Before testing, let's take a look at the hidden gaps in the number index in the my_gap1 table:

(- infinity, 1] (1, 3] (3, 8] (8, 12] (12, + infinity] Test 1

We execute the following transaction (transaction 1 is finally committed), executing the following statements:

/ * start transaction 1 * / BEGIN;/* query data with number = 3 and add record lock * / SELECT * FROM `my_ gap1` WHERE `number` = 3 FOR UPDATE;/* delay 30 seconds to prevent lock release * / SELECT SLEEP (30) # Note: the following statements are not executed in a single transaction, but are executed several times separately. In each transaction, only one add statement / * transaction 2 inserts a data of number = 0 * / INSERT INTO `my_ gap1` (`number`) VALUES (0); # normal execution / * transaction 3 inserts a data of number = 1 * / INSERT INTO `my_ gap1` (`number`) VALUES (1) # blocked / * transaction 4 inserts a data with number = 2 * / INSERT INTO `my_ gap1` (`number`) VALUES (2); # blocked / * transaction 5 inserts a data with number = 4 * / INSERT INTO `my_ gap1` (`number`) VALUES (4); # blocked / * transaction 6 inserts a data with number = 8 * / INSERT INTO `my_ gap1` (`number`) VALUES (8) # normal execution / * transaction 7 inserts a piece of data with number = 9 * / INSERT INTO `my_ gap1` (`number`) VALUES (9); # normal execution / * transaction 8 inserts a piece of data with number = 10 * / INSERT INTO `my_ gap1` (`number`) VALUES (10); # normal execution / * commit transaction 1 * / COMMIT; replication code

We will find that some statements can be executed normally, and some statements are blocked. View the data in the table:

Here you can see that the insert statements are blocked in the gap of number (1pr 8), and the statements that are not in this range are executed normally, which is due to the gap lock.

Test 2

Let's do the following tests, where we restore the data to initialization.

/ * start transaction 1 * / BEGIN;/* query data with number = 3 and add record lock * / SELECT * FROM `my_ gap1` WHERE `number` = 3 FOR UPDATE;/* delay 30 seconds to prevent lock release * / SELECT SLEEP (30); / * transaction 1 inserts a piece of data with id = 2 and number = 1 * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (2,1) # blocking / * transaction 2 inserts a data of id = 3, number = 2 * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (3,2); # blocking / * transaction 3 inserts a data of id = 6, number = 8 * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (6,8) # blocking / * transaction 4 inserts a data of id = 8, number = 8 * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (8,8); # normal execution / * transaction 5 inserts a data of id = 9, number = 9 * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (9,9) # normal execution / * transaction 6 inserts an id = 10, number = 12 data * / INSERT INTO `my_ gap1` (`id`, `number`) VALUES (10,12); # normal execution / * transaction 7 modifies id = 11, number = 12 data * / UPDATE `my_ gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # blocking / * submitting transaction 1 * / COMMIT; replication code

View the data in the table

Here is a strange phenomenon:

Transaction 3 is blocked by adding the data of id = 6 Magi number = 8; transaction 4 adds the data of id = 8 Magi number = 8, which is executed normally; transaction 7 modifies the data of id = 11 Magi number = 12 to the operation of id = 11 Magi number = 5, which is blocked.

Why is that? Let's take a look at the following picture:

As you can see from the library in the figure, when the number is the same, it will be sorted according to the primary key id

Transaction 3 adds id = 6 number = 8, this data is in the interval of (3), so it will block; transaction 4 adds id = 8, this data is in the interval (8), so it won't block; the modification statement of transaction 7 is equivalent to inserting a data in the interval of (3), so it is blocked. Conclusion on the ordinary index column, no matter what kind of query, as long as the lock is added, there will be a gap lock, which is different from the unique index; in the analysis of the data gap between the ordinary index and the unique index, the data rows are sorted first according to the general index, and then sorted according to the unique index. Key lock (Next-key Locks)

Key lock is the combination of record lock (row lock) and gap lock. Its lock range includes both index record and index interval. It refers to the lock added to a record and the gap in front of that record. Suppose an index contains values of 15, 18, 20, 30, 49, and 50, and the possible Next-key locks are as follows:

(- ∞, 15], (15, 18], (18, 20], (20, 30], (30, 49], (49, 50], (50, + ∞) copy code

We usually use this left-open and right-close interval to denote the Next-key lock, where parentheses indicate that the record is not included and square brackets indicate that the record is included. The first four are Next-key locks, and the last one is a gap lock. Like gap locks, there are no Next-key locks under the RC isolation level, only the RR isolation level. As in the previous example, if id is not a primary key, but a secondary index, and is not a unique index, then the SQL will add the following Next-key lock (30,49] (49,50) under the RR isolation level

At this point, if you insert a record with id = 31, it will block. The reason for locking the gap before and after id = 49 is still to solve the problem of phantom reading. Because id is a non-unique index, there may be multiple records for id = 49, in order to prevent the insertion of another record with id = 49.

Note: the main purpose of the key lock is also to avoid Phantom Read. If the transaction isolation level is downgraded to RC, the critical lock will also fail.

Insert intention lock (Insert Intention Locks)

Insert intention lock is a special gap lock (II GAP for short) that indicates the intention to insert, which is available only in INSERT. Note that although this lock is also called an intention lock, it is a completely different concept from the table-level intention lock described above, so don't get confused.

Insert intention locks and insert intention locks do not conflict with each other, so records with different indexes can be inserted simultaneously by multiple transactions in the same gap. For example, between id = 30 and id = 49, it is okay for two transactions to insert id = 32 and id = 33 at the same time, although both transactions add an insert intent lock between id = 30 and id = 50, but there is no conflict.

Inserting an intent lock will only conflict with a gap lock or Next-key lock. As mentioned above, the only function of a gap lock is to prevent other transactions from inserting records from causing illusory readings. It is precisely because the insertion intention lock needs to be inserted when the INSERT statement is executed, and the insertion intention lock conflicts with the gap lock, thus preventing the insertion operation from being executed.

The role of inserting an intention lock:

To evoke waiting. Because the gap already has a lock, it must be blocked during insertion, and the function of the insertion intention lock has blocking function; the insertion intention lock is a special gap lock, since it is a kind of gap lock, why not use the gap lock directly? Gap locks are not mutually exclusive directly. Can not block that is to evoke waiting, will cause illusion. Why not use record locks (row locks) or key locks? The record lock or key lock is applied, and the key lock may be mutually exclusive, that is, it affects the concurrency of insert. Self-increasing lock (Auto-inc Locks)

AUTO_INC locks, also known as self-increment locks (usually abbreviated to AI locks), are table locks that occur when there are self-incrementing columns (AUTO_INCREMENT) in the table. When there are self-incrementing columns in the inserted table, the database needs to automatically generate self-increment. It will first add an AUTO_INC table lock to the table to block the insert operation of other transactions, so as to ensure that the generated self-increment must be unique. The AUTO_INC lock has the following characteristics:

AUTO_INC locks are not compatible with each other, that is, only one self-increment lock is allowed in the same table; once the self-increment is allocated, it will be + 1, and if the transaction rolls back, the self-increment will not be reduced, so the self-increment may be interrupted. Self-increasing operation

The AUTO_INCREMENT function is used to realize the self-increment operation, and the self-increment is controlled by the two parameters auto_increment_offset and auto_increment_increment:

Auto_increment_offset represents the starting number auto_increment_increment represents the range of transfer (that is, each increase of n digits, 2 represents + 2 each time)

The last inserted number can be obtained by using the last_insert_id () function

Select last_insert_id (); copy code self-adding lock

First, insert can be roughly divided into three categories:

Simple insert such as insert into t (name) values ('test') bulk insert such as load data | insert into. Select.... From.... mixed insert such as insert into t (id,name) values (1 null,'b'), (null,'b'), (5)

If there is a self-increment field, MySQL maintains a self-increment lock. One parameter related to the self-increment lock is (added after version 5.1.22) innodb_autoinc_lock_mode. You can set 3 values:

0: traditonal (table locks will be generated every time) 1: consecutive (will generate a lightweight lock, simple insert will get a batch lock to ensure continuous insertion) 2: interleaved (will not lock the table, deal with one at a time, and send the highest)

All MyISam engines are traditonal, and table locks are performed each time. However, the InnoDB engine produces different locks depending on the parameters, and the default is 1:consecutive.

Show variables like 'innodb_autoinc_lock_mode'; copy code traditonal

When innodb_autoinc_lock_mode is 0, that is the traditional level. This self-increment lock shows the lock level, and must wait for the current SQL execution or rollback before it will be released. In the case of high concurrency, it is conceivable that the self-increase lock competition is larger.

It provides a backward compatibility capability in which all insert statements ("insert like") get a table-level auto_inc lock at the beginning of the statement and release the lock at the end of the statement. Note that what we are talking about here is at the statement level rather than the transaction level, and a transaction may contain one or more statements; it ensures the predictability, continuity and repeatability of the value assignment, that is, it ensures that the insert statement can generate the same value as the master when it is copied to slave (it ensures the security of statement-based replication) Because in this mode the auto_inc lock is held until the end of the statement, this affects concurrent insertions. Consecutive

When innodb_autoinc_lock_mode is 1, that is the consecutive level. This means that if it is a single insert SQL, the lock can be acquired immediately and released immediately without having to wait for the current SQL execution to complete (unless a session has acquired the self-increment lock in other transactions). In addition, when SQL is some batch insert SQL, such as insert into. Select..., load data, replace... Select... It is also a table-level lock, which can be understood as having to wait for the current SQL to finish executing before releasing it. It can be considered that the relatively lightweight lock with a value of 1 will not affect replication, and the only drawback is that self-increment is not necessarily completely continuous.

Simple insert is optimized in this mode, and because the number of values inserted by simple insert at once can be determined immediately, MyQL can generate several consecutive values at a time for this insert statement. Generally speaking, this is also safe for replication (it ensures the security of statement-based replication); this mode is also the default mode of MySQL, and the advantage of this mode is that the auto_inc lock is not held until the end of the statement, as long as the statement gets the corresponding value, the lock can be released in advance. Interleaved

When innodb_autoinc_lock_mode is 2, that is the interleaved level. All insert types of SQL can be locked and released immediately, which is the most efficient. But a new problem will be introduced: when binlog_format is statement, replication cannot be secured because batch insert, such as insert. Select... Statement in this case, you can also immediately get a large number of self-increasing id values, do not have to lock the entire table, slave will inevitably cause confusion when playing back the SQL.

Since there are no auto_inc locks in this mode, the performance in this mode is the best, but there is also a problem that the auto_incremant values it gets may not be contiguous for the same statement.

If your binary format is mixed | row, then any of these three values is safe for you to copy.

Since mysql has now recommended that the binary format be set to row, it is better to use innodb_autoinc_lock_mode=2 when binlog_format is not statement so that you may know better performance.

Summarize the characteristics of InnoDB locks when querying without index conditions, InnoDB does use table locks! Because the row lock of MySQL is a lock for index, not a lock for records, although it accesses records of different rows, lock conflicts will occur if the same index key is used. When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes. Even if index fields are used in the condition, whether or not to use indexes to retrieve data is determined by MySQL by determining the cost of different execution plans. If MySQL thinks that full table scanning is more efficient, for example, for small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the SQL's execution plan (explain view) to confirm that the index is actually used. Lock mode

The lock modes are: read intentional lock, freehand lock, read lock, write lock and self-increment lock (auto_inc).

Compatibility Matrix of different Mode Lock

ISIXSXAIIS compatible

Compatible IX compatible compatible

Compatible with S

Compatible

X

AI compatible

To sum up, there are the following points:

Intention locks do not conflict with each other; S locks are only compatible with S/IS locks and conflict with other locks; X locks conflict with all other locks; AI locks are only compatible with intention locks; lock type

According to the granularity of locks, locks can be subdivided into table locks and row locks, and row locks can be further subdivided according to different scenarios, which are Next-Key Lock,Gap Lock gap locks, Record Lock record locks and insert intention GAP locks.

Different lock lock positions are different, for example, the record lock only locks the corresponding record, while the gap lock locks the gap between the record and the record, and the Next-Key Lock belongs to the record and the gap before the record. The locking range of different types of locks is roughly shown in the following figure.

Compatibility matrix of different types of locks

RECORDGAPNEXT-KEYII GAPRECORD

Compatible

Compatible with GAP compatible with NEXT-KEY

Compatible

Compatible with II GAP

Compatible

Where the first row represents the existing lock and the first column represents the lock to be added. Inserting intention locks is special, so let's summarize the insertion intention locks first, as follows:

Inserting an intention lock does not affect other transactions plus any other locks. That is, one transaction has acquired the insert intention lock, which has no effect on other transactions; the insert intention lock conflicts with gap locks and Next-key locks. That is, if a transaction wants to acquire an insert intention lock, it will block if another transaction has added a gap lock or Next-key lock.

The rules for other types of locks are relatively simple:

Gap locks do not conflict with other locks (excluding insertion intention locks)

Record lock and record lock conflict, Next-key lock and Next-key lock conflict, record lock and Next-key lock conflict

Thank you for reading this article carefully. I hope the editor will share what the MySQL lock type and locking principle is helpful to everyone. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.