mysql-Lock (2)

InnoDB How to implement a row lock

InnoDB row locks are implemented by locking the index entries on the index, which is different from Oracle, which is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the fact that InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock!

In practice, it is important to pay special attention to this feature of the InnoDB row lock, otherwise, it may lead to a lot of lock conflicts, which can affect the concurrency performance. Here are some practical examples to illustrate.

(1) The InnoDB does use a table lock instead of a row lock when querying without an index condition.

In the example shown in table 20-9, the starting Tab_no_index table is not indexed:

1

Table 20-9 InnoDB Storage engine tables Use the table lock example when you do not use an index

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

In the example shown in table 20-9, it seems that session_1 only adds an exclusive lock on one line, but Session_2 waits for a lock while requesting an exclusive lock on another row! The reason is that InnoDB can only use table locks without an index. When we add an index to it, InnoDB only locks the qualifying rows, as shown in table 20-10.

To create a tab_with_index table, the ID field has a normal index:

1

Table 20-10 InnoDB Storage engine tables Use row lock example 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) Since the MySQL row lock is for the index plus lock, not for the record plus lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict. Be aware of this when applying design.

In the example shown in table 20-11, the ID field of table Tab_with_index is indexed and the name field is not indexed:

1

Table 20-11 Block example of InnoDB storage engine 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 a different record than session_1, it needs to wait for the lock because the same index is used: Mysql> SELECT * from tab_with_index where id = 1 and name = ' 4 ' for update; Wait

(3) When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index.

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 normal index:

1

Table 20-12 Blocking examples of tables using different indexes for INNODB storage engine table

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 uses the index of name to access the record because the record is not indexed, so the lock 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)
	The access record has been locked by session_1, so wait for the lock to be acquired. ： Mysql> SELECT * from tab_with_index WHERE name = ' 4 ' for update;

(4) Even if the index field is used in the condition, but whether the index is used to retrieve the data is determined by MySQL by judging the cost of different execution plans, if MySQL thinks that the full table scan is more efficient, such as for some small tables, it will not use the index, in which case InnoDB will use the table lock. Instead of a row lock. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used. For a detailed discussion of when MySQL does not use indexes, see the "Indexing Issues" section of this chapter.

In the following example, the data type of the retrieved value is different from the indexed field, although MySQL is capable of data type conversion but does not use the index, which causes InnoDB to use table locks. We can clearly see this by checking the execution plan of two SQL with explain.

In the example, the Name field of the Tab_with_index table is indexed, but the name field is a varchar type, and if the Where condition is not compared to the varchar type, then a full table scan is performed for the name type conversion.

1

Gap Lock (Next-key Lock)

When we retrieve data with a range condition rather than an equal condition, and request a shared or exclusive lock, InnoDB locks the index entry for the qualifying existing data record, and for a record in which the key value is within the condition but does not exist, called "gap", InnoDB also locks the "gap". This locking mechanism is called a gap lock (Next-key lock).

For example, if there are only 101 records in the EMP table, the value of the Empid is,..., 100,101 respectively, the following SQL:

1

is a retrieval of a range condition, innodb not only locks the records that meet the conditional Empid value of 101, but also locks the "gap" of Empid greater than 101 (which do not exist).

InnoDB The purpose of the use of clearance locks, on the one hand is to prevent Phantom reading to meet the requirements of the relevant isolation level, for the above example, if you do not use a gap lock, if other transactions inserted empid greater than 100 of any record, then this transaction if the above statement execution, a phantom reading will occur, on the other hand, is to meet the needs of its recovery and replication. Further sections are described in subsequent chapters on the effect of their recovery and replication on the locking mechanism, and the use of gap locks under different isolation levels in InnoDB.

Obviously, when using scope conditions to retrieve and lock records, the INNODB locking mechanism blocks concurrent insertions that match the key values within the range, which often results in severe lock waits. Therefore, in the actual application development, especially the concurrent inserting more and more applications, we should try to optimize the business logic, try to use equal conditions to access the updated data, to avoid the use of scope conditions.

It is also necessary to note that InnoDB, in addition to using the clearance lock when the range condition is locked, the InnoDB will use a gap lock if the equivalent condition is used to request a non-existent record lock!

In the example shown in table 20-13, if there are only 101 records in the EMP table, the value of the Empid is,......, 100,101 respectively.

Table 20-13 Example of Gap lock blocking for 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 records that do not exist: Mysql> SELECT * from emp where Empid = 102 for update; Empty Set (0.00 sec)
	At this point, if the other session inserts a record of Empid 201 (note: This record does not exist), there will also be a lock wait: Mysql>insert into EMP (empid,...) VALUES (201,...); Blocking wait
