In-depth understanding of MySQL lock types and locking principles

Click “like” to see, form a habit, the public account search [dime technology] pay attention to more original technical articles. This article has been included in GitHub org_Hejianhui /JavaStudy.

preface

MySQL indexes underlying data structures and algorithms
MySQL Performance Optimization Principles – Previews
MySQL Performance Optimization – Practice 1
MySQL Performance Optimization – Practice 2
MySQL lock and transaction isolation level

Previously we talked about MySQL database underlying data structure and algorithm, MySQL performance optimization some content. MySQL row locks and transaction isolation levels. This article will focus on the lock type and lock principle.

Mysql > select * from user where id = 1;

According to the granularity of locks: row locks, table locks, page locks
By lock usage: shared locks, exclusive locks (an implementation of pessimistic locks)
There are also two kinds of locks on the mind: the pessimistic lock and the optimistic lock.
InnoDB has several row-level locking types: Record Lock, Gap Lock, next-key Lock
Record Lock: Locks an index Record
Gap Lock: a Gap Lock
Next-key Lock: Record Lock+Gap Lock

Table locks

Table level locks are the largest type of MySQL locks, indicating that the current operation locks the entire table. The resource cost is lower than that of row locks, and deadlocks do not occur, but lock conflicts are likely to occur. Table locking is supported by most mysql engines. MyISAM and InnoDB both support table locking, but InnoDB uses row locking by default.

TABLE locking is implemented by MySQL Server. It is generally used to lock the entire TABLE when executing DDL statements, such as ALTER TABLE operations. You can also explicitly specify that a table is locked when executing an SQL statement.

Table locks use the one-time lock technique, that is, you use the lock command at the beginning of the session to lock all tables that need to be used later. Until the table is released, only these tables can be accessed and other tables cannot be accessed.

In addition to using UNLOCK Tables to display the release of a lock, executing the Lock TABLE statement when a session holds another table lock releases the lock previously held by the session. When a session holds other table locks and executes start transaction or BEGIN to start a transaction, the previously held locks are also released.

Shared lock usage:

LOCK TABLE table_name [ AS alias_name ] READ
Copy the code

Exclusive lock usage:

LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

Copy the code

Unlock usage:

unlock tables;
Copy the code

Row locks

Row-level locking is the most fine-grained lock in Mysql. It only locks the current row. Row-level locking can greatly reduce conflicts in database operations. Its locking particle size is the smallest, but the locking cost is also the largest. Deadlock situations can occur. Row-level locks are divided into shared locks and exclusive locks according to their usage.

Different storage engines have different implementation of row locking. If not specified later, row locking refers to InnoDB’s implementation of row locking.

Before understanding the locking principle of InnoDB, we need to have a certain understanding of its storage structure. InnoDB is a clustered index, that is, leaves of a B+ tree store both primary key indexes and rows. The leaf node of InnoDB secondary index stores the primary key value, so when querying data through secondary index, it is necessary to take the corresponding primary key to cluster index for query again. You can read more about MySQL indexesMySQL Index Underlying Data Structures and Algorithms.

The following two SQL execution as an example, explain InnoDB for a single row of data lock principle.

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';
Copy the code

Select * from primary key where id = 49; select * from primary key where id = 49;

Select * from InnoDB where id = 49; select * from InnoDB where id = 49; select * from InnoDB where id = 49; select * from InnoDB where id = 49;

That is, a primary key index requires a lock, and a secondary key index requires a lock.

If the update operation involves more than one row, as in the following SQL execution scenario.

update user set age = 10 where id > 49;
Copy the code

The lock release in this scenario is complicated, and there are many optimization methods. I haven’t understood this yet, please leave a comment below to explain.

Page locks

Page-level locking is a kind of lock whose granularity is between row-level locking and table-level locking in MySQL. Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. So a compromise page level is taken, locking adjacent sets of records at a time. BDB supports page-level locking.

Shared/exclusive locks

Share Lock

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

If transaction T holds A shared lock on data A, other transactions can only hold A shared lock on data A, not an exclusive lock. Transactions that are allowed to share locks can only read data, not modify it.

usage

SELECT ... LOCK IN SHARE MODE;

