MySQL Locks (Table Locks, Row Locks, Shared Locks, Exclusive Locks, Gap Locks) Use Details

Time:2019-7-2

Lock, in real life, is a tool we want to hide from the outside world. In the computer, it is a mechanism to coordinate multiple processes or counties to access a resource concurrently. In the database, besides the contention of traditional computing resources (CPU, RAM, I/O, etc.), data is also a resource for many users to share and access. How to ensure the consistency and validity of concurrent access to data is a problem that all databases must solve. The conflict of locks is also an important factor affecting the performance of concurrent access to databases. From this point of view, locks are particularly important for databases.

MySQL lock

Compared with other databases, MySQL’s locking mechanism is relatively simple, and the most notable feature is that different storage engines support different locking mechanisms. According to different storage engines, the characteristics of locks in MySQL can be roughly summarized as follows:

Row lock Watch lock Page lock
MyISAM
BDB
InnoDB

Overhead, lock speed, deadlock, granularity, concurrency performance

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

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

  • Page Locks: Overhead and locking speed are between table locks and row locks; deadlocks occur; locking granularity is between table locks and row locks, and concurrency is general.

From the above characteristics of courseware, it is difficult to say which kind of lock is the best in general, only according to the characteristics of specific applications, which kind of lock is more appropriate. From the point of view of lock only:

Table locks are more suitable for query-based applications, with only a small number of data updates according to index conditions; row locks are more suitable for applications with a large number of data updates concurrently according to index conditions, and concurrent queries at the same time. PS: Since BDB has been replaced by InnoDB, we only discuss MyISAM table locks and InnoDB row locks.

MyISAM table lock

The MyISAM storage engine only supports table locks, which is the only type of lock supported in the first few versions of MySQL. With the continuous improvement of transaction integrity and concurrency requirements of applications, MySQL began to develop transaction-based storage engine. Later, BDB storage engine supporting page locks and InnoDB storage engine supporting row locks emerged slowly (actually InnoDB is a separate company and is now being acquired by Oracle). But MyISAM’s watch lock is still the most widely used type of lock. This section describes in detail the use of MyISAM table locks.

Query table level lock contention

Table lock contention on the system can be analyzed by checking table_locks_waited and table_locks_immediate state 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 indicates that there is a serious table-level lock contention.

Lock Mode of MySQL Table Level Locks

MySQL’s table level locks have two modes: Table Read Lock and Table Write Lock. The compatibility of lock mode is shown in the following table.

Table Lock Compatibility in MySQL

Request lock mode

Compatibility

Current lock mode

None Read lock Write lock
Read lock yes yes no
Write lock yes no no

It can be seen that the reading operation of MyISAM tables will not block other users’reading requests for the same table, but will block the writing requests for the same table; the writing operation of MyISAM tables will block other users’ reading and writing operations for the same table; the reading operation and writing operation of MyISAM tables are serial, as well as the writing operation! As you can see from the example shown in the following table, when a thread acquires a write lock for a table, only the thread holding the lock can update the table. The read and write operations of other threads wait until the lock is released.

Write Blocking Read Example of MyISAM Storage Engine

session_1 session_2

WRITE Lock to Get Table File_text

mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)

The current session can perform queries, updates, and inserts on the locked table:

mysql> select film_id,title from film_text where film_id = 1001;
+———+————-+
| film_id | title       |
+———+————-+
| 1001    | Update Test |
+———+————-+
1 row in set (0.00 sec)
mysql> insert into film_text (film_id,title) values(1003,’Test’);
Query OK, 1 row affected (0.00 sec)
mysql> update film_text set title = ‘Test’ where film_id = 1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Queries from other sessions to locked tables are blocked and need to wait for the lock to be released:

mysql> select film_id,title from film_text where film_id = 1001;

wait for

Release lock:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

wait for

Session 2 gets the lock and the query returns:

mysql> select film_id,title from film_text where film_id = 1001;
+———+——-+
| film_id | title |
+———+——-+
| 1001    | Test  |
+———+——-+
1 row in set (57.59 sec)

How to add table lock

Before executing SELECT, MyISAM automatically adds read locks to all tables involved. Before updating operations (UPDATE, DELETE, INSERT, etc.), it automatically adds write locks to the tables involved. This process does not require user intervention. Therefore, users generally do not need to explicitly add LOCK TABLE commands to MyISAM tables directly. Lock. In the examples in this book, explicit locking is basically for convenience, not necessarily for convenience.

Locking MyISAM tables is generally used to simulate transaction operations to a certain extent and to achieve consistent reading of multiple tables at a certain point in time. For example, there is an order form orders, which records the total amount of each order and an order detail table order_detail, which records the subtotal of the total amount of each product for each order. Assuming that we need to check whether the sum of the two tables is consistent, we may need to execute the following two SQLs:


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

At this point, if you don’t lock the two tables first, you may get the wrong result, because the order_detail table may have changed during the execution of the first statement. Therefore, the correct method 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 highlighted.

  • The example above adds the “local” option to LOCK TABLES. Its function is to allow other users to insert records concurrently at the end of the table when the condition of concurrent insertion of MyISAM publications is satisfied. The concurrent insertion of MyISAM publications will be further discussed in the following chapters.

  • When explicitly adding table locks to tables with LOCK TABLES, all locks involving tables must be acquired simultaneously, and MySQL does not support lock escalation. That is to say, after LOCK TABLES is executed, only the explicitly locked tables can be accessed, and the unlocked tables can not be accessed; at the same time, if the read lock is added, only the query operation can be executed, but not the update operation. In fact, this is basically the case with automatic locking. MyISAM always gets all the locks needed by the SQL statement at one time. This is why MyISAM tables do not have Deadlock Free.

