• Database lock Knowledge (INNODB)
    • Library lock
    • Table locks
      • MDL lock
      • Intent locks
      • The efficiency of online DDL
      • Lock the upgrade and downgrade mechanism
    • Row locks
      • Four levels of isolation
      • Classification of row locks
      • Record Locks
      • Gap Lock
      • Next Key Lock
    • AUTO-INC Locking

    Articles have been included in my warehouse:Java learning Notes and free books to share

Database lock Knowledge (INNODB)

Library lock

There are two main types of library locks:

  • Flush tables with Read Lock (FTWRL) : Sets the database to read-only state. When the client is abnormally disconnected, the database lock is automatically released. The database lock is officially recommended.
  • Set the global global variable, that isset global readonly=true, also set the database to read-only state,However, no matter when, the database will never release the lock, even if the client is abnormally disconnected.

Flush Tables with Read Lock provides a more secure exception handling mechanism, so it is recommended to use Flush tables with Read Lock instead of modifying global variables.

Table locks

Convention: For convenience, this article classifies the SELECT statement as an MDL statement.

MDL lock

MDL lock is a type of table lock, also known as metadata lock. Metadata lock is a lock at the server layer. Almost all MYSQL engines provide table-level lock.

When we perform DDL statements, such as create table, modify the table data statements, we all need to form the lock to prevent concurrent problems between DDL statements, only S and S lock is Shared, the rest are mutually exclusive lock, the lock is not we need to show the application, when we perform DDL statements automatically lock application.

Of course we can also display the application form lock:

  • LOCK TABLE table_name READ;Locking a table with a read lock blocks other transactions that modify the table data and share the read table data.
  • LOCK TABLE table_name WRITE;Using a write lock lock table blocks other transaction reads and writes.

MDL lock is mainly used to solve the problem of concurrency between multiple DDL statements, but in addition to DDL and DDL problems, DDL and DML statements can also occur concurrency problems, so there is an implicit lock mode under INNODB.

Intent locks

In fact, to resolve conflicts between DDL and DML, INNODB also implicitly assigns table-level locks to each DML statement, without our explicit specification.

To see why the database does this, let’s assume that transaction A executes A DDL statement ALTER TABLE test DROP COLUMN ID; Transaction B is executing two DML statements SELECT * FROM. If you are familiar with databases, you should quickly notice that there are some concurrency issues:

A transaction Transaction B
BEGIN BEGIN
SELECT * FROM test;
. ALTER TABLE test DROP COLUMN id;
SELECT * FROM test;
COMMIT COMMIT

This creates conflict phenomenon, transaction A do not match the two query execution, has violated the consistency of the transaction, and in order to solve this problem, the MYSQL intent locks IS introduced, this kind of lock can be divided into official intent Shared lock lock (IS) and intent exclusive (IX) lock lock, intent locks IS produced by DML operations, please pay attention to distinguish and mentioned the S lock and X lock, An intentional shared lock indicates that a row on the current table holds a row shared lock (distinct table S lock), and an intentional exclusive lock indicates that a row on the current table holds a row exclusive lock (distinct table X lock).

The type of the intent lock is determined by the type of the row lock. For example, the SELECT statement applies for the row share lock as well as the intent share lock, and the UPDATE statement applies for the intent exclusive lock. DML statements in the same table do not conflict, which means the intent lock is compatible. Note that intent locks are generated by DML statements, and DDL statements do not apply for intent locks.

As mentioned above, the DDL statement requests only the normal X or S lock, but it must wait for the IX or IS lock to be released according to the rule.

Table lock compatibility rules are shown below:

Transaction B must wait for transaction A to commit before executing:

A transaction Transaction B
BEGIN BEGIN
SELECT * FROM test;(Apply IX lock)
. ALTER TABLE test DROP COLUMN id;(Apply X lock, need to wait IX release)
SELECT * FROM test; (heavy)
COMMIT(Release IX lock) COMMIT(Release X lock)