If you add LOCK IN SHARE MODE to the end of the query statement, Mysql will add a shared LOCK to each row IN the query result. If no other thread uses an exclusive LOCK on any row IN the query result set, Mysql can apply for a shared LOCK. Otherwise, Mysql will block. A table with a shared lock can be read by other threads reading the same version of data.

EXclusive Lock

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

usage

SELECT ... FOR UPDATE;

If no other thread uses an exclusive lock on any row in the query result set, the Mysql database can apply FOR an exclusive lock. Otherwise, the Mysql database will block.

Optimistic locks and pessimistic locks

In the database lock mechanism introduced, 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 the transaction and the unity of the database will not be destroyed.

Optimistic concurrency control (optimistic locking) and pessimistic concurrency control (pessimistic locking) are the main techniques used in concurrency control.

Both pessimistic lock and optimistic lock are concepts defined by people and can be considered as an idea. In fact, it is not only the relational database system that has the concept of optimistic locking and pessimistic locking, such as memcache, Hibernate, Tair, etc.

Different concurrency control methods should be selected for different business scenarios. Therefore, optimistic concurrency control and pessimistic concurrency control should not be narrowly understood as DBMS concepts, let alone confused with the locking mechanisms provided in data (row, table, exclusive, shared). In fact, in DBMS, pessimistic lock is to use the lock mechanism provided by the database itself to achieve.

Pessimistic locking

In relational database management systems, Pessimistic Concurrency Control (also known as “Pessimistic lock”, or “PCC”) is an approach to Concurrency Control. It prevents a transaction from modifying data in a way that affects other users. If a transaction performs an operation that applies a lock to a row, other transactions can perform operations that conflict with the lock only if the transaction releases the lock. Pessimistic concurrency control is primarily used in environments where data contention is intense and where the cost of locking to protect data in the event of a concurrency conflict is lower than the cost of rolling back transactions.

Pessimistic locking, as its name suggests, refers to being conservative (pessimistic) about data being modified by other transactions currently on the system, as well as transactions from an external system, and therefore keeping the data locked for the entire process. The realization of pessimistic lock, often rely on the locking mechanism provided by the database (only the locking mechanism provided by the database layer can truly ensure the exclusivity of data access, otherwise, even if the locking mechanism is implemented in the system, it can not guarantee that the external system will not modify the data)

Pessimistic lock specific process

Before modifying any record, try adding exclusive locking to the record.
If the lock fails, the record is being modified, and the current query may wait or throw an exception. The specific response mode shall be determined by the developer according to the actual needs;
If the lock is successfully locked, changes can be made to the record and the transaction will be unlocked after completion.
In the meantime, if there are other operations to modify the record or add an exclusive lock, they will wait for us to unlock or directly throw an exception.

Advantages and disadvantages of pessimistic locking

Pessimistic locking actually adopts the policy of “lock in access first”, which ensures the security of data processing. However, in terms of efficiency, the extra locking mechanism produces extra overhead and increases the chance of deadlocks. And reduce concurrency; When one transaction has a row of data, other transactions must wait for the transaction to commit before they can operate on the row.

Optimistic locking

Optimistic Concurrency Control (also known as Optimistic Concurrency Control, or “OCC” for short) is one approach to Concurrency Control in relational database management systems. It assumes that multi-user concurrent transactions can be processed without affecting each other and that each transaction can process its affected portion of data without locking. Before committing data updates, each transaction checks to see if other transactions have modified the data since the transaction read it. The committed transaction is rolled back if updates are made to other transactions.

Optimistic Locking is different from pessimistic Locking. Optimistic Locking assumes that data will not cause conflict in general, so when data is submitted for update, whether data conflict will be detected formally. If a conflict is found, the wrong information will be returned to the user so that the user can decide what to do.

In contrast to pessimistic locks, optimistic locks do not use the locking mechanism provided by the database when processing the database. A common way to implement optimistic locking is to record the version of the data.

Data version, a version identifier added to the data. When reading data, the value of the version id is read together, and the version ID is updated each time the data is updated. When we submit the update, we compare the current version information of the database table with the version id taken out for the first time. If the current version number of the database table is equal to the version id value taken out for the first time, it will be updated; otherwise, it is considered as expired data.