In the example shown in the following table, a session uses the LOCK TABLE command to add a read lock to the table film_text. This session can query the records in the locked table, but updating or accessing other tables will prompt errors; at the same time, another session can query the records in the table, but the update will result in lock waiting.

Read Blocking Write Example of MyISAM Storage Engine

session_1 session_2

READ Lock to Get Table File_text

mysql> lock table film_text write;
Query OK, 0 rows affected (0.00 sec)

The current session can query the table record

mysql> select film_id,title from film_text where film_id = 1001;
+———+——————+
| film_id | title            |
+———+——————+
| 1001    | ACADEMY DINOSAUR |
+———+——————+
1 row in set (0.00 sec)

Other sessions can also query the records of the table

mysql> select film_id,title from film_text where film_id = 1001;
+———+——————+
| film_id | title            |
+———+——————+
| 1001    | ACADEMY DINOSAUR |
+———+——————+
1 row in set (0.00 sec)

Current session cannot query unlocked tables

mysql> select film_id,title from film where film_id = 1001;
ERROR 1100 (HY000): Table ‘film’ was not locked with LOCK TABLES

Other sessions can query or update unlocked tables

mysql> select film_id,title from film where film_id = 1001;
+———+—————+
| film_id | title         |
+———+—————+
| 1001    | update record |
+———+—————+
1 row in set (0.00 sec)
mysql> update film set title = ‘Test’ where film_id = 1001;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Inserting or updating locked tables in the current session will prompt errors:

mysql> insert into film_text (film_id,title) values(1002,’Test’);
ERROR 1099 (HY000): Table ‘film_text’ was locked with a READ lock and can’t be updated
mysql> update film_text set title = ‘Test’ where film_id = 1001;
ERROR 1099 (HY000): Table ‘film_text’ was locked with a READ lock and can’t be updated

Other session update locking tables wait for locks to be acquired:

mysql> update film_text set title = ‘Test’ where film_id = 1001;

wait for

Release lock

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

wait for

Session obtains the lock and the update operation completes:

mysql> update film_text set title = ‘Test’ where film_id = 1001;
Query OK, 1 row affected (1 min 0.71 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Note that when using LOCK TABLES, you not only need to lock all tables once, but also how many times the same table appears in the SQL statement, you need to lock it by the same alias as as in the SQL statement, otherwise you will make mistakes! Examples are given below.

(1) Obtain read locks for actor tables:


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

(2) But an alias access will prompt errors:


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) Queries based on aliases can be correctly executed:


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

As mentioned above, the reading and writing of MyISAM tables are serial, but in general. Under certain conditions, MyISAM tables also support concurrent query and insert operations.