This implicit locking was introduced after MYSQL5.5. In previous versions, performing DML operations did not lock tables, so performing DDL operations required not only requesting an X lock, but also traversing each row in the table to determine if a row lock existed, and abandoning the operation if it did. As a result, online DDL was not supported in previous versions, and in order to perform DDL operations you had to stop all activity on the table. In addition, performing DDL operations required traversing all rows, which was very inefficient.

With this implicit MDL lock, the conflict between DML and DDL operations is resolved, and online DDL becomes possible without iterating through all rows, just applying for the lock.

Therefore, this implicit table lock resolves the conflict between DML and DDL operations, enabling the database to support online DDL while increasing the efficiency of DDL execution.

Note an inclusion relationship where IX, IS, X, and S locks are MDL locks.

The efficiency of online DDL

Although online DDL operations became possible with the introduction of implicit MDL locking after MYSQL5.5, we have to think about its efficiency, considering the following:

There are three transactions. The first transaction executes a DQL statement and applies for an IS lock. The second transaction executes the DDL statement and applies for the X lock. The X lock IS exclusive, so it must wait for the IS lock of transaction 1 to be released. Transaction 2 IS blocked. Transaction 3 also executes DQL statements, but because the write lock has a higher priority than the read lock, transaction 3 has to be placed behind transaction 2, and transaction 3 is blocked (not only in databases, but in most scenarios). If N DQL statements follow, all N statements will be blocked, whereas without transaction 2, since reads are shared, none of the transactions will be blocked, making online DDL extremely inefficient overall.

This phenomenon is mainly caused by the use of implicit MDL lock and write lock first principle, so it is difficult to cure this phenomenon, we can only alleviate it, after MYSQL5.6 introduced the lock upgrade and downgrade mechanism.

Lock the upgrade and downgrade mechanism

In the above example, all transactions after transaction 2 must wait for transaction 2 to complete. Transaction 2 is a DDL operation. DDL operations involve file reads and writes, write REDO logs and initiate disk I/OS. MYSQL is trying to speed up DDL operations to reduce the wait for subsequent transactions, but DDL operations are already difficult to change, MYSQL has come up with a curve to save the country — let it temporarily give up holding write locks!

The specific process is as follows:

  1. Transaction start, application level write lock;
  2. Degraded to table-level read lock, which prevents subsequent DQL operations from being blocked (DML is still blocked);
  3. Perform specific changes.
  4. Upgrade to write lock;
  5. Transaction commit, release lock;

When DDL transactions are degraded by write locks, subsequent DQL operations can run, increasing efficiency.

When a transaction starts, DDL operations are degraded from write locks to read locks. Since read locks and write locks are excluded, DDL changes table data without any other write operations, avoiding concurrency problems. When a transaction is committed, the read lock is upgraded to a write lock, which ensures that no other read operations are performed at the same time, avoiding read/write inconsistency.

However, if there are so many readers that the lock cannot be upgraded from a read lock to a write lock, there may be a problem of starving the DDL operation, which is a disadvantage for improving performance.

Row locks

Row locking is a more granular form of locking. In MYSQL, only INNODB performs row locking. Other engines do not support row locking.

INNODB implements two standard row-level locks (the difference between table lock S and X lock, here is row lock!). :

  • A shared lock (S lock) allows a transaction to read a row of data.
  • Exclusive locks (X locks) that allow a transaction to modify or delete a row of data.

Row locks are implemented based on indexes in INNODB. If an index is not hit, any operation will match the query for the full table. Row locks will degenerate into table locks, and the database will lock the table first before performing the full table search.

So be aware that all row locks are on indexes.

Four levels of isolation

  1. Read Uncommitted. That is, transactions can read data that has not yet been committed by other transactions, and transactions are completely transparent, where even dirty reads are unavoidable.
  2. Read Committed. This isolation level, the transaction can be read by MVVC data without waiting for the release of the X lock, but the transaction always read the latest version of the record, such as transaction is modify A row, A transaction B read twice, first read found A is modified, the data is locked, so read on A version of the data, A transaction at this time and submit the changes, Transaction B starts the second read, which always tries to read the latest version of the data. Therefore, transaction B reads the data modified by transaction A for the second time. The two reads of transaction B are inconsistent, causing an unrepeatable read problem.
  3. Repeatable read. INNDOB under the default level, similar to read committed, the only difference is that this isolation level, within a transaction, will always read the same version of the record, read what, read what, do not occur repeatable read problem. Initially, there was a magic read problem, which was solved by the introduction of the Next Key Lock.
  4. Serializable. Disable MVVC, no problem, but low efficiency.