The advantages and disadvantages of optimistic locking

Optimistic concurrency control believes that the probability of a data race between transactions is low, so proceed as directly as possible and do not lock until commit time, so there are no locks and deadlocks. However, if you simply do this, you can still run into unexpected results, such as two transactions that read a row from the database, modify it and write it back to the database, and then run into problems.

Intentional shared lock/intentional exclusive lock

Because table lock and row lock although the lock scope is different, but will conflict with each other. So when you want to add a table lock, you must first go through all the records in the table to determine whether there is an exclusive lock. MySQL has introduced intentional locks to detect conflicts between table locks and row locks.

Intent lock IS also table level lock, also can be divided into read intent lock (IS lock) and freehand direction lock (IX lock). When a party wants to place a read or write lock on a record, it first places an intent lock on the table. It is easy to determine if there is a record lock in the table, just look at the table if there is an intent lock on the line.

Intent locks do not conflict with each other, nor with AUTO_INC table locks. They only block table read locks or table write locks. Intent locks do not conflict with row locks, but row locks only.

Intent locks are automatically added by InnoDB without user intervention.

For inserts, updates, and deletes, InnoDB automatically adds an exclusive lock (X) to the data involved.

For normal Select statements, InnoDB does not attach any locks. Transactions can attach shared or exclusive locks to displays by using the following statements.

Intention Shared Lock

Intended Shared lock (IS) : indicates that a transaction intends to add a shared lock to a row. That IS, before adding a shared lock to a row, an IS lock must be obtained for the table

Exclusive Lock

Intentional exclusive lock (IX) : similar to above, indicates that a transaction intends to assign an exclusive lock to a row, indicating that a transaction must acquire an IX lock on that table before it can assign an exclusive lock to a row.

Record Lock

Record locks are the simplest row locks, and there’s nothing to say. InnoDB lock (id = 49 or name = ‘Tom’)

When an index cannot be used by an SQL statement, a full table scan is performed. In this case, MySQL locks all rows of the entire table and filters them by the MySQL Server layer. However, when filtering at the MySQL Server layer, if the WHERE condition is not met, the lock of the corresponding record is released. This ensures that only the records that meet the criteria will end up holding the lock, but the lock operation for each record cannot be omitted.

Therefore, the update operation must be performed according to the index. Without the index, it will not only consume a large number of lock resources, increase the overhead of the database, but also greatly reduce the concurrency performance of the database.

Gap Lock

When we retrieve data using a range condition rather than an equality condition and request a shared or exclusive lock, InnoDB locks the index entries of existing data records that meet the condition. InnoDB also locks the “gap” for records whose keys are in the condition range but do not exist. This locking mechanism is called gap locking.

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

The only function of a gap lock in InnoDB is to prevent the insertion of other transactions to prevent phantom reading, so there is no shared lock or exclusive lock.

To disable gap locking, either lower the isolation level to read committed or turn on the innodb_locks_unsafe_for_binlog parameter

 show variables like 'innodb_locks_unsafe_for_binlog';
Copy the code

innodb_locks_unsafe_for_binlog: The default value is OFF, that is, the gap lock is enabled. Because this parameter is in read-only mode, if you want to disable gap locking, you need to modify my.cnf (Windows is my.ini) to restart.

# add [mysqld] innodb_locks_unsafe_for_binlog to [mysqld] in my.cnf= 1
Copy the code

Case 1: Gap lock with unique index

Test environment:

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

The sample list:

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 ('1'.'Joe');
INSERT INTO `my_gap` VALUES ('5'.'bill');
INSERT INTO `my_gap` VALUES ('7'.'Cathy');
INSERT INTO `my_gap` VALUES ('11'.'Daisy');
Copy the code

Before we test, let’s look at the hidden gaps in my_gap:

(-infinity, 1]
(1, 5]
(5, 7)
(7, 11]
(11, +infinity]

Only record locks (row locks) are used and no gap locks are created

/* Start transaction 1 */
BEGIN;
/* select * from * where id = 5 */
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 multiple times, with only one addstatement per transaction/* Transaction 2 inserts a row of data with name = 'jay' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4."Jay"); # Normal execution/* Transaction 3 inserts a row with name = 'xueyou' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8.'xueyou'); # Normal execution/* Commit transaction 1, release transaction 1 lock */
COMMIT;
Copy the code