MyISAM storage engine has a system variable concurrent_insert, which is used to control the concurrent insert behavior. Its values can be 0, 1 or 2, respectively.

  • Concurrent inserts are not allowed when concurrent_insert is set to 0.

  • When concurrent_insert is set to 1, if there are no holes in the MyISAM table (that is, rows not deleted 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 MyISAM table is allowed regardless of whether there is a hole in the MyISAM table.

In the example shown in the following table, session_1 obtains a READ LOCAL lock for a table, which can query the table, but cannot update the table; other threads (session_2), although unable to delete and update the table, can insert the table concurrently, assuming that There is no hole in the middle of the table.

MyISAM Storage Engine Read-Write (INSERT) Concurrent Example

session_1 session_2

READ LOCAL Lock to Get Table Fil_text

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

The current session cannot update or insert the lock table:

mysql> insert into film_text (film_id,title) values(1002,’Test’);
ERROR 1099 (HY000): Table ‘film_text’ was locked with a READ lock and can’t be updated
mysql> update film_text set title = ‘Test’ where film_id = 1001;
ERROR 1099 (HY000): Table ‘film_text’ was locked with a READ lock and can’t be updated

Other sessions can insert operations, but updates wait:

mysql> insert into film_text (film_id,title) values(1002,’Test’);
Query OK, 1 row affected (0.00 sec)
mysql> update film_text set title = ‘Update Test’ where film_id = 1001;

wait for

Current session cannot access records inserted by other sessions:

mysql> select film_id,title from film_text where film_id = 1002;
Empty set (0.00 sec)

Release lock:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

wait for

Records inserted by other sessions can be obtained after the current session is unlocked:

mysql> select film_id,title from film_text where film_id = 1002;
+———+——-+
| film_id | title |
+———+——-+
| 1002    | Test  |
+———+——-+
1 row in set (0.00 sec)

Session 2 obtains the lock and the update operation completes:

mysql> update film_text set title = ‘Update Test’ where film_id = 1001;
Query OK, 1 row affected (1 min 17.75 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The concurrent insertion feature of MyISAM storage engine can be used to solve the lock contention for the same table query and insertion in application. For example, setting the concurrent_insert system variable to 2 always allows concurrent inserts; at the same time, space debris can be sorted out by periodically executing OPTIMIZE TABLE statements during the idle period of the system to recover the holes caused by deleting records. For a detailed introduction to OPTIMIZE TABLE statements, see the section “Two Simple and Practical Optimizations” in Chapter 18.

Lock Scheduling in MyISAM

As mentioned earlier, the read and write locks of MyISAM storage engine are mutually exclusive and the read and write operations are serial. So, how does MySQL handle when one process requests a read lock for one MyISAM table and another process requests a write lock for the same table? The answer is that the write process gets 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 believes that writing requests is generally more important than reading requests. This is why MyISAM tables are not suitable for applications with 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, which may be blocked forever. Sometimes this situation can get very bad! Fortunately, we can adjust MyISAM’s scheduling behavior through some settings.

  • By specifying the start parameter low-priority-updates, the MyISAM engine defaults to give priority to read requests.

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

  • By specifying LOW_PRIORITY attributes of INSERT, UPDATE and DELETE statements, the priority of the statement is reduced.

Although the above three methods are either update-first or query-first, they can still be used to solve the serious problem of Read-Lock waiting in relatively important query applications, such as user login system.

In addition, MySQL also provides a compromise method to adjust the read-write conflict, that is, to set an appropriate value for the system parameter max_write_lock_count. When a table’s read lock reaches this value, MySQL temporarily reduces the priority of write requests and gives the read process a chance to get the lock.

The problems and solutions brought about by the Write First Scheduling mechanism have been discussed above. Here’s another point to emphasize: some query operations that need to be run for a long time will also starve the writing process! Therefore, we should try our best to avoid long-running query operations in application, and not always want 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, we can do some “decomposition” of the SQL statement by using intermediate tables and other measures. In this way, each step of the query can be completed in a relatively short time, thus reducing lock conflicts. If complex queries are unavoidable, try to schedule execution in the free time of the database, for example, some periodic statistics can be scheduled for execution at night.

InnoDB Lock Problem

The biggest difference between InnoDB and MyISAM is that they support transactions (TRANSACTION) and use 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. Let’s start with a little background, and then discuss InnoDB locks in detail.

background knowledge

1. Transaction and its ACID attributes

Transaction is a logical processing unit consisting of a set of SQL statements. Transaction has the following four attributes, commonly referred to as ACID attributes of transaction.

  • Atomicity: A transaction is an atomic unit of operation that modifies data either entirely or entirely.

  • Consistent: Data must be consistent both at the beginning and at the end of a transaction. This means that all relevant data rules must be applied to transaction modifications to maintain data integrity; at the end of the transaction, all internal data structures (such as B-tree index or bi-directional linked list) must also be correct.

  • Isolation: The database system provides some isolation mechanism to ensure that transactions are executed in an “independent” environment that is not affected by external concurrent operations. This means that the intermediate state in the transaction process is invisible to the outside, and vice versa.

  • Durable: After a transaction is completed, its modifications to the data are permanent and can be maintained even in case of a system failure.

Bank transfers are a typical example of business.

2. Problems arising from concurrent transaction processing

Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources, improve transaction throughput of database system, and thus support more users. However, the issue transaction processing will also bring some problems, mainly including the following situations.

  • Lost Update:When two or more transactions select the same row and update the row based on the initially selected value, the problem of lost updates occurs because each transaction does not know the existence of other transactions – the last update covers the updates made by other firms. For example, two editors made electronic copies of the same document. Each editor changes its copy independently, and then saves the changed copy, thus overwriting the original document. Finally, the editor who saves a copy of his changes overwrites the changes made by another editor. This problem can be avoided if one editor cannot access the same file before completing and committing a transaction.

  • Dirty Reads:A transaction is modifying a record, which is inconsistent before the transaction is completed and submitted; at this time, another transaction reads the same record. If not controlled, the second transaction reads the “dirty” data, and further processing based on it will occur. Unsubmitted data dependencies. This phenomenon is vividly called “dirty reading”.

  • Non-Repeatable Reads:When a transaction reads some data at some time, it reads the previously read data again, but finds that the data it reads has changed, or some records have been deleted! This phenomenon is called “non-repeatable reading”.

  • Phantom Reads:When a transaction reads the previously retrieved data according to the same query conditions, it finds that other transactions insert new data satisfying its query conditions. This phenomenon is called “hallucination”.

3. Transaction isolation level

Among the problems associated with concurrent transaction processing mentioned aboveLoss of updates should usually be completely avoided. However, to prevent the loss of updates, it can not be solved by the database transaction controller alone. It needs the application program to add the necessary locks to update the data. Therefore, it should be the responsibility of the application to prevent the loss of updates.

Dirty reading, non-repeatable reading and hallucination reading are all problems of consistency of database reading, which must be solved by the transaction isolation mechanism provided by database.There are basically two ways for database to achieve transaction isolation.

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

  • The other is to generate a consistent snapshot of data request time points (Snapshot) without any locks, and use this snapshot to provide consistent reading at a certain level (statement level or transaction level). From the user’s point of view, it seems that the database can provide multiple versions of the same data, so this technology is called data.Multi-version concurrency controlMultiVersion Concurrency Control (MVCC or MCC) is also often called multi-version database.

The stricter the transaction isolation is, the smaller the side effects of concurrency will be, but the higher the cost will be, because transaction isolation is essentially to make transactions “serialized” to a certain extent, which is obviously contradictory to “concurrency”. At the same time, different applications require different levels of read consistency and transaction isolation. For example, many applications are not sensitive to “unrepeatable reading” and “hallucination”, and may be more concerned about the ability of concurrent access to data.

In order to solve the contradiction between “isolation” and “concurrency”, ISO/ANSI SQL92 defines four transaction isolation levels, each of which has different isolation levels and allows different side effects. Applications can balance the contradiction between “isolation” and “concurrency” by choosing different isolation levels according to their own business logic requirements. 。 The following table gives a good overview of the four isolation levels.

Comparison of four isolation levels

Read data consistency and permissible concurrent side effects

Isolation level

Read data consistency Dirty reading Non-repeatable reading phantom read

Read uncommitted

At the lowest level, only physically damaged data can be guaranteed not to be read yes yes yes

Read committed

Statement level no yes yes

Repeatable read

Transaction level no no yes

Serializable

Top level, transaction level no no no

Finally, it should be pointed out that the four isolation levels mentioned above are not necessarily fully realized in each specific database. For example, Oracle only provides two standard isolation levels, Read committed and Serilizable, and also provides its own definition of Read only isolation level. In addition to supporting the four isolation levels defined by ISO/ANSI SQL92, SQL Server supports the four isolation levels defined above. It 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 when implemented, there are some features, such as MVCC consistent reading in some isolation levels, but not in some cases, which will be further described in the following chapters.

Get InnoDB row lock contention

The contention for row locks on the system can be analyzed by checking InnoDB_row_lock state variables:


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 found to be serious, such as InnoDB_row_lock_waits and InnoDB_row_lock_time_avg, the values are higher.In addition, InnoDB Monitors can be set up to further observe the tables, data rows, etc. where lock conflicts occur, and to analyze the causes of lock contention.

The specific methods are 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:


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 stop viewing by issuing the following statement:


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

After setting up the monitor, in the display content of SHOW INNODB STATUS, there will be detailed information about the current lock waiting, including table name, lock type, lock record, etc. for further analysis and problem determination. When the monitor is turned on, by default, the monitored content is recorded in the log every 15 seconds. If it is opened for a long time, the. err file becomes very large. So after confirming the cause of the problem, the user should remember to delete the monitor table to close the monitor or start the server by using the’–console’option. Close the write log file.

InnoDB’s Lock Mode and Locking Method

InnoDB implements the following two types of row locks.

  • Shared Lock (S): Allows a transaction to read a row, preventing other transactions from acquiring exclusive locks for the same data set.

  • Exclusive Locks (X): Allows transactions that acquire exclusive locks to update data, preventing other transactions from acquiring shared read locks and exclusive write locks for the same data set. In addition, in order to allow the coexistence of row locks and table locks and realize multi-granularity lock mechanism, InnoDB also has two kinds of intentional locks used internally, both of which are table locks.

  • Intentional shared locks (IS): Transactions intend to add row shared locks to data rows. Transactions must acquire the IS locks of the table before adding shared locks to a data row.

  • Intentional exclusive locks (IX): Transactions intend to add row exclusive locks to data rows. Transactions must acquire the IX locks of the table before adding row exclusive locks to a data row.

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

InnoDB row lock mode compatibility list

Request lock mode

Compatibility

Current lock mode

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

If the lock mode of a transaction request 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 automatically added by InnoDB without user intervention. For UPDATE, DELETE and INSERT statements, InnoDB automatically adds exclusive locks (X) to the data set involved; for ordinary SELECT statements, InnoDB does not add any locks; transactions can be displayed as shared locks or exclusive locks to the recordset through the following statements.

  • Shared Lock (S): SELECT * FROM table_name WHERE… LOCK IN SHARE MODE.

  • Exclusive Locks (X): SELECT * FROM table_name WHERE… For UPDATE.

Using SELECT… IN SHARE MODE to obtain shared locks is mainly used to confirm the existence of a row record when data dependencies are required, and to ensure that no UPDATE or DELETE operation is performed on the record. However, if the current transaction also needs to update the record, it is likely to cause deadlock. For applications that need to update after locking the row record, we should use SELECT… FOR UPDATE to obtain exclusive locks.

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

An example of shared locks in InnoDB storage engine

session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 178;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 178;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)

The current session has a shared lock for actor_id=178 records plus share mode:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.01 sec)

