Detailed explanation of MySQL locks (table locks, row locks, shared locks, exclusive locks, gap locks)

In real life, a lock is a tool we use when we want to hide from the outside world. In computers, it is a mechanism for coordinating multiple processes or counties to access a resource concurrently. In a database, in addition to the contention for traditional computing resources (CPU, RAM, I/O, etc.), data is also a resource that is shared and accessed by many users. How to ensure the consistency and effectiveness of concurrent data access is a problem that all databases must solve. Lock conflicts are also an important factor affecting the concurrent access performance of databases. From this perspective, locks are particularly important for databases.

MySQL Locks

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms. Depending on the storage engine, the characteristics of locks in MySQL can be roughly summarized as follows:

Overhead, locking speed, deadlock, granularity, and concurrency performance

• Table lock: low overhead, fast locking; no deadlock; strong locking, high probability of lock conflict, lowest concurrency

• Row lock: high overhead, slow locking; deadlock may occur; small locking granularity, low probability of lock conflict, high concurrency

• Page lock: The cost and locking speed are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average

From the above characteristics, it is difficult to say in general which lock is the best. We can only say which lock is more suitable based on the characteristics of the specific application. Just from the lock perspective:

Table locks are more suitable for applications that are query-based and only update a small amount of data according to index conditions; row locks are more suitable for applications that have a large number of concurrent updates of a small amount of different data according to index conditions and concurrent queries. (PS: Since BDB has been replaced by InnoDB, we only discuss the issues of MyISAM table locks and InnoDB row locks)

MyISAM table locks

The MyISAM storage engine only supports table locks, which is the only lock type supported in the first few versions of MySQL. As applications' requirements for transaction integrity and concurrency continued to increase, MySQL began to develop a transaction-based storage engine. Later, the BDB storage engine that supports page locks and the InnoDB storage engine that supports row locks gradually appeared (InnoDB is actually a separate company and has now been acquired by Oracle). However, MyISAM table locks are still the most widely used lock type. This section will introduce the use of MyISAM table locks in detail.

Query table-level lock contention

Table lock contention on your system can be analyzed by examining the table_locks_waited and table_locks_immediate status variables:

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))

If the value of Table_locks_waited is relatively high, it means that there is serious table-level lock contention.

MySQL table-level lock mode

MySQL table-level locks have two modes: table shared read lock (Table Read Lock) and table exclusive write lock (Table Write Lock). The compatibility of lock modes is shown in the following table.

Table lock compatibility in MySQL

It can be seen that the read operation on the MyISAM table will not block other users' read requests for the same table, but will block write requests for the same table; the write operation on the MyISAM table will block other users' read and write operations on the same table; the read operation and write operation of the MyISAM table, as well as the write operations themselves, are serial! From the example shown in the following table, we can know that when a thread obtains a write lock on a table, only the thread holding the lock can update the table. Read and write operations of other threads will wait until the lock is released.

MyISAM storage engine write blocking read example

How to add table lock

Before executing a query statement (SELECT), MyISAM will automatically add a read lock to all tables involved. Before executing an update operation (UPDATE, DELETE, INSERT, etc.), it will automatically add a write lock to the tables involved. This process does not require user intervention. Therefore, users generally do not need to explicitly lock the MyISAM table directly using the LOCK TABLE command. In the examples in this book, explicit locking is mostly for convenience and is not required.

The purpose of explicitly locking the MyISAM table is to simulate transaction operations to a certain extent and achieve consistent reading of multiple tables at a certain point in time. For example, there is an order table orders, which records the total amount of each order, and there is also an order detail table order_detail, which records the subtotal amount of each product in each order. If we need to check whether the total amount of these two tables matches, we may need to execute the following two SQL statements:

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

At this time, if you do not lock the two tables first, incorrect results may occur because the order_detail table may have been changed during the execution of the first statement. Therefore, the correct way should be:

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

The following two points should be explained in particular.

• In the above example, the "local" option is added when LOCK TABLES. Its function is to allow other users to concurrently insert records at the end of the MyISAM table when the concurrent insertion conditions of the MyISAM table are met. The issue of concurrent insertion of MyISAM tables will be further introduced in the following chapters.