In the above case, because the primary key is a unique index, and only one index query is used, and only one record is locked, only a record lock (row lock) is applied to the data with ID = 5, and no gap lock is generated.

Generate gap lock

Restore the initialized 4 records and continue with the following tests on the ID-unique index column:

/* Start transaction 1 */
BEGIN;
/* Select * from data where id = 7 and id = 11 */
SELECT * FROM `my_gap` WHERE `id` BETWEEN 5 AND 7 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 multiple times, with only one addstatement per transaction/* Transaction 2 inserts a row with id = 3 and name = 'scong 3' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (3.'think astute 3'); # Normal execution/* select * from transaction 3 where id = 4 and name = 'secong 4' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4.'think chung 4'); # Normal execution/* select * from transaction 4 where id = 6 and name = 'secong 6' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (6.'think astute 6'); # block/* Insert a row with id = 8 and name = 'scong 8' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8.'think astute 8'); # block/* Select * from transaction 6 where id = 9 and name = 'scong 9' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (9.'think astute 9'); # block/* Select * from transaction 7 where id = 11 and name = 'secong 11' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (11.'think astute 11'); # block/* Insert a row with id = 12 and name = 'scong 12' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (12.'think astute 12'); # Normal execution/* Commit transaction 1, release transaction 1 lock */
COMMIT;
Copy the code

As can be seen from the above, data cannot be inserted into the two ranges (5,7] and (7,11], while data can be inserted into the other ranges normally. Therefore, it can be concluded that when we lock the interval of (5,7], the interval of (5,7] and the interval of (7,11] will be locked.

Restore the initialized 4 records and test what happens if you lock data that doesn’t exist.

/* Start transaction 1 */
BEGIN;
Select * from * where id = 3 and lock */
SELECT * FROM `my_gap` WHERE `id` = 3 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 multiple times, with only one addstatement per transaction/* Transaction 2 inserts a row with id = 3 and name = 'zhang' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (2.'zhang'); # block/* insert a row with id = 4 and name = 'white' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (4.'white'); # block/* select * from transaction 4 where id = 6 and name = 'xiaodong' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (6.'the east'); # Normal execution/* transaction 5 insert a row with id = 8 and name = 'big' */
INSERT INTO `my_gap` (`id`, `name`) VALUES (8.'大罗'); # Normal execution/* Commit transaction 1, release transaction 1 lock */
COMMIT;
Copy the code

As can be seen from the above, a gap lock will be generated if the specified query record does not exist.

conclusion

If a record does not exist, a record lock (row lock) and a gap lock will be generated. If a record exists, only a record lock (row lock) will be generated.
A gap lock is created for queries that look for a range.

Case 2: Gap locks for normal indexes

Example table: ** ID is the primary key, and on number, a normal index is created. 习皇帝

# Note: number is not the unique valueCREATE TABLE `my_gap1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT 'digital'.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);
Copy the code

Before we test, let’s look at the hidden gap in the number index in my_gap1:

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

Test 1

We execute the following transaction (transaction 1 commits last), executing the following statement respectively:

/* Start transaction 1 */
BEGIN;
/* select * from * where number = 3 */
SELECT * FROM `my_gap1` WHERE `number` = 3 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 multiple times, with only one addstatement per transaction/* Transaction 2 inserts a number = 0 */
INSERT INTO `my_gap1` (`number`) VALUES (0); # Normal execution/* Transaction 3 inserts a row of data with number = 1 */
INSERT INTO `my_gap1` (`number`) VALUES (1); # is blocked/* Transaction 4 inserts a number = 2 */
INSERT INTO `my_gap1` (`number`) VALUES (2); # is blocked/* Transaction 5 inserts a row with number = 4 */
INSERT INTO `my_gap1` (`number`) VALUES (4); # is blocked/* Transaction 6 inserts a number = 8 */
INSERT INTO `my_gap1` (`number`) VALUES (8); # Normal execution/* Transaction 7 inserts a row with number = 9 */
INSERT INTO `my_gap1` (`number`) VALUES (9); # Normal execution/* Transaction 8 inserts a row with number = 10 */
INSERT INTO `my_gap1` (`number`) VALUES (10); # Normal execution/* Commit transaction 1 */
COMMIT;
Copy the code