Other sessions can still query records and add a shared lock of share mode to the record:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.01 sec)

The current session updates the locked record and waits for the lock:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

wait for

Other sessions also update the record, resulting in deadlock exit:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After the lock is acquired, it can be successfully updated:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;
Query OK, 1 row affected (17.67 sec)
Rows matched: 1  Changed: 1  Warnings: 0

When using SELECT… FOR UPDATE to lock and then update the record, the following table shows the situation.

Examples of exclusive locks in InnoDB storage engines

session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 178;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 178;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)

The current session adds an exclusive lock for update to the record of actor_id=178:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)

Other sessions can query the record, but can’t add a shared lock to the record and wait for the lock to be acquired:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE    |
+———-+————+———–+
1 row in set (0.00 sec)
mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

wait for

The current session can update the locked record and release the lock after the update:

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Other sessions get locks and records submitted by other sessions:

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;
+———-+————+———–+
| actor_id | first_name | last_name |
+———-+————+———–+
| 178      | LISA       | MONROE T  |
+———-+————+———–+
1 row in set (9.59 sec)


InnoDB Line Lock Implementation

InnoDB row locks are implemented by locking index items on the indexMySQL, unlike Oracle, isIt is realized by locking the corresponding data rows in the data block. InnoDB’s implementation of row locks means that InnoDB only uses row-level locks if it retrieves data through index conditions, otherwise InnoDB will use table locks!

In practical applications, special attention should be paid to InnoDB row locks, otherwise, it may lead to a large number of lock conflicts, thus affecting concurrency performance.Here are some practical examples to illustrate.

(1) InnoDB does use table locks rather than row locks when querying without indexing conditions.

In the example shown below, the start tab_no_index table has no index:


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

An example of InnoDB storage engine using table locks when tables do not use indexes

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_no_index where id = 1 ;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
+——+——+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_no_index where id = 2 ;
+——+——+
| id   | name |
+——+——+
| 2    | 2    |
+——+——+
1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 1 for update;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
+——+——+
1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 2 for update;

wait for

In the example shown in the table above, it seems that session_1 only adds exclusive locks to one row, but session_2 appears to be waiting for locks when requesting exclusive locks on other rows! The reason is that InnoDB can only use table locks without indexes. When we add an index to it, InnoDB locks only qualified rows, as shown in the table below.