• When you use LOCK TABLES to explicitly lock a table, you must obtain locks on all tables involved at the same time, and MySQL does not support lock escalation. That is to say, after executing LOCK TABLES, you can only access the explicitly locked tables, and cannot access unlocked tables; at the same time, if a read lock is added, only query operations can be performed, and update operations cannot be performed. In fact, this is basically the case with automatic locking. MyISAM always obtains all the locks required for a SQL statement at once. This is why MyISAM tables are deadlock-free.

In the example shown in the following table, a session uses the LOCK TABLE command to add a read lock to the film_text table. This session can query records in the locked table, but updates or accesses to other tables will prompt errors. At the same time, another session can query records in the table, but updates will result in lock waits.

MyISAM storage engine read blocking write example

Note that when using LOCK TABLES, not only do you need to lock all the tables used at once, but you also need to lock the same table as many times as it appears in the SQL statement using the same alias in the SQL statement, otherwise an error will occur! An example is given below.

(1) Obtain a read lock on the actor table:

mysql> lock table actor read;
Query OK, 0 rows affected (0.00 sec)

(2) However, accessing through an alias will result in an error:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

(3) Aliases need to be locked separately:

mysql> lock table actor as a read, actor as b read;
Query OK, 0 rows affected (0.00 sec)

(4) The query by alias can be executed correctly:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
+------------+-----------+------------+-----------+
| first_name | last_name | first_name | last_name |
+------------+-----------+------------+-----------+
| Lisa | Tom | LISA | MONROE |
+------------+-----------+------------+-----------+

1 row in set (0.00 sec)

Concurrent Inserts

It was mentioned above that the reading and writing of MyISAM tables are serial, but this is generally speaking. Under certain conditions, MyISAM tables also support concurrent query and insert operations.

The MyISAM storage engine has a system variable concurrent_insert, which is specifically used to control its concurrent insertion behavior. Its value can be 0, 1, or 2.

• When concurrent_insert is set to 0, concurrent inserts are not allowed.

• When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, there are no deleted rows in the middle of the table), MyISAM allows one process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.

• When concurrent_insert is set to 2, concurrent insertion of records at the end of the table is allowed regardless of whether there are holes in the MyISAM table.

In the example shown in the following table, session_1 obtains the READ LOCAL lock of a table. The thread can query the table but cannot update it. The other thread (session_2) cannot delete or update the table but can perform concurrent insert operations on it. It is assumed that there is no hole in the table.

MyISAM storage engine read and write (INSERT) concurrent example

You can use the concurrent insert feature of the MyISAM storage engine to resolve lock contention for queries and inserts on the same table in your application. For example, setting the concurrent_insert system variable to 2 always allows concurrent insertions. At the same time, by periodically executing the OPTIMIZE TABLE statement during system idle periods, space fragments are defragmented and gaps created by deleted records are reclaimed. For a detailed introduction to the OPTIMIZE TABLE statement, see the section "Two simple and practical optimization methods" in Chapter 18.

MyISAM lock scheduling

As mentioned earlier, the read lock and write lock of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial. So, if one process requests a read lock on a MyISAM table and another process also requests a write lock on the same table, how does MySQL handle it? The answer is that the writing process acquires the lock first. Not only that, even if the read request arrives at the lock waiting queue first and the write request arrives later, the write lock will be inserted before the read lock request! This is because MySQL considers write requests to be generally more important than read requests. This is also the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, and they may be blocked forever. This situation can get really bad at times! Fortunately, we can adjust the scheduling behavior of MyISAM through some settings.

• By specifying the startup parameter low-priority-updates, the MyISAM engine gives priority to read requests by default.

• By executing the command SET LOW_PRIORITY_UPDATES=1, the priority of the update request issued by the connection is lowered.

• By specifying the LOW_PRIORITY attribute of the INSERT, UPDATE, or DELETE statement, you can lower the priority of the statement.

Although the above three methods are either update-first or query-first methods, they can still be used to solve the serious problem of read lock waiting in applications where queries are relatively important (such as user login systems).

In addition, MySQL also provides a compromise method to adjust read-write conflicts, that is, to set a suitable value for the system parameter max_write_lock_count. When the read lock of a table reaches this value, MySQL temporarily lowers the priority of the write request to give the read process a certain opportunity to obtain the lock.