Isolation level Dirty read Unrepeatable read Phantom Read
Read uncommitted may may may
Read Committed Can’t be may may
Repeatable read Can’t be Can’t be Can’t be
Serializable Can’t be Can’t be Can’t be

Classification of row locks

There are three types of row locks under INNODB, using different locks according to different conditions.

SQL > create table test;

drop table if exists test;
create table test(
	a int,
    b int,
    primary key(a),
    key(b)
)engine=InnoDB charset=utf8;

insert into test select 1.1;
insert into test select 3.1;
insert into test select 5.3;
insert into test select 7.6;
insert into test select 10.8;
Copy the code
A (Primary index) B (General index)
1 1
3 1
5 3
7 6
10 8

Record Locks

A record lock locks rows on a unique index. Note that the record is locked and not the index is locked, which means you cannot bypass the index to access the record.

When does a row record lock take effect? The next key lock is degraded to a row record lock only when the query column is a unique index equivalent query (i.e., where XXX = XXX).

Why should a row record lock be used when the query column is the only indexed equivalent query? Actually very simple, because the only index column corresponding to the only rows, only when we perform equivalent query, has ensured that we will only access this one row, so the record locking, prevented other operations cannot affect the records, and insert the operation of the new record will be rejected due to primary key conflict, phantom reads the question also won’t produce, concurrent to ensure safety.

Note that MYSQL uses the next key lock by default, and only demots to a row record lock if the condition is met. The impulse condition of using Record Lock is that the query Record is unique.

Isn’t it possible to use a row record lock under other conditions? The answer is no! Any other condition, we cannot satisfy the important condition of row record locking.

If it is not an equivalent query, then it must have multiple results. At the RR level, let’s look at A range query, considering transaction A and transaction B:

A transaction Transaction B
BEGIN; BEGIN;
SELECT * FROM test WHERE id >= 1 FOR UPDATE; (Lock X to prevent snapshot reading) .
. INSERT INTO test SELECT 101, 5;
SELECT * FROM test WHERE id >= 1 FOR UPDATE; .
COMMIT; COMMIT;

Imagine that if only one record is locked, transaction A will read different results twice, and the second read will have one more record, namely phantom read! Therefore, we have to lock in a range.

Consider an equivalent query under a non-unique index, and consider why row locks cannot be added in this case.

SELECT * FROM test WHERE b = 1 FOR UPDATE; SELECT * FROM test WHERE b = 1 FOR UPDATE; , the statement is corresponding to two records, line record lock can only lock a record, and another record can be modified at will, and can add other records, produce magic read! This is conflicting and therefore cannot be used for row record locking. Please understand again that locking records data and not lock condition values.

Gap Lock

A gap lock locks a range, but does not lock the record itself, that is, the condition value of the lock rather than the record data. This is the opposite of a row record lock. We will wait until we study the temporary key lock to say when to use a gap lock, because the gap lock is degraded by the temporary key lock when the condition is met.

Next Key Lock

Temporary and gap locks only work at RR isolation levels to solve illusory problems, while RC isolation does not solve even unrepeatable reads, let alone illusory problems.

Temporary key lock is also a range lock, but different from gap lock, temporary lock not only locks a range, but also locks the record itself. The locked record itself adopts row record lock, which is based on a unique index. It can be regarded as a combination of gap lock and row record lock.

The Next Key Locking algorithm is adopted to implement left-open and right-closed Locking on a range. The closed range means that the record is also locked.

