Concurrency Control MySQL InnoDB table lock

InnoDB Lock Problems
There are two major differences between InnoDB and MyISAM: transaction support and row-Level Lock. There are many differences between Row-level locks and table-level locks. In addition, the introduction of transactions also brings about some new problems. Next we will introduce a little background knowledge and then discuss the InnoDB Lock in detail.
Background
1. transaction and its acid attributes
A transaction is a logical processing unit composed of a set of SQ statements. A transaction has the following four attributes, usually referred to as the acid attribute of a transaction.
Atomicity: a transaction is an atomic operation unit, and its modifications to the data are either all executed or all are not executed.
Consistent: data must be consistent at the beginning and end of the 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 indexes or two-way linked lists) it must also be correct.
Isolation: the database system provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment not affected by external concurrent operations. This means that the intermediate state in the transaction processing process is invisible to the outside, and vice versa.
Durabe: after the transaction is completed, it modifies the data permanently, even if a system failure occurs.
Bank transfers are a typical example of transactions.
2. Problems arising from concurrent Transaction Processing
Compared with serial processing, concurrent transaction processing can greatly increase the utilization of database resources, increase the transaction throughput of the database system, and thus support more users. However, concurrent transaction processing also brings about some problems, mainly including the following situations.
Update loss (OST update): When two or more transactions select the same row and update the row based on the originally selected value, because each transaction does not know the existence of other transactions, the update will be lost. The last update will overwrite the updates made by other firms. For example, two editors make an electronic copy of the same document. Each editor changes its copy independently and saves the changed copy to overwrite the original document. The edited person who saves the change copy finally overwrites the changes made by another edited person. This problem can be avoided if the other editor cannot access the same file before an editor completes and submits the transaction.
Dirty read (dirty reads): a transaction is modifying a record. before the transaction is completed and committed, the data of this record is inconsistent, another transaction also reads the same record. If no control is imposed, the second transaction reads the "dirty" data and performs further processing accordingly, the uncommitted data dependency is generated. This phenomenon is vividly called "Dirty reading ".
Non-repeatabe reads: A transaction reads previously read data at a certain time after reading some data, it is found that the read data has changed, or some records have been deleted! This phenomenon is called "repeatable reading ".
Phantom reads: A transaction re-reads previously retrieved data based on the same query conditions, but finds that other transactions have inserted new data that meets the query conditions, this phenomenon is called phantom reading ".
3. transaction isolation level
In the concurrent transaction processing problems mentioned above, "Update loss" should be completely avoided. However, to prevent the loss of updates, the Database Transaction Controller cannot be used alone. Program Apply the necessary lock to the data to be updated. Therefore, it is the responsibility of the application to prevent the loss of updates.
"Dirty read", "Repeatable read", and "Phantom read" are actually database read consistency issues that must be solved by the database by providing a certain transaction isolation mechanism. There are two ways to isolate transactions in a database.
One is to lock the data before reading it to prevent other transactions from modifying the data.
The other is to generate a snapshot of consistent data at the time point of a data request through a certain mechanism without any lock, and use this snapshot to provide a certain level (statement or transaction level). From the user's perspective, it seems that a database can provide multiple versions of the same data. Therefore, this technology is called mutiversion concurrency contro (MVCC or MCC ), it is also often called a multi-version database.
The stricter the database's transaction isolation, the smaller the concurrency side effects, but the higher the cost, because the transaction isolation essentially enables the transaction to be "serialized" to a certain extent, this is obviously in conflict with "concurrency. At the same time, different applications have different requirements on read consistency and transaction isolation. For example, many applications are not sensitive to "non-repeated read" and "Phantom read, may be more concerned with the capability of concurrent data access.
To solve the conflict between "isolation" and "concurrency", ISO/ANSI sq92 defines four transaction isolation levels, each of which has different degrees of isolation and allows different side effects, applications can balance the conflict between "isolation" and "concurrency" by selecting different isolation levels based on their business logic requirements. Table 20-5 provides a good overview of the four isolation-level features.