Session_1 Execution Rollback: mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	Since the other session_1 has released the Next-key lock, the current session can obtain a lock and successfully insert the record: Mysql>insert into EMP (empid,...) VALUES (201,...); Query OK, 1 row affected (13.35 sec)

The effects of recovery and replication on the INNODB lock mechanism

MySQL performs a successful SQL statement of INSERT, UPDATE, Delete, and other updated data through Binlog, and thus implements the recovery and master-slave replication of the MySQL database (see the "Management" section of this book). The MySQL recovery mechanism (replication is actually in slave MySQL constantly doing binlog based recovery) has the following characteristics.

The first is that MySQL recovery is SQL statement-level, which is to re-execute the SQL statement in Binlog. This differs from the Oracle database in that Oracle is based on a database file block.

The second is that MySQL's binlog are recorded in the order in which the transactions are submitted, and the recovery is done in this order. This is also different from Oralce, where Oracle recovers data according to system change NUMBER,SCN, and when each transaction begins, the order in which Oracle assigns a globally unique SCN,SCN is consistent with the order in which transactions begin.

From the above two, the recovery mechanism of MySQL requires that, before a transaction is committed, other concurrent transactions cannot insert any records that satisfy their locking criteria, that is, no phantom reads are allowed, which exceeds the requirements of Iso/ansi SQL92 "repeatable read" isolation level, which in fact requires transactions to be serialized. This is also the reason for InnoDB to use clearance locks in many cases, such as when updating records with a range condition, InnoDB use a gap lock regardless of the read commited or REPEATABLE read isolation level, but this is not required by the isolation level, The difference between InnoDB and locking at different isolation levels is described in the next section.

Also, for "INSERT into Target_tab select * from Source_tab where ..." and "CREATE TABLE New_tab ... select ... From Source_tab where ... (CTAS) "This SQL statement, the user did not do any update to Source_tab, but MySQL has done special processing of this SQL statement." Let's take a look at the example shown in table 20-14.

Table 20-14 CTAs operation to the original table lock example

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
Commit
	return results Commit

In the example above, simply reading the data from the Source_tab table is equivalent to executing a normal SELECT statement, which can be read with consistency. This is what Oracle does, and it achieves consistent reads with multiple versions of data implemented by MVCC technology, without requiring any locks on source_tab. We know that InnoDB also implemented multi-version data, the normal select consistency read, and no need to add any locks, but here InnoDB to Source_tab added a shared lock, and do not use multi-version data consistency read technology!

Why does MySQL want to do this? The reason for this is also to ensure the correctness of recovery and replication. Because it is not locked, if the other transaction has done an update to Source_tab during the execution of the above statement, it may result in an error in data recovery. To illustrate this, let's repeat the previous example by setting the value of the system variable Innodb_locks_unsafe_for_binlog to "on" (its default value is off) before Session_1 executes the transaction, as shown in table 20-15.

Table 20-15 Example of security problems caused by CTAs operation not to lock 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 committed, the record of the 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)
	The update action is submitted first Mysql> commit; Query OK, 0 rows affected (0.05 sec)
Insert Action Mysql> commit; Query OK, 0 rows affected (0.07 sec)	 
View data at this time, Target_tab can insert results before Source_tab Update, which conforms to 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)

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 contents of the Binlog are analyzed:

12345678910111213141516171819202122

You can see that in Binlog, the location of the update operation is in the insert ... Before select, if you use this binlog for database recovery, the result of recovery is inconsistent with the actual application logic, and if you replicate, it will cause the master-slave database to be inconsistent!

From the example above, it is not difficult to understand why MySQL is processing "Insert into Target_tab select * from Source_tab where ..." and "CREATE TABLE New_tab ... select". . From Source_tab where ... "When you want to lock source_tab, instead of using multiple versions of data with minimal concurrency impact, you can achieve consistent reads. In particular, if the select of the above statement is a range condition, InnoDB also adds a gap lock (Next-lock) to the source table.

Therefore, INSERT ... SELECT ... and CREATE TABLE ... SELECT ... statement, which may prevent concurrent updates to the source table, causing a wait on the source table lock. If the query is more complex, it will cause serious performance problems, we should try to avoid using in the application. In fact, MySQL is not recommended for SQL, which is called non-deterministic.

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

The first is to take the practice in the above example and set the value of Innodb_locks_unsafe_for_binlog to "on", forcing MySQL to use multiple versions of data consistency reading. But the price paid is that it may not be possible to recover or replicate the data correctly with Binlog, so this is not recommended.

The second is by using the "SELECT * from Source_tab ... into outfile "and" Load data infile ... "statement combination to indirectly implement, in this way MySQL will not give Source_tab lock

mysql-Lock (2)