Create a tab_with_index table with a common index in 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

An example of InnoDB storage engine tables using row locks when using indexes

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 ;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
+——+——+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 2 ;
+——+——+
| id   | name |
+——+——+
| 2    | 2    |
+——+——+
1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
+——+——+
1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 2 for update;
+——+——+
| id   | name |
+——+——+
| 2    | 2    |
+——+——+
1 row in set (0.00 sec)

(2)Because MySQL’s row locks are for index-added locks, not for records-added locks, although it accesses records of different rows, lock conflicts occur if the same index key is used.This should be noted in application design.

In the example shown in the following table, the ID field of tab_with_index has an index and the name field has no 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)

An example of InnoDB storage engine blocking using the same index key

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 and name = ‘1’ for update;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
+——+——+
1 row in set (0.00 sec)

Although session_2 accesses records different from session_1, it needs to wait for locks because the same index is used:

mysql> select * from tab_with_index where id = 1 and name = ‘4’ for update;

wait for

(3) When tables have 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 index, unique index or ordinary index.

In the example shown in the following table, the ID field of tab_with_index has a primary key index and the name field has a general 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 examples of tables using different indexes in InnoDB storage engine

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update;
+——+——+
| id   | name |
+——+——+
| 1    | 1    |
| 1    | 4    |
+——+——+
2 rows in set (0.00 sec)

Session_2 accesses records using the index of name, because records are not indexed, so locks can be obtained:

mysql> select * from tab_with_index where name = ‘2’ for update;
+——+——+
| id   | name |
+——+——+
| 2    | 2    |
+——+——+
1 row in set (0.00 sec)

Because the access record has been locked by session_1, the lock is waiting to be acquired. :

mysql> select * from tab_with_index where name = ‘4’ for update;

(4) Even if index fields are used in conditions, the use of index to retrieve data is determined by MySQL’s cost of judging different execution plans. If MySQL believes that full table scanning is more efficient, for example, for some very small tables, it will not use index. In this case InnoDB will use table locks instead of table locks. Line lock.Therefore, when analyzing lock conflicts, don’t forget to check the execution plan of SQL to confirm that the index is actually used.

In the following example, the data type of the retrieved value is different from that of the index field. Although MySQL can convert the data type, it does not use the index, which results in InnoDB using table locks. Check the execution of two SQLs by explainingWe can clearly see this in the plan.

In the example, the name field of the tab_with_index table is indexed, but the name field is of varchar type. If the where condition is not compared with the varchar type, the name is converted and the full table scan is 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)

Next-Key Lock

InnoDB locks the index entries of qualified existing data records when we retrieve data using range conditions rather than equality conditions and request sharing or exclusive locks; for records whose key values are within the condition range but do not exist, InnoDB locks this gap, too. The system is the so-called Next-Key lock.

For example, if there are only 101 records in the EMP table, their empid values are 1,2,…, 100,101, respectively. The following SQL:


Select * from emp where empid > 100 for update;

InnoDB locks not only records with a qualified empid value of 101, but also “gaps” with empid greater than 101 (which do not exist).

InnoDB uses gap locks, on the one hand, to prevent hallucination to meet the requirements of the relevant isolation level. For the above example, if no gap locks are used, if any records with empid greater than 100 are inserted into other transactions, hallucination will occur if the transaction executes the above statement again; on the other hand, hallucination will occur if the transaction executes the above statement again. It is to meet the needs of its recovery and replication. The impact of recovery and replication on lock mechanism and the use of gap locks in InnoDB at different isolation levels will be further described in subsequent chapters.

Obviously, InnoDB, as a locking mechanism, blocks concurrent insertion of key values within the eligible range when using scope conditions to retrieve and lock records, which often results in serious lock waiting. Therefore, in practical application development, especially concurrent insertion of more applications, we should try to optimize business logic, as far as possible to use the same conditions to access updated data, avoid the use of scope conditions.

In particular, InnoDB will also use gap locks if it requests to lock a non-existent record by using equal conditions, in addition to using gap locks when locking by range conditions.

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

Examples of Gap Lock Blocking in InnoDB Storage Engine

session_1 session_2
mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

The current session adds a lock for update to a non-existent record:

mysql> select * from emp where empid = 102 for update;
Empty set (0.00 sec)

At this point, if other sessions insert a record with empid 102 (note: this record does not exist), a lock wait will also occur:

mysql>insert into emp(empid,…) values(102,…);

Blocking waiting

Session_1 performs rollback:

mysql> rollback;
Query OK, 0 rows affected (13.04 sec)

Because the Next-Key lock is released after other session_1 falls back, the current session can obtain the lock and successfully insert the record:

mysql>insert into emp(empid,…) values(102,…);
Query OK, 1 row affected (13.35 sec)

The Need for Recovery and Replication and Its Impact on InnoDB Lock Mechanism

MySQL executes successful INSERT, UPDATE, DELETE and other SQL statements to update data through BINLOG records, and thus realizes the recovery and master-slave replication of MySQL database (see the introduction in the “Management Chapter” of this book). MySQL’s recovery mechanism (replication is actually a continuous BINLOG-based recovery in Slave Mysql) has the following characteristics.

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

Second, MySQL’s Binlog is recorded in the order of transaction submission, and recovery is done in this order. This is also different from Oracle, which restores data according to the System Change Number (SCN). At the beginning of each transaction, Oracle allocates a globally unique SCN. The order of SCN is consistent with the time sequence of transaction start.