We will find that some statements execute normally and some statements are blocked. View the data in the table:As you can see, insert statements are blocked in the gap between number(1,8), and statements outside the gap are executed normally. This is because of the gap lock.

Test 2

Let’s do the following test again, where the data is restored as it was initialized

/* Start transaction 1 */
BEGIN;
/* select * from * where number = 3 */
SELECT * FROM `my_gap1` WHERE `number` = 3 FOR UPDATE;
/* Delay execution for 30 seconds to prevent lock release */
SELECT SLEEP(30);

/* Insert a row with id = 2 and number = 1 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (2.1); # block/* Insert a row of data with id = 3 and number = 2 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (3.2); # block/* insert a row with id = 6 and number = 8 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (6.8); # block/* transaction 4 insert a row with id = 8 and number = 8 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (8.8); # Normal execution/* insert a row with id = 9 and number = 9 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (9.9); # Normal execution/* Transaction 6 insert a row with id = 10, number = 12 */
INSERT INTO `my_gap1` (`id`, `number`) VALUES (10.12); # Normal execution/* transaction 7 alter table id = 11, number = 12 */
UPDATE `my_gap1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; # block/* Commit transaction 1 */
COMMIT;
Copy the code

View the data in the table;Here’s a weird thing:

Select * from transaction 3 where id = 6 and number = 8;
Select * from transaction 4 where id = 8 and number = 8;
Id = 11, number = 5; id = 11, number = 5;

Why is that? Let’s take a look at the picture below:As you can see from the library in the figure, when the number is the same, the primary key ID is sorted

Transaction 3 added id = 6, number = 8, this data is in the interval of (3,8), so it will block;
Id = 8, number = 8; id = 8, number = 8;
The change statement in transaction 7, which is equivalent to inserting a piece of data into the interval (3,8), is also blocked.

conclusion

On a normal index column, any query that is locked produces a gap lock, unlike a unique index;
In common index and unique index, data gap analysis, data rows are sorted according to common index first, then according to unique index.

Next-key Locks

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

(- up, 15], (15, 18], (18, 20], (20, 30], (30, 49], (49, 50], (50 + up)Copy the code

The next-key lock is typically represented by a left open, right closed interval, where parentheses indicate exclusion of the record and square brackets indicate inclusion of the record. The first four are next-key locks, and the last one is a gap lock. Like gap locks, there is no next-key lock at the RC isolation level, only at the RR isolation level. As in the previous example, if the ID is not a primary key, but a secondary index, and not a unique index, then the SQL at RR isolation level will add the following next-key lock (30, 49) (49, 50)

Inserting a record with id = 31 will block. If id = 49 is not a unique index, there may be multiple records. In order to prevent the insertion of another record with ID = 49.

Note: The main purpose of the keylock is also to avoid Phantom Read. If the transaction isolation level is reduced to RC, the temporary key lock will also fail.

Insert Intention Locks

INSERT intention lock is a special type of GAP lock (II GAP for short) that indicates INSERT intention. This lock is only available during INSERT. Note that this lock is also called an intent lock, but it is a completely different concept from the table level intent lock introduced above.

There is no conflict between insert intent locks and insert intent locks, so multiple transactions can insert records of different indexes simultaneously in the same gap. For example, if there are two transactions between id = 30 and id = 49, it is ok to insert intent lock between id = 32 and id = 33, respectively.

INSERT intent locks only conflict with a gap lock or next-key lock. As mentioned above, the only purpose of a gap lock is to prevent phantom reads from other transaction inserts. INSERT intent locks conflict with a gap lock, preventing INSERT operations.

Insert intent lock function:

To evoke waiting. Because there is already a lock in this gap, it must be blocked when inserting, and the function of inserting the intent lock has the blocking function.
Insert intention lock is a special clearance lock, since it is a clearance lock, why not directly use clearance lock? Gap locks are not mutually exclusive. Do not block to arouse wait, will cause magic read.
Why not use record locks (row locks) or keylocks? Either a record lock or a near-key lock was applied for, which can be mutually exclusive, affecting insert concurrency.

Auto-inc Locks

An AUTO_INC lock, also called an AUTO_INCREMENT lock (commonly abbreviated as AI lock), is a table lock that occurs when a table has an AUTO_INCREMENT column. When an insert table has an AUTO_INC table lock, the database automatically generates an AUTO_INC table lock for the table, blocking the insert operations of other transactions, and ensuring that the generated AUTO_INC column is unique. AUTO_INC locks have the following features:

AUTO_INC locks are incompatible, which means that only one autoincrement lock is allowed on the same table at the same time.
Self-increment is +1 once allocated and does not go back if the transaction is rolled back, so self-increment may be interrupted.

On the operation

Use the AUTO_INCREMENT function to implement the increment operation. The increment is controlled by the auto_increment_offset and auto_increment_increment parameters:

Auto_increment_offset indicates the starting number
Auto_increment_increment indicates the increment of n digits.

The last inserted number can be obtained by using the 314219 function

select 314219;
Copy the code

On the lock

First, inserts can be roughly divided into three categories:

Simple insert e.g. Insert into T (name) values(‘test’)
Bulk insert, such as the load data | insert into… select …. from ….
Mixed insert such as insert into the t (id, name) values (1, ‘a’), (null, ‘b’), (5, ‘c’);

Innodb_autoinc_lock_mode innodb_autoinc_lock_mode innodb_autoinc_lock_mode innodb_autoinc_lock_mode innodb_autoinc_lock_mode

0: traditonal (table locks are generated every time)
1: row (creates a light lock, and simple insert obtains a batch of locks to ensure continuous inserts)
2: Interleaved (no lock table, to process one by one, maximum concurrency)

MyISam engines are all traditonal, and table locks will be carried out every time. However, the InnoDB engine generates a different lock depending on the parameter. The default is 1: consecutive.

 show variables like 'innodb_autoinc_lock_mode';
Copy the code

traditonal

Innodb_autoinc_lock_mode is set to 0, which is traditional. The auto-add lock is the level of the table lock and can only be released after the current SQL execution is completed or rolled back. In the case of high concurrency, it can be imagined that the auto-add lock competition is relatively large.

It provides a backward compatibility capability
In this mode, all INSERT statements (” INSERT like “) acquire a table-level auto_INC lock at the beginning of the statement and release it at the end of the statement. Note that this is at the statement level, not the transaction level. A transaction may contain one or more statements;
It ensures predictability, continuity, and repeatability of value assignment. This ensures that the insert statement generates the same value as the master statement when copied to the slave (it ensures security based on statement replication).
Since the auto_inc lock is held until the end of the statement in this mode, this affects concurrent inserts.

consecutive

Innodb_autoinc_lock_mode is set to 1, which is the size of the row. This means that if it is a single INSERT SQL, the lock can be acquired immediately and released immediately, without waiting for the current SQL execution to complete (unless a session already acquired the auto-increment lock in another transaction). Insert into SQL… select … , load data, replace… select … Is still a table-level lock, which can be interpreted as having to wait for the current SQL execution to complete. It can be argued that a relatively lightweight lock with a value of 1 has no effect on replication, with the only drawback being that the auto-increment may not be completely continuous.

This mode is optimized for Simple Insert. Since the number of simple Insert values can be determined immediately, MyQL can generate several consecutive values at a time for the insert statement. This is also safe for replication in general (it guarantees security for statement based replication);
This mode is also the default mode of MySQL. 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 early.

interleaved

Innodb_autoinc_lock_mode is set to 2, which is the interleaved level. All INSERT types of SQL are most efficient when locks can be acquired and released immediately. However, a new problem is introduced: when the binlog_format statement is used, replication cannot be guaranteed because a batch of inserts, such as insert… select … In this case, the slave can also immediately obtain a large number of self-incrementing ID values, without locking the entire table, which will inevitably cause confusion when replaying the SQL.

Since there is no auto_inc lock in this mode, performance is best, but there is a problem that the auto_INCREmant value it gets may not be continuous for the same statement.

If you have a binary file format is mixed | row so that any one of three values are copied safe for you.

Mysql now recommends that the binary format be set to row, so innodb_autoinc_lock_mode=2 if binlog_format is not a statement for better performance.

conclusion

InnoDB lock features

InnoDB does use table locks when queries are not indexed.
MySQL row locks are used to access rows of different rows, but lock conflicts can occur if the same key is used to access rows of different rows.
When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition,InnoDB uses row locks to lock data whether using primary key indexes, unique indexes, or normal indexes.
Even if index fields are used in a condition, it is up to MySQL to determine whether to use indexes to retrieve data by determining the cost of different execution plans. If MySQL decides that a full table sweep is more efficient, such as for some small tables, it will not use indexes. In this case InnoDB uses table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan (explain view) to confirm that indexes are actually being used.

The lock mode

The lock modes include: read intention lock, freehand direction lock, read lock, write lock and auto_INC.

Compatibility matrix for different mode locks

	IS	IX	S	AI
IS	Compatible with	Compatible with	Compatible with	Compatible with
IX	Compatible with	Compatible with		Compatible with
S	Compatible with		Compatible with
X
AI	Compatible with	Compatible with

To sum up the following points:

Intent locks do not conflict with each other;
S locks are compatible with S/IS locks but conflict with other locks.
[Fixed] X lock conflicts with all other locks.
[Fixed] AI locks are only compatible with intent locks

The type of lock

According to the granularity of the Lock can be subdivided into table Lock and row Lock, row Lock according to the different scene can be further subdivided, Next Key Lock, Gap Lock Gap Lock, Record Lock and insert intention Gap Lock.

For example, a record Lock only locks the corresponding record, a gap Lock locks the interval between the record and the record, and a next-key Lock locks the record and the interval before the record. The locking ranges of different types of locks are shown in the following figure.

Compatibility matrices for different types of locks

	RECORD	GAP	NEXT-KEY	II GAP
RECORD		Compatible with		Compatible with
GAP	Compatible with	Compatible with	Compatible with	Compatible with
NEXT-KEY		Compatible with		Compatible with
II GAP	Compatible with			Compatible with

Where the first row represents the existing lock and the first column represents the lock to be added. Insert intention lock is special, so we first make a summary of insert intention lock, as follows:

Inserting an intent lock does not affect any other locks added to other transactions. In other words, one transaction has acquired the intent lock for insertion, which has no effect on other transactions.
Insert intent locks conflict with gap locks and next-key locks. That is, a transaction attempting to acquire an insert intent lock will block if another transaction has a gap lock or a next-key lock.

The rules for other types of locks are simpler:

[Fixed] Gap locks do not conflict with other locks (excluding insert intent locks);
A record lock conflicts with a record lock, a next-key lock conflicts with a next-key lock, and a record lock conflicts with a next-key lock.

GitHub Org_Hejianhui /JavaStudy GitHub Hejianhui /JavaStudy

In-depth understanding of MySQL lock types and locking principles

preface

Table locks

Row locks

Page locks

Shared/exclusive locks

Share Lock

usage

EXclusive Lock

usage

Optimistic locks and pessimistic locks

Pessimistic locking

Pessimistic lock specific process

Advantages and disadvantages of pessimistic locking

Optimistic locking

The advantages and disadvantages of optimistic locking

Intentional shared lock/intentional exclusive lock

Intention Shared Lock

Exclusive Lock

Record Lock

Gap Lock

Case 1: Gap lock with unique index

Only record locks (row locks) are used and no gap locks are created

Generate gap lock

conclusion

Case 2: Gap locks for normal indexes

Test 1

Test 2

conclusion

Next-key Locks

Insert Intention Locks

Auto-inc Locks

On the operation

On the lock

traditonal

consecutive

interleaved

conclusion

InnoDB lock features

The lock mode

Compatibility matrix for different mode locks

The type of lock

Compatibility matrices for different types of locks

Related Posts

Java based _08 | three second feature of object-oriented inheritance (the use of the keyword extends, the principle of inheritance, (with package) inherited access problems, the super call the superclass constructor)

What exactly is a B tree?

SSM Framework — Detailed integration tutorial (Spring+SpringMVC+MyBatis)