Finally, it should be noted that specific databases do not necessarily fully implement the preceding four isolation levels. For example, Oracle only provides two standard isolation levels: Read committed and serializable, in addition to the four isolation levels defined in ISO/ANSI sql92, SQL Server also supports an isolation level called "snapshot, but strictly speaking, it is a serializable isolation level implemented by MVCC. MySQL supports all four isolation levels, but it has some features in specific implementation. For example, MVCC consistent read is used at some isolation levels, but not in some cases, these content will be further introduced in the following sections.

 

Obtain InnoDB row lock contention

You can check the innodb_row_lock status variable to analyze the contention of row locks on the system:

Java Code

2. Mysql> show status like 'innodb _ row_lock % ';
4. + ------------------------------- + ------- +
6. | Variable_name | value |
8. + ------------------------------- + ------- +
10. | Innodb_row_lock_current_waits | 0 |
12. | Innodb_row_lock_time | 0 |
14. | Innodb_row_lock_time_avg | 0 |
16. | Innodb_row_lock_time_max | 0 |
18. | Innodb_row_lock_waits | 0 |
20. + ------------------------------- + ------- +
22. 5 rows in SET (0.01 Sec)

If you find that the lock contention is serious, for example, the values of innodb_row_lock_waits and innodb_row_lock_time_avg are relatively high, you can set InnoDB monitors to further observe the tables and data rows in which the lock conflict occurs, analyze the reasons for Lock contention. The specific method is as follows:

Java code

1. Mysql> Create Table innodb_monitor (a int) engine = InnoDB;
2. Query OK, 0 rows affected (0.14 Sec)

Then you can use the following statement to view the details:

Java code

1. Mysql> show InnoDB status \ G;
2. * *************************** 1. row ***************************
3. Type: InnoDB
4. Name:
5. Status:

The monitor can stop viewing by issuing the following statement:

Java code

1. Mysql> drop table innodb_monitor;
2. Query OK, 0 rows affected (0.05 Sec)

After the monitor is set, detailed information about the current lock wait will be displayed in the show InnoDB Status display, including the table name, lock type, and lock record, this facilitates further analysis and problem determination. After the monitor is enabled, the monitored content is recorded in the log every 15 seconds by default. the err file becomes very huge, so after you confirm the cause of the problem, remember to delete the monitoring table to disable the monitor, or use the "-- console" option to start the server to close the log file writing.
InnoDB row lock mode and locking method
InnoDB implements the following two types of row locks.
Share lock (s): allows a transaction to read a row to prevent other transactions from obtaining exclusive locks for the same dataset.
Exclusive lock (x): allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining shared read locks and exclusive write locks for the same dataset.
In addition, to allow row locks and table locks to coexist and implement multi-granularity locks, InnoDB also has two inner intention locks, both of which are table locks.
Intention share lock (is): the transaction intends to add a row share lock to the data row. The transaction must first obtain the is lock of the table before adding a share lock to the data row.
Intention exclusive lock (ix): the transaction intends to apply a row exclusive lock to the data row. The transaction must first obtain the IX lock of the table before applying an exclusive lock to the data row.
The compatibility of the above lock modes is shown in Table 20-6.

If the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction. If the two are not compatible, the transaction will wait for the lock to be released.
The intention lock is automatically added by InnoDB without user intervention. For update, delete, and insert statements, InnoDB automatically adds an exclusive lock (X) to the involved dataset. InnoDB does not apply any lock to normal select statements; A transaction can use the following statement to add a shared lock or exclusive lock to the record set.
Share 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 the shared lock. It is mainly used to confirm whether a row of records exists when data dependency is required, and ensure that no one performs update or delete operations on the record. However, if the current transaction also needs to update the record, it is likely to cause a deadlock. For applications that require update operations after the row record is locked, select... obtain the exclusive lock in the for update mode.
In the example shown in Table 20-7, the record is updated after the Select... in share mode is locked to see what happens. The actor_id field of the actor table is the primary key.

When the Select... for update lock is used, the record is updated, as shown in table 20-8.