As can be seen from the above two points, MySQL’s recovery mechanism requires that before a transaction is committed, other concurrent transactions cannot insert any records that satisfy its locking conditions, that is, no hallucination is allowed, which has exceeded the ISO/ANSI SQL92 “repeatable read” isolation level requirement, and in fact requires transactions to be serialized. This is also why InnoDB uses gap locks in many cases, such as when updating records with range conditions, whether at Read Commited or Repeatable Read isolation level, InnoDB uses gap locks, but this is not required by isolation level. The difference in locking InnoDB at different isolation levels is smaller. The section will also be introduced.

In addition, for the SQL statements “insert into target_tab select * from source_tab where…” and “create table new_tab… Select… From source_tab where… (CTAS)”, the user does not do any update operation on source_tab, but MySQL makes special treatment on this kind of SQL statement. Let’s start with an example of the table below.

Examples of CTAS operation locking the original table

session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = ‘1’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = ‘1’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = ‘1’;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> update source_tab set name = ‘1’ where name = ‘8’;

wait for

commit;

Return results

commit;

In the example above, simply reading the data from the source_tab table is equivalent to executing a common SELECT statement, which can be read consistently. This is what ORACLE does. It uses MVCC technology to implement multi-version data to achieve consistent reading, without any locks on source_tab. We know that InnoDB also implements multi-version data and does not need any locks for common SELECT consistent reading, but InnoDB adds shared locks to source_tab instead of using multi-version data consistent reading technology!

Why does MySQL do this? The reason is to ensure the correctness of recovery and replication. Because unlocked, if other transactions update source_tab during the execution of the above statement, the result of data recovery may be wrong. To illustrate this point, let’s repeat the previous example. The difference is that before session_1 executes a transaction, the value of the system variable innodb_locks_unsafe_for_binlog is set to “on” (its default value is off), as shown in the table below.

Examples of security problems caused by CTAS operation not locking the original table

session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql>set innodb_locks_unsafe_for_binlog=’on’
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = ‘1’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from target_tab;
Empty set (0.00 sec)
mysql> select * from source_tab where name = ‘1’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 1    |  1 |
|  5 | 1    |  1 |
|  6 | 1    |  1 |
|  7 | 1    |  1 |
|  8 | 1    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = ‘1’;
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Session_1 is not submitted, so the record of session_1 select can be updated.

mysql> update source_tab set name = ‘8’ where name = ‘1’;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> select * from source_tab where name = ‘8’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)

Update operation submits first

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

Submit after insertion

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

Looking at the data at this point, the target_tab can insert the results before the source_tab update, which is in line with the application logic:

mysql> select * from source_tab where name = ‘8’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> select * from target_tab;
+——+——+
| id   | name |
+——+——+
| 4    | 1.00 |
| 5    | 1.00 |
| 6    | 1.00 |
| 7    | 1.00 |
| 8    | 1.00 |
+——+——+
5 rows in set (0.00 sec)

mysql> select * from tt1 where name = ‘1’;
Empty set (0.00 sec)
mysql> select * from source_tab where name = ‘8’;
+—-+——+—-+
| d1 | name | d2 |
+—-+——+—-+
|  4 | 8    |  1 |
|  5 | 8    |  1 |
|  6 | 8    |  1 |
|  7 | 8    |  1 |
|  8 | 8    |  1 |
+—-+——+—-+
5 rows in set (0.00 sec)
mysql> select * from target_tab;
+——+——+
| id   | name |
+——+——+
| 4    | 1.00 |
| 5    | 1.00 |
| 6    | 1.00 |
| 7    | 1.00 |
| 8    | 1.00 |
+——+——+
5 rows in set (0.00 sec)

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 the source_tab, and the result conforms to the application logic, but 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 location of update operation is before INSERT… SELECT. If the BINLOG is used to restore the database, the result of recovery does not match the actual application logic. If the BINLOG is replicated, it will lead to inconsistency between master and slave databases.

With the examples above, it’s not hard to understand why MySQL locks source_tab when dealing with “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 concurrency impact to achieve consistency. Sexual reading. In particular, InnoDB also adds a Next-Lock to the source table if the SELECT of the above statement is a scope condition.

Therefore, INSERT… SELECT… and CRATE TABLE… SELECT… statements may prevent concurrent updates to source publications, resulting in waiting for source table locks. If the query is more complex, it will cause serious performance problems. We should try to avoid using it in the application. In fact, MySQL calls this kind of SQL non-deterministic and is not recommended.

If the application must use this kind of SQL to implement business logic, and do not want to affect the concurrent updates of source publications, the following two measures can be taken:

  • One is to adopt the approach of the example above, set the value of innodb_locks_unsafe_for_binlog to “on”, forcing MySQL to use multi-version data consistency reading. However, the cost is that it may not be possible to recover or copy data correctly with binlog, so this approach is not recommended.

  • Secondly, it is indirectly implemented by using the combination of “select * from source_tab… Into outfile” and “load data infile…” statements. In this way, MySQL will not lock source_tab.

Consistent Read and Lock Differences in InnoDB at Different Isolation Levels

As mentioned earlier, locks and multi-version data are the means of InnoDB to achieve consistency reading and ISO/ANSI SQL92 isolation level. Therefore, under different isolation levels, InnoDB uses different consistency reading strategies and needs different locks when dealing with SQL. At the same time, the characteristics of data recovery and replication mechanism also have a great impact on some SQL consistent read strategy and lock strategy. These features are summarized as shown in the table below for reader’s reference.

Lock Comparison of Different SQL in InnoDB Storage Engine at Different Isolation Levels

Isolation level

Consistent Read and Lock

SQL