The problems and solutions brought about by the write-priority scheduling mechanism have been discussed above. One more point needs to be emphasized here: some query operations that require a long time to run will also cause the write process to "starve"! Therefore, long-running query operations should be avoided as much as possible in the application. Do not always try to use a SELECT statement to solve the problem, because this seemingly clever SQL statement is often complex and takes a long time to execute. When possible, you can use intermediate tables and other measures to "decompose" the SQL statement so that each query step can be completed in a shorter time, thereby reducing lock conflicts. If complex queries are unavoidable, they should be scheduled to be executed during the database idle time. For example, some regular statistics can be scheduled to be executed at night.

InnoDB lock issue

There are two biggest differences between InnoDB and MyISAM: one is that it supports transactions (TRANSACTION); the other is the use of row-level locks. There are many differences between row-level locks and table-level locks. In addition, the introduction of transactions also brings some new problems. Below we first introduce some background knowledge, and then discuss the InnoDB lock problem in detail.

Background

1. Transaction and its ACID properties

A transaction is a logical processing unit consisting of a group of SQL statements. A transaction has the following four properties, which are usually referred to as the ACID properties of the transaction.

• Atomicity: A transaction is an atomic operation unit, and its modifications to data are either all executed or none of them are executed.

• Consistency: Data must remain in a consistent state when a transaction starts and completes. This means that all relevant data rules must be applied to the transaction modifications to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree indexes or doubly linked lists) must also be correct.

• Isolation: The database system provides certain isolation mechanisms to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations. This means that intermediate states during transaction processing are not visible to the outside world, and vice versa.

• Durable: After a transaction is completed, its changes to data are permanent and can be maintained even if a system failure occurs.

A bank transfer is a classic example of a transaction.

2. Problems with concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources and improve the transaction throughput of the database system, thereby supporting more users. However, concurrent transaction processing also brings some problems, mainly including the following situations.

• Lost Update: When two or more transactions select the same row and then update the row based on the initially selected values, the lost update problem occurs because each transaction is unaware of the existence of the other transactions - the last update overwrites the updates made by other transactions. For example, two editors make electronic copies of the same document. Each editor independently makes changes to his or her copy and then saves the changed copy, overwriting the original document. The editor who last saves his or her copy of changes overwrites the changes made by the other editor. This problem can be avoided if one editor cannot access the same file until another editor completes and commits the transaction.

• Dirty Reads: A transaction is modifying a record. Before the transaction is completed and committed, the data of the record is in an inconsistent state. At this time, another transaction also reads the same record. If it is not controlled, the second transaction reads the "dirty" data and performs further processing based on it, which will generate uncommitted data dependencies. This phenomenon is vividly called "dirty read".

• Non-Repeatable Reads: A transaction reads some data at a certain time after reading it, and then reads the data it read before, only to find that the data it read has changed or some records have been deleted! This phenomenon is called "non-repeatable read".

• Phantom Reads: When a transaction re-reads previously retrieved data using the same query criteria, only to find that other transactions have inserted new data that meets its query criteria, this phenomenon is called a "phantom read."

3. Transaction Isolation Level

Among the problems caused by concurrent transaction processing mentioned above , "update loss" should usually be completely avoided. However, preventing update loss cannot be solved by the database transaction controller alone. The application needs to add necessary locks to the data to be updated. Therefore, preventing update loss should be the responsibility of the application.

"Dirty reads", "non-repeatable reads" and "phantom reads" are actually database read consistency problems, which must be solved by the database providing a certain transaction isolation mechanism. The ways in which databases implement transaction isolation can basically be divided into the following two types.

• One is to lock the data before reading it to prevent other transactions from modifying the data.

• The other method is to generate a consistent data snapshot (Snapshot) at the data request time point through a certain mechanism without adding any locks, and use this snapshot to provide consistent reading at a certain level (statement level or transaction level). From the user's perspective, it seems that the database can provide multiple versions of the same data. Therefore, this technology is called MultiVersion Concurrency Control (MVCC or MCC), and is often called a multi-version database.

The stricter the transaction isolation of the database, the smaller the concurrency side effects, but the greater the price paid, because transaction isolation essentially makes transactions "serialized" to a certain extent, which is obviously contradictory to "concurrency". At the same time, different applications have different requirements for read consistency and transaction isolation. For example, many applications are not sensitive to "non-repeatable reads" and "phantom reads" and may be more concerned about the ability to access data concurrently.