InnoDB row lock Implementation Method
InnoDB row locks are implemented by locking the index items on the index. This is different from Oracle in MySQL. The latter is implemented by locking the corresponding data rows in the data block. The implementation of InnoDB row locks means that InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB uses table locks!
In practical applications, pay special attention to the InnoDB row lock feature. Otherwise, it may lead to a large number of lock conflicts, thus affecting concurrent performance. The following are some examples.
(1) When InnoDB does not pass the index condition query, InnoDB does use table locks rather than row locks. In the example shown in Table 20-9, the tab_no_index table has no index at the beginning: Java code

1. Mysql> Create Table tab_no_index (ID int, name varchar (10) engine = InnoDB;
2. Query OK, 0 rows affected (0.15 Sec)
3. Mysql> insert into tab_no_index values (1, '1'), (2, '2'), (3, '3'), (4, '4 ');
4. Query OK, 4 rows affected (0.00 Sec)
5. Records: 4 duplicates: 0 Warnings: 0

Table 20-9 examples of using Table locks when indexes are not used for InnoDB Storage engine tables

In the example shown in Table 20-9, it seems that session_1 only adds an exclusive lock to one row, but session_2 waits for a lock when it requests an exclusive lock from other rows! The reason is that InnoDB can only use table locks without indexing. After we add an index to it, InnoDB only locks the qualified rows, as shown in table 20-10.
Create a tab_with_index table. The ID field has a common index: Java code.

1. Mysql> Create Table tab_with_index (ID int, name varchar (10) engine = InnoDB;
2. Query OK, 0 rows affected (0.15 Sec)
3. Mysql> alter table tab_with_index add index ID (ID );
4. Query OK, 4 rows affected (0.24 Sec)
5. Records: 4 duplicates: 0 Warnings: 0

(2) because MySQL row locks apply to indexes rather than to records, although they access records of different rows, if the same index key is used, yes, there will be lock conflicts. Pay attention to this when designing applications. In the example shown in Table 20-11, the ID field of table tab_with_index has an index and the name field has no index.

Java code

2. Mysql> alter table tab_with_index drop index name;
4. Query OK, 4 rows affected (0.22 Sec)
6. Records: 4 duplicates: 0 Warnings: 0
8. Mysql> insert into tab_with_index values (1, '4 ');
10. Query OK, 1 row affected (0.00 Sec)
12. Mysql> select * From tab_with_index where id = 1;
14. + ------ +
16. | ID | Name |
18. + ------ +
20. | 1 | 1 |
22. | 1 | 4 |
24. + ------ +
26. 2 rows in SET (0.00 Sec)

(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using primary key indexes, unique indexes, or common indexes, innoDB uses row locks to lock data.
In the example shown in Table 20-12, the ID field of table tab_with_index has a primary key index, and the name field has a common index:

Java code

1. Mysql> alter table tab_with_index add index name (name );
2. Query OK, 5 rows affected (0.23 Sec)
3. Records: 5 duplicates: 0 Warnings: 0

Example of blocking when different indexes are used in tables of Table 20-12 InnoDB Storage Engine

(4) even if the index field is used in the condition, it is determined by MySQL to determine whether to use the index to retrieve data by determining the cost of different execution plans, if MySQL considers that full table scan is more efficient, for example, for some small tables, it will not use indexes. In this case, InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, do not forget to check the SQL Execution Plan to confirm whether the index is actually used. For more information about how MySQL does not use indexes, see the "index problem" section in this chapter.
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 causes InnoDB to use the table lock. By using explain to check the execution plans of the two SQL statements, we can clearly see this.
In this example, the name field of the tab_with_index table has an index, but the name field is of the varchar type. If the where condition is not compared with the varchar type, the name type is converted, and perform a full table scan.

Java code

2. Mysql> alter table tab_no_index add index name (name );
4. Query OK, 4 rows affected (8.06 Sec)
6. Records: 4 duplicates: 0 Warnings: 0
8. Mysql> explain select * From tab_with_index where name = 1 \ G
10. * *************************** 1. row ***************************
12. ID: 1
14. Select_type: simple
16. Table: tab_with_index
18. Type: All
20. Possible_keys: Name
22. Key: NULL
24. Key_len: NULL
26. Ref: NULL
28. Rows: 4
30. Extra: Using where
32. 1 row in SET (0.00 Sec)
34. Mysql> explain select * From tab_with_index where name = '1' \ G
36. * *************************** 1. row ***************************
38. ID: 1
40. Select_type: simple
42. Table: tab_with_index
44. Type: ref
46. Possible_keys: Name
48. Key: Name
50. Key_len: 23
52. Ref: const
54. Rows: 1
56. Extra: Using where
58. 1 row in SET (0.00 Sec)

Gap lock (next-key lock)

When we use range conditions instead of equal conditions to retrieve data and request sharing or exclusive locks, InnoDB locks the index items of existing data records that meet the conditions; for a record whose key value is within the condition but does not exist, it is called "gap". InnoDB also locks the "gap, this lock mechanism is called the next-key lock ). For example, if there are only 101 records in the EMP table, the empid values are 1, 2 ,..., 100,101, the following SQL: Select * from EMP where empid> 100 for update; Is a range-based search. InnoDB not only locks records that meet the condition's empid value of 101, it also locks the gap between empid greater than 101 (these records do not exist. InnoDB uses clearance locks to prevent Phantom reads to meet isolation-level requirements. For the above example, if the gap locks are not used, if other transactions insert any record with an empid greater than 100, Phantom reads will occur if the preceding statement is executed again. On the other hand, it is to meet the needs of recovery and replication. The impact of restoration and replication on the lock mechanism and the use of gap locks in InnoDB at different isolation levels will be further described in the subsequent sections. Obviously, when a range condition is used to retrieve and lock a record, InnoDB locks will block concurrent inserts of key values within the range that meet the condition, which may lead to severe lock waits. Therefore, in actual application development, especially for applications with a large number of concurrent inserts, we should optimize the business logic as much as possible and use equal conditions to access and update data to avoid using range conditions. Additionally, InnoDB uses clearance locks in addition to range condition locking. If a non-existent reCord is requested to be locked using equal conditions, InnoDB also uses clearance locks! In the example shown in Table 20-13, if there are only 101 records in the EMP table, the empid values are 100,101, and respectively.

The need for restoration and replication affects the InnoDB Lock Mechanism. MySQL uses the BINLOG to execute SQL statements that update data such as insert, update, and delete successfully, in this way, MySQL database recovery and master-slave replication can be achieved (see "management" in this book ). MySQL's recovery mechanism (replication is actually a constant BINLOG-based restoration in slave MySQL) has the following features. L first, MySQL recovers at the SQL statement level, that is, re-execute the SQL statement in BINLOG. Unlike Oracle databases, Oracle is based on database file blocks. L second, the BINLOG of MySQL is recorded in the transaction commit order, and the recovery is also performed in this order. This is also different from oralce. Oracle restores data according to the system change number (SCN). At the beginning of each transaction, Oracle allocates a globally unique SCN, the sequence of the SCN is the same as that of the start time of the transaction. 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 its locking conditions, that is, Phantom reads are not allowed, this has already exceeded the ISO/ANSI sql92 "Repeatable read" isolation level requirement. In fact, the transaction needs to be serialized. This is also the reason for InnoDB to use clearance locks in many cases. For example, when a range condition is used to update a record, it is not discussed at the read commited or Repeatable read isolation level, innoDB requires clearance locks, but this is not required at the isolation level. The differences between InnoDB locks at different isolation levels will be introduced in the next section. In addition, for "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 does special processing on such SQL statements. First, let's look at the example in table 20-14.

in the preceding example, the data in the source_tab table is simply read, which is equivalent to executing a common SELECT statement and using consistent read. Oracle does this. It implements consistent read through multi-version data implemented by MVCC technology and does not need to add any locks to source_tab. We know that InnoDB also implements Multi-version data and does not need to apply any lock to normal select consistent reads. However, InnoDB adds a shared lock to source_tab, multi-version Data Consistency reading technology is not used!