Read Uncommited Read Commited Repeatable Read Serializable
SQL condition
select Equal None locks Consisten read/None lock Consisten read/None lock Share locks
Range None locks Consisten read/None lock Consisten read/None lock Share Next-Key
update Equal exclusive locks exclusive locks exclusive locks Exclusive locks
Range exclusive next-key exclusive next-key exclusive next-key exclusive next-key
Insert N/A exclusive locks exclusive locks exclusive locks exclusive locks
replace Keyless conflict exclusive locks exclusive locks exclusive locks exclusive locks
Key conflict exclusive next-key exclusive next-key exclusive next-key exclusive next-key
delete Equal exclusive locks exclusive locks exclusive locks exclusive locks
Range exclusive next-key exclusive next-key exclusive next-key exclusive next-key
Select … from … Lock in share mode Equal Share locks Share locks Share locks Share locks
Range Share locks Share locks Share Next-Key Share Next-Key
Select * from … For update Equal exclusive locks exclusive locks exclusive locks exclusive locks
Range exclusive locks Share locks exclusive next-key exclusive next-key

Insert into … Select …

(Referring to source table locks)

innodb_locks_unsafe_for_binlog=off Share Next-Key Share Next-Key Share Next-Key Share Next-Key
innodb_locks_unsafe_for_binlog=on None locks Consisten read/None lock Consisten read/None lock Share Next-Key

create table … Select …

(Referring to source table locks)

innodb_locks_unsafe_for_binlog=off Share Next-Key Share Next-Key Share Next-Key Share Next-Key
innodb_locks_unsafe_for_binlog=on None locks Consisten read/None lock Consisten read/None lock Share Next-Key

As can be seen from the table above, for many SQLs, the higher the isolation level, the stricter the locks InnoDB imposes on the Recordset (especially when the scope conditions are used), the higher the possibility of lock conflicts and the greater the impact on concurrent transaction processing performance. Therefore, in our application, we should try to use a lower isolation level to reduce the probability of lock contention. In fact, by optimizing transaction logic, the Read Commited isolation level is sufficient for most applications. For some transactions that do require a higher isolation level, the requirement can be met by executing SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ or SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE dynamically changing the isolation level in the program.

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 reason why we choose InnoDB tables. However, table-level locks can also be considered in individual special transactions.

  • The first case is that transactions need to update most or all of the data, and tables are relatively large. If the default row lock is used, not only the transaction execution efficiency is low, but also other transactions may have long lock waiting and lock conflict. In this case, table lock can be considered to improve the execution speed of the transaction.

  • The second scenario is that transactions involve multiple tables, which are complex and may cause deadlocks, resulting in a large number of transaction rollbacks. In this case, one-time locking of tables involved in transactions can also be considered to avoid deadlock and reduce the overhead of database caused by transaction rollback.

Of course, you can’t use too many of these two transactions in your application, otherwise you should consider using MyISAM tables.

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

(1) Although table-level locks can be added to InnoDB by using LOCK TABLES, it must be noted that table locks are not managed by InnoDB storage engine layer, but by the upper layer, MySQL Server. Only when autocommit = 0, innodb_table_locks = 1 (default setting), can InnoDB layer know the table locks added by MySQL, MySQL Serv Server. In this case, InnoDB can automatically identify deadlocks involving table level locks; otherwise, InnoDB will not be able to detect and process such deadlocks automatically. Deadlock will be discussed in the next section.

(2) When using LOCK TABLES to lock InnoDB tables, it should be noted that AUTOCOMMIT should be set to 0, otherwise MySQL will not lock tables; before the end of the transaction, do not release table locks with UNLOCK TABLES, because UNLOCK TABLES will implicitly commit transactions; COMMIT or ROLLBACK can not release table-level locks with LOCK TABLES, so UNLOCK TA must be used. BLES releases table locks.The correct way is as follows:

For example, if you need to write table t1 and read from table t, you can do the following:


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

On Deadlock

As mentioned above, MyISAM table locks are deadlock free, because MyISAM always gets all the locks needed at one time, either fully satisfied or waiting, so there will be no deadlock. However, in InnoDB, besides transactions composed of a single SQL, locks are gradually acquired, which determines that deadlocks are possible in InnoDB. An example of deadlock occurring is shown below.

An example of deadlock in InnoDB storage engine

session_1 session_2
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_1 where where id=1 for update;

Do something else.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from table_2 where id=1 for update;
select * from table_2 where id =1 for update;

Because session_2 has acquired an exclusive lock, wait

Do something else.
mysql> select * from table_1 where where id=1 for update;

deadlock

In the example above, both transactions need to acquire exclusive locks held by the other party to continue to complete the transaction. This kind of circular lock waiting is a typical deadlock.

After deadlock occurs, InnoDB can generally detect automatically, and make one transaction release the lock and roll back. Another transaction acquires the lock and continues to complete the transaction. However, InnoDB cannot detect deadlocks automatically when external locks or table locks are involved, which 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, causing serious performance problems and even dragging across the database. This can be avoided by setting an appropriate lock waiting timeout threshold.

Generally speaking, deadlock is a problem of application design. Most deadlocks can be avoided by adjusting business processes, database object design, transaction size, and SQL statements to access the database. Here are some common ways to avoid deadlocks through examples.

(1) In applications, if different programs access multiple tables concurrently, we should try to agree to access tables in the same order, which can greatly reduce the chance of deadlock. In the following example, deadlock is very likely to occur because of the different order in which two sessions access the two tables. But if accessed in the same order, deadlocks can be avoided.