Key lock rules

  1. For the upper interval and the next interval of the non-unique index query, the row record lock is applied to the value on the right of the interval to block the row record of the unique index corresponding to the non-unique index. In this case, the interval complies with the principle of open on left and closed on right.

  2. For values on the right side of the range, if they are not in the query range, the range is downgraded to a gap lock and row records are no longer blocked.

  3. If it is an equivalent query under a unique index, it is degraded to a row record lock.

Let’s take a look at a few examples to understand key adjacency locking and why phantom reading can be avoided.

Let’s first look at equivalent searches under normal indexes, where we simulate concurrency by enabling multiple terminals.

Suppose transaction A executes the following statement:

BEGIN; 
SELECT * FROM test WHERE b = 1 FOR UPDATE; // Do not commit, simulate concurrency.Copy the code

With the other terminal open, transaction B executes the following statement:

BEGIN;
SELECT * FROM test WHERE a = 3 FOR UPDATE;
SELECT * FROM test WHERE a = 5 FOR UPDATE;
INSERT INTO test SELECT - 1.- 1;
Copy the code

What are the results? SELECT * FROM test WHERE a = 7 FOR UPDATE; Outside normal execution, the other two sentences are blocked.

SELECT * FROM test WHERE b = 1 FOR UPDATE; According to rule 1, we lock the left and right interval of normal index B, where B is locked between (minus infinity, 1] and (1, 3]; According to rule 2, since b=3 is inconsistent with our query b=1, the right interval degrades, where the lock ranges are (minus infinity, 1] and (1, 3);

SELECT * FROM test WHERE a = 3 FOR UPDATE; SQL > select * from a where a= 1; SQL > select * from b where b = 1; SQL > select * from a where b = 1;

SELECT * FROM test WHERE a = 5 FOR UPDATE; Statement query record a = 5, because the right interval is degraded to gap lock, b = 3 is not locked record, that is, the record is locked, the query is successful.

INSERT INTO test SELECT -1, -1; Statement inserts a record, but because b has a gap lock at (minus infinity, 1) (equal to 1 is a row record lock), insert record b=-1 is in range, locked, blocked.

You might say, well, what if I insert b = 1? INSERT INTO test SELECT -1, 1; Since a gap lock locks (minus infinity, 1) and all records with b equal to 1 are locked by row records, it looks like there is no problem inserting a statement with b = 1, resulting in a phantom read.

But in fact, it is still locked by gap lock. To understand this, it must be explained from the level of B+ tree. Since both sides of B =1 are locked, and the left and right sections must be used to locate leaf nodes in the insertion algorithm, the insertion is blocked, thus avoiding phantom reading.

Why does an equivalent query need a range lock? As explained earlier, a non-unique index equivalent query queries multiple statements, and a row record lock can only lock one. If each row has an upstream record lock, it is inefficient, so a range lock is required.

Similarly, range statements follow the rules of immediate key locking, such as select * from test where b >= 1; , the range of the final lock is (negative infinity, 1] & (1, positive infinity);

A transaction Transaction B
BEGIN; BEGIN;
SELECT * FROM test WHERE id >= 1 FOR UPDATE; (Upper range lock) .
. INSERT INTO test SELECT 101, 5; (b = 5 falls into range, blocking wait)
SELECT * FROM test WHERE id >= 1 FOR UPDATE; .
COMMIT; COMMIT;

At this point the illusory phenomenon no longer occurs.

AUTO-INC Locking

Self-growth lock. In the InnoDB engine, each table maintains a table-level self-growth counter. When inserting a table, the following command is used to obtain the current self-growth value.

SELECT MAX(auto_inc_col)  FROM user FOR UPDATE;
Copy the code

The insert adds one to this base to get the auto-growing ID to be inserted, and then sets the ID within a transaction.

To improve insert performance, self-growing locks are not released until the transaction commits, but immediately after the related insert SQL statement completes, which also results in some transaction rollback with discontinuous ids.

Since self-locking will apply for write locks, the performance of the database is still reduced without waiting until the end of the transaction. After version 5.1.2, InnoDB supports the mutex to realize self-growth. Through the mutex, the counters in memory can be accumulated, which is faster than auto-Inc Locking.