In order to resolve the contradiction between "isolation" and "concurrency", ISO/ANSI SQL92 defines four transaction isolation levels. Each level has a different degree of isolation and allows different side effects. Applications can balance the contradiction between "isolation" and "concurrency" by selecting different isolation levels according to their business logic requirements. The following table gives a good summary of the characteristics of these four isolation levels.

Comparison of 4 isolation levels

Finally, it should be noted that each specific database does not necessarily fully implement the above four isolation levels. For example, Oracle only provides two standard isolation levels, Read committed and Serializable, and also provides its own defined Read only isolation level. In addition to supporting the above four isolation levels defined by ISO/ANSI SQL92, SQL Server also supports an isolation level called "snapshot", but strictly speaking it is a Serializable isolation level implemented with MVCC. MySQL supports all four isolation levels, but there are some peculiarities in its implementation. For example, MVCC consistent reads are used in some isolation levels, but not in others. These contents will be further introduced in the following chapters.

Get InnoDB row lock contention status

You can analyze the row lock contention on your system by checking the InnoDB_row_lock status variable:

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)

If lock contention is severe, such as high values ​​for InnoDB_row_lock_waits and InnoDB_row_lock_time_avg , you can set InnoDB Monitors to further observe the tables and data rows where lock conflicts occur and analyze the causes of lock contention.

The specific method is as follows:

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

Then you can use the following statement to view it:

mysql> Show innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
…
…
------------
TRANSACTIONS
------------
Trx id counter 0 117472192
Purge done for trx's n:o < 0 117472190 undo n:o < 0 0
History list length 17
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 117472185, not started, process no 11052, OS thread id 1158191456
MySQL thread id 200610, query id 291197 localhost root
---TRANSACTION 0 117472183, not started, process no 11052, OS thread id 1158723936
MySQL thread id 199285, query id 291199 localhost root
Show innodb status
…

The monitor can be stopped by issuing the following statement:

mysql> DROP TABLE innodb_monitor;
Query OK, 0 rows affected (0.05 sec)

After setting up the monitor, the display content of SHOW INNODB STATUS will include detailed information about the current lock wait, including the table name, lock type, and the status of the locked record, which is convenient for further analysis and problem determination. After turning on the monitor, the monitoring content will be recorded in the log every 15 seconds by default. If it is turned on for a long time, the .err file will become very large. Therefore, after confirming the cause of the problem, the user must remember to delete the monitoring table to turn off the monitor, or start the server by using the "--console" option to turn off writing log files.

InnoDB row lock mode and locking method

InnoDB implements the following two types of row locks.

• Shared lock (S): allows one transaction to read a row, preventing other transactions from obtaining exclusive locks on the same data set.

• Exclusive lock (X): allows transactions that obtain exclusive locks to update data, preventing other transactions from obtaining shared read locks and exclusive write locks on the same data set. In addition, in order to allow row locks and table locks to coexist and implement a multi-granularity lock mechanism, InnoDB also has two internally used intention locks (Intention Locks), both of which are table locks.

• Intentional shared lock (IS): The transaction intends to add a row shared lock to a data row. The transaction must first obtain the IS lock of the table before adding a shared lock to a data row.

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

The compatibility of the above lock modes is shown in the following table.

InnoDB row lock mode compatibility list

If the lock mode requested by a transaction is compatible with the current lock, InnoDB grants the requested lock to the transaction; otherwise, if the two are incompatible, the transaction waits for the lock to be released.

Intention locks are added automatically by InnoDB without user intervention. For UPDATE, DELETE, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved; for ordinary SELECT statements, InnoDB does not add any locks; transactions can explicitly add shared locks or exclusive locks to the record set through the following statements.

• Shared lock(s): SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE.

• Exclusive lock (X): SELECT * FROM table_name WHERE ... FOR UPDATE.

Use SELECT ... IN SHARE MODE to obtain a shared lock, which is mainly used to confirm whether a row of records exists when data dependencies are required, and to ensure that no one performs UPDATE or DELETE operations on this record. However, if the current transaction also needs to update the record, it is likely to cause a deadlock. For applications that need to update the row record after locking it, the SELECT... FOR UPDATE method should be used to obtain an exclusive lock.

In the example shown in the following table, SELECT ... IN SHARE MODE is used to lock and then update the record to see what happens. The actor_id field of the actor table is the primary key.

InnoDB storage engine shared lock example

When you use SELECT...FOR UPDATE to lock and then update records, the following situation occurs.

InnoDB storage engine exclusive lock example