An example of deadlock caused by table order in InnoDB storage engine

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| PENELOPE   | GUINESS   |
+————+———–+
1 row in set (0.00 sec)
mysql> insert into country (country_id,country) values(110,’Test’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into country (country_id,country) values(110,’Test’);

wait for

mysql> select first_name,last_name from actor where actor_id = 1 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| PENELOPE   | GUINESS   |
+————+———–+
1 row in set (0.00 sec)
mysql>  insert into country (country_id,country) values(110,’Test’);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

(2) When the program processes data in batches, if the data are sorted in advance, and the records are processed in a fixed order by each thread, the possibility of deadlock can be greatly reduced.

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

session_1 session_2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| PENELOPE   | GUINESS   |
+————+———–+
1 row in set (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 3 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| ED         | CHASE     |
+————+———–+
1 row in set (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 3 for update;

wait for

mysql> select first_name,last_name from actor where actor_id = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> select first_name,last_name from actor where actor_id = 3 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| ED         | CHASE     |
+————+———–+
1 row in set (4.71 sec)

(3) In a transaction, if you want to update a record, you should apply for an exclusive lock of sufficient level directly, that is, exclusive lock, instead of sharing lock first, and then applying exclusive lock when updating, because when the user applies for exclusive lock, other transactions may have acquired the same record share lock, resulting in lock conflict or even deadlock.

(4) As mentioned earlier, at the REPEATABLE-READ isolation level, if two threads simultaneously use SELECT… FOR UPDATE with exclusive locks for the same condition record, both threads will lock successfully without meeting the condition record. When the program finds that the record does not exist, it tries to insert a new record. If both threads do this, a deadlock will occur. In this case, the problem can be avoided by changing the isolation level to READ COMMITTED, as shown below.

Examples of deadlocks caused by isolation level in InnoDB storage engine 1

session_1 session_2
mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+—————–+
| @@tx_isolation  |
+—————–+
| REPEATABLE-READ |
+—————–+
1 row in set (0.00 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

The current session adds a lock for update to a non-existent record:

mysql> insert into actor (actor_id , first_name , last_name) values(201,’Lisa’,’Tom’);

Other sessions can also lock non-existent records for update:

mysql> insert into actor (actor_id, first_name , last_name) values(201,’Lisa’,’Tom’);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Because other sessions also lock the record, the current insert waits:

mysql> insert into actor (actor_id , first_name , last_name) values(201,’Lisa’,’Tom’);

wait for

Because other sessions have updated records, inserting records at this time will prompt deadlock and exit:

mysql> insert into actor (actor_id, first_name , last_name) values(201,’Lisa’,’Tom’);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Because other sessions have exited, the current session can get the lock and successfully insert the record:

mysql> insert into actor (actor_id , first_name , last_name) values(201,’Lisa’,’Tom’);
Query OK, 1 row affected (13.35 sec)

(5)When the isolation level is READ COMMITTED, if both threads execute SELECT… FOR UPDATE first, determine whether there are qualified records, and if not, insert records. At this point, only one thread can insert successfully, and another thread will have lock waiting. When the first thread submits, the second thread will make a re-error because of the primary key, but although this thread makes a mistake, it will get an exclusive lock! At this point, if the third thread comes to apply for exclusive locks, deadlocks will also occur.

In this case, insert operations can be done directly, and then the primary key re-exception can be caught, or in case of a primary key re-error, exclusive locks obtained by ROLLBACK release can always be executed, as shown below.

Examples of deadlocks caused by isolation level in InnoDB storage engine 2

session_1 session_2 session_3
mysql> select @@tx_isolation;
+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@tx_isolation;
+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@tx_isolation;
+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |
+—————-+
1 row in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

Session_1 obtains a shared lock for update:

mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)

Because the record does not exist, session_2 can also obtain a shared lock for update:

mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;
Empty set (0.00 sec)

Session_1 can successfully insert records:

mysql> insert into actor (actor_id,first_name,last_name) values(201,’Lisa’,’Tom’);
Query OK, 1 row affected (0.00 sec)

Session_2 Insert Application Waiting for Lock:

mysql> insert into actor (actor_id,first_name,last_name) values(201,’Lisa’,’Tom’);

wait for

Session_1 was successfully submitted:

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

Session_2 obtains the lock and finds that the primary key of the inserted record is heavy. At this time, an exception is thrown, but the shared lock is not released:

mysql> insert into actor (actor_id,first_name,last_name) values(201,’Lisa’,’Tom’);
ERROR 1062 (23000): Duplicate entry ‘201’ for key ‘PRIMARY’

Session_3 requests a shared lock because session_2 has locked the record, so session_3 needs to wait:

mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update;

wait for

At this point, if session_2 updates the record directly, the deadlock exception will be thrown:

mysql> update actor set last_name=’Lan’ where actor_id = 201;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

After Session_2 releases the lock, session_3 obtains the lock:

mysql> select first_name, last_name from actor where actor_id = 201 for update;
+————+———–+
| first_name | last_name |
+————+———–+
| Lisa       | Tom       |
+————+———–+
1 row in set (31.12 sec)

Although deadlocks can be greatly reduced through the design and optimization of SQL described above, deadlocks can hardly be completely avoided. Therefore, it is a good programming habit to always capture and handle deadlock exceptions in programming.

If a deadlock occurs, the SHOW INNODB STATUS command can be used to determine the cause of the last deadlock. The return results include details of deadlock-related transactions, such as the SQL statement that triggered the deadlock, the locks that the transaction has acquired, what locks are waiting for, and the transactions that are rolled back. Based on this, we can analyze the causes of deadlock and improve measures. The following is a sample output of SHOW INNODB STATUS:


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 use of Mysql table locks, row locks, shared locks, exclusive locks and gap locks, hoping to help you all.