InnoDB row lock implementation

InnoDB row locks are implemented by locking the index items on the index . This is different from MySQL and Oracle, which are implemented by locking the corresponding data rows in the data block. InnoDB's row lock implementation feature means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks!

In actual applications, special attention should be paid to this feature of InnoDB row locks, otherwise, a large number of lock conflicts may occur, thus affecting concurrency performance. The following are some practical examples to illustrate this.

(1) When querying without index conditions, InnoDB does use table locks instead of row locks.

In the following example, the tab_no_index table has no index at the beginning:

mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Example of using table locks when the InnoDB storage engine table does not use indexes

In the example shown in the table above, it seems that session_1 only adds an exclusive lock to one row, but when session_2 requests exclusive locks for other rows, a lock wait occurs! The reason is that without an index, InnoDB can only use table locks. When we add an index to it, InnoDB only locks the rows that meet the conditions, as shown in the following table.

Create the tab_with_index table with a normal index on the id field:

mysql> create table tab_with_index(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> alter table tab_with_index add index id(id);
Query OK, 4 rows affected (0.24 sec)
Records: 4 Duplicates: 0 Warnings: 0

Example of using row locks when using indexes for tables with the InnoDB storage engine

(2) Since MySQL row locks are locked against indexes, not records, lock conflicts will occur when accessing records in different rows using the same index key. Keep this in mind when designing your application.

In the example shown in the following table, the id field of the table tab_with_index has an index, but the name field does not have an index:

mysql> alter table tab_with_index drop index name;
Query OK, 4 rows affected (0.22 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into tab_with_index values(1,'4');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_with_index where id = 1;
+------+------+
| id | name |
+------+------+
| 1 | 1 |
| 1 | 4 |
+------+------+
2 rows in set (0.00 sec)

InnoDB storage engine blocking example using the same index key

(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, regardless of whether a primary key index, unique index, or ordinary index is used, InnoDB will use row locks to lock the data.

In the example shown in the following table, the id field of the table tab_with_index has a primary key index, and the name field has a normal index:

mysql> alter table tab_with_index add index name(name);
Query OK, 5 rows affected (0.23 sec)
Records: 5 Duplicates: 0 Warnings: 0

Blocking example of using different indexes for InnoDB storage engine tables

(4) Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL believes that a full table scan is more efficient, such as for some very small tables, it will not use the index. In this case, InnoDB will use a table lock instead of a row lock. Therefore, when analyzing lock conflicts, don't forget to check the SQL execution plan to confirm whether the index is actually used.

In the following example, the data type of the retrieved value is different from the index field. Although MySQL can perform data type conversion, it will not use the index, causing InnoDB to use a table lock. By using explain to check the execution plans of the two SQL statements, we can clearly see this.

In the example, the name field of the tab_with_index table has an index, but the name field is of varchar type. If the where condition does not compare with the varchar type, the name field will be converted to a new type and a full table scan will be performed.

mysql> alter table tab_no_index add index name(name);
Query OK, 4 rows affected (8.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> explain select * from tab_with_index where name = 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab_with_index
type: ALL
possible_keys: name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from tab_with_index where name = '1' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab_with_index
type: ref
possible_keys: name
key: name
key_len: 23
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

Gap lock (Next-Key lock)

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

For example, if there are only 101 records in the emp table, and the empid values ​​are 1, 2, ..., 100, 101 respectively, the following SQL:

Select * from emp where empid > 100 for update;

This is a range condition search. InnoDB will not only lock the records with an empid value of 101 that meet the conditions, but also lock the "gaps" where the empid value is greater than 101 (these records do not exist).

The purpose of InnoDB using gap locks is, on the one hand, to prevent phantom reads to meet the requirements of related isolation levels. For the above example, if gap locks are not used, if other transactions insert any records with empid greater than 100, then if this transaction executes the above statement again, phantom reads will occur; on the other hand, it is to meet the needs of recovery and replication. The impact of recovery and replication on the locking mechanism, as well as InnoDB's use of gap locks at different isolation levels, will be further introduced in subsequent chapters.

Obviously, when using range conditions to retrieve and lock records, the InnoDB locking mechanism will block concurrent insertion of key values ​​that meet the conditions, which often causes serious lock waits. Therefore, in actual application development, especially applications with a lot of concurrent insertions, we should try our best to optimize business logic, use equality conditions to access and update data, and avoid using range conditions.

It should also be noted that in addition to using gap locks when locking through range conditions, InnoDB will also use gap locks if an equality condition is used to request a lock on a non-existent record!

In the example shown in the following table, if there are only 101 records in the emp table, the values ​​of empid are 1, 2, ..., 100, 101 respectively.

InnoDB storage engine gap lock blocking example

The need for recovery and replication affects the InnoDB locking mechanism

MySQL uses BINLOG to record successful INSERT, UPDATE, DELETE and other SQL statements that update data, thereby realizing the recovery and master-slave replication of the MySQL database (see the introduction in the "Management" section of this book). The MySQL recovery mechanism (replication is actually continuous BINLOG-based recovery on the slave MySQL) has the following characteristics.

l First, MySQL recovery is at the SQL statement level, that is, re-executing the SQL statements in BINLOG. This is different from Oracle database, which is based on database file blocks.

l Second, MySQL's Binlog records transactions in the order they were submitted, and recovery is also performed in this order. This is also different from Oracle. Oracle restores data according to the system change number (SCN). At the beginning of each transaction, Oracle assigns a globally unique SCN. The order of SCNs is consistent with the time sequence of the transaction start.

From the above two points, we can see that MySQL's recovery mechanism requires that before a transaction is committed, other concurrent transactions cannot insert any records that meet their locking conditions, that is, phantom reads are not allowed. This exceeds the requirements of the ISO/ANSI SQL92 "repeatable read" isolation level and actually requires transactions to be serialized. This is also the reason why InnoDB uses gap locks in many cases. For example, when updating records with range conditions, InnoDB uses gap locks regardless of the Read Commited or Repeatable Read isolation level. However, this is not a requirement of the isolation level. The differences in InnoDB locking at different isolation levels will be introduced in the next section.

In addition, for SQL statements such as "insert into target_tab select * from source_tab where ..." and "create table new_tab ...select ... From source_tab where ...(CTAS)", the user does not perform any update operations on source_tab, but MySQL makes special processing for such SQL statements. Let’s first look at the example in the following table.

Example of CTAS operation to lock the original table

In the above example, we simply read the data in the source_tab table, which is equivalent to executing a normal SELECT statement and can be read with consistency. This is exactly what ORACLE does. It uses multi-version data implemented by MVCC technology to achieve consistent reading without adding any locks to source_tab. We know that InnoDB also implements multi-version data, and does not require any locks for ordinary SELECT consistent reads; but here InnoDB adds a shared lock to source_tab and does not use multi-version data consistent read technology!

Why does MySQL do this? The reason is to ensure the correctness of recovery and replication. If the lock is not added, if other transactions update source_tab during the execution of the above statement, it may cause errors in the data recovery results. To demonstrate this, let’s repeat the previous example. The difference is that before session_1 executes the transaction, the value of the system variable innodb_locks_unsafe_for_binlog is set to “on” (its default value is off). The specific results are shown in the following table.

An example of security issues caused by CTAS operation without locking the original table

As can be seen from the above, after setting the value of the system variable innodb_locks_unsafe_for_binlog to "on", InnoDB no longer locks source_tab, and the result is consistent with the application logic. However, if the content of BINLOG is analyzed:

......
SET TIMESTAMP=1169175130;
BEGIN;
# at 274
#070119 10:51:57 server id 1 end_log_pos 105 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1169175117;
update source_tab set name = '8' where name = '1';
# at 379
#070119 10:52:10 server id 1 end_log_pos 406 Xid = 5
COMMIT;
# at 406
#070119 10:52:14 server id 1 end_log_pos 474 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175134;
BEGIN;
# at 474
#070119 10:51:29 server id 1 end_log_pos 119 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1169175089;
insert into target_tab select d1,name from source_tab where name = '1';
# at 593
#070119 10:52:14 server id 1 end_log_pos 620 Xid = 7
COMMIT;
......

It can be found that in BINLOG, the update operation is located before INSERT...SELECT. If this BINLOG is used to restore the database, the restored result will not match the actual application logic; if it is copied, it will cause inconsistency between the master and slave databases!

From the above example, it is not difficult for us to understand why MySQL locks source_tab when processing "Insert into target_tab select * from source_tab where ..." and "create table new_tab ...select ... From source_tab where ..." instead of using multi-version data with minimal impact on concurrency to achieve consistent reading. It should also be noted that if the SELECT in the above statement is a range condition, InnoDB will also add a gap lock (Next-Lock) to the source table.

Therefore, INSERT...SELECT... and CREATE TABLE...SELECT... statements may prevent concurrent updates to the source table, causing waits for the source table lock. If the query is complex, it will cause serious performance problems, and we should try to avoid using it in the application. In fact, MySQL calls this kind of SQL non-deterministic SQL and does not recommend its use.

If you must use this SQL to implement business logic in your application and do not want to affect concurrent updates of the source table, you can take the following two measures:

• One is to adopt the approach in the above example and set the value of innodb_locks_unsafe_for_binlog to "on" to force MySQL to use multi-version data consistency reading. But the price you pay is that you may not be able to correctly restore or copy data using binlog, so this method is not recommended.

• The second method is to use the combination of "select * from source_tab ... Into outfile" and "load data infile ..." to achieve it indirectly. In this way, MySQL will not lock source_tab.

Differences in consistent reads and locks between different isolation levels of InnoDB

As mentioned earlier, locks and multi-version data are the means by which InnoDB implements consistent reads and ISO/ANSI SQL92 isolation levels. Therefore, under different isolation levels, the consistent read strategy and required locks adopted by InnoDB when processing SQL are different. At the same time, the characteristics of data recovery and replication mechanisms also have a great impact on the consistent reading strategies and locking strategies of some SQLs. These features are summarized in the following table for the convenience of readers.

Comparison of locks in different SQL statements at different isolation levels in the InnoDB storage engine

From the above table, we can see that for many SQL statements, the higher the isolation level, the stricter the lock that InnoDB adds to the record set (especially when using range conditions), the higher the possibility of lock conflicts, and the greater the impact on concurrent transaction processing performance. Therefore, we should try to use a lower isolation level in our application to reduce the chance of lock contention. In fact, by optimizing transaction logic, it is sufficient for most applications to use the Read Commited isolation level. For some transactions that do require a higher isolation level, you can dynamically change the isolation level by executing SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ or SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE in the program to meet the needs.

When to use table locks

For InnoDB tables, row-level locks should be used in most cases, because transactions and row locks are often the reasons why we choose InnoDB tables. However, in some special transactions, you may also consider using table-level locks.

• The first situation is: the transaction needs to update most or all of the data, and the table is relatively large. If the default row lock is used, not only will the transaction execution efficiency be low, but it may also cause long lock waits and lock conflicts for other transactions. In this case, you can consider using table locks to speed up the execution of the transaction.

• The second situation is that the transaction involves multiple tables, which is relatively complex and may cause deadlocks and rollback of a large number of transactions. In this case, you can also consider locking the tables involved in the transaction at one time to avoid deadlock and reduce the database overhead caused by transaction rollback.

Of course, there should not be too many of these two types of transactions in the application, otherwise, you should consider using MyISAM tables.

In InnoDB, pay attention to the following two points when using table locks.

(1) Although LOCK TABLES can be used to add table-level locks to InnoDB, it must be noted that table locks are not managed by the InnoDB storage engine layer, but by its upper layer, the MySQL Server. Only when autocommit=0 and innodb_table_locks=1 (default settings) can the InnoDB layer know about the table locks added by MySQL, and the MySQL Server can also perceive the row locks added by InnoDB. In this case, InnoDB can automatically identify deadlocks involving table-level locks; otherwise, InnoDB will not be able to automatically detect and handle such deadlocks. We will continue discussing deadlock in the next section.

(2) When using LOCK TABLES to lock an InnoDB table, be careful to set AUTOCOMMIT to 0, otherwise MySQL will not lock the table. Do not use UNLOCK TABLES to release the table lock before the transaction ends, because UNLOCK TABLES will implicitly commit the transaction. COMMIT or ROLLBACK cannot release the table-level lock added by LOCK TABLES. UNLOCK TABLES must be used to release the table lock. The correct way is as follows:

For example, if you need to write to table t1 and read from table t, you can do it as follows:

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

About Deadlock

As mentioned above, MyISAM table locks are deadlock free. This is because MyISAM always obtains all the required locks at once, either all of them are satisfied or waiting, so there will be no deadlock. But in InnoDB, except for transactions consisting of a single SQL statement, locks are acquired step by step, which makes deadlock possible in InnoDB. The following is an example of a deadlock.

Deadlock example in InnoDB storage engine

In the above example, both transactions need to obtain the exclusive lock held by the other party to continue to complete the transaction. This circular lock wait is a typical deadlock.

After a deadlock occurs, InnoDB can generally detect it automatically and make one transaction release the lock and roll back, while the other transaction acquires the lock and continues to complete the transaction. However, when external locks or table locks are involved, InnoDB cannot automatically detect deadlocks. This needs to be solved by setting the lock wait timeout parameter innodb_lock_wait_timeout. It should be noted that this parameter is not only used to solve the deadlock problem. In the case of high concurrent access, if a large number of transactions are suspended because they cannot obtain the required locks immediately, it will occupy a large amount of computer resources, cause serious performance problems, and even drag down the database. We can avoid this situation by setting an appropriate lock wait timeout threshold.

Generally speaking, deadlocks are a problem of application design. Most deadlocks can be avoided by adjusting business processes, database object design, transaction size, and SQL statements for accessing the database. The following are some common methods to avoid deadlock through examples.

(1) In an application, if different programs access multiple tables concurrently, you should try to agree to access the tables in the same order. This can greatly reduce the chance of deadlock. In the following example, since the two sessions access the two tables in different orders, the chance of deadlock is very high! But if access is done in the same order, deadlock can be avoided.

Deadlock example caused by table order in InnoDB storage engine

(2) When the program processes data in batches, if the data is sorted in advance to ensure that each thread processes records in a fixed order, the possibility of deadlock can be greatly reduced.

An example of deadlock caused by inconsistent table data operation order in the InnoDB storage engine

(3) In a transaction, if you want to update a record, you should directly apply for a lock of a sufficient level, that is, an exclusive lock, rather than applying for a shared lock first and then applying for an exclusive lock when updating. This is because when a user applies for an exclusive lock, other transactions may have already obtained a shared lock for the same record, causing a lock conflict or even a deadlock.

(4) As mentioned earlier, at the REPEATABLE-READ isolation level, if two threads simultaneously use SELECT...FOR UPDATE to apply exclusive locks to records with the same condition, both threads will succeed in applying the locks if no records meet the condition. The program finds that the record does not exist yet, so it tries to insert a new record. If two threads do this, a deadlock will occur. In this case, changing the isolation level to READ COMMITTED can avoid the problem, as shown below.

Deadlock example 1 caused by isolation level in InnoDB storage engine

(5) When the isolation level is READ COMMITTED, if both threads first execute SELECT...FOR UPDATE, they determine whether there are records that meet the conditions. If not, they insert the records. At this time, only one thread can insert successfully, and the other thread will wait for the lock. When the first thread submits, the second thread will make an error due to the primary key, but although this thread makes an error, it will obtain an exclusive lock! At this time, if a third thread applies for an exclusive lock again, a deadlock will occur.

In this case, you can directly perform the insert operation and then catch the primary key duplicate exception, or when encountering a primary key duplicate error, always execute ROLLBACK to release the acquired exclusive lock, as shown below.

Deadlock example 2 caused by isolation level in InnoDB storage engine

Although deadlock can be greatly reduced through the design and SQL optimization measures introduced above, it is difficult to avoid deadlock completely. Therefore, it is a good programming habit to always catch and handle deadlock exceptions in program design.

If a deadlock occurs, you can use the SHOW INNODB STATUS command to determine the cause of the last deadlock. The returned results include detailed information about the deadlock-related transactions, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, the locks it is waiting for, and the transactions that have been rolled back. Based on this, we can analyze the causes of deadlock and improvement measures. The following is a sample of SHOW INNODB STATUS output:

mysql> show innodb status \G
…….
------------------------
LATEST DETECTED DEADLOCK
------------------------
070710 14:05:16
*** (1) TRANSACTION:
TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 7521657, query id 673468054 localhost root update
insert into country (country_id,country) values(110,'Test')
………
*** (2) TRANSACTION:
TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 7521664, query id 673468058 localhost root statistics
select first_name,last_name from actor where actor_id = 1 for update
*** (2) HOLDS THE LOCK(S):
………
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
………
*** WE ROLL BACK TRANSACTION (1)
…

This article comprehensively explains the detailed usage of Mysql table locks, row locks, shared locks, exclusive locks, and gap locks. I hope it will be helpful to everyone.