Mysql InnoDB Line Lock Implementation method

Mysql InnoDB Line Lock Implementation method

 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, pay special attention to this feature of the InnoDB row lock, otherwise it may result in a large number of lock collisions, which can affect 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 Start Tab_no_index table has no index:mysql> CREATE TABLE Tab_no_index (ID int,name varchar) 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 table 20-9         Inno The table of the DB storage engine uses the table lock example   www.2cto.com  session_1session_2mysql> set autocommit=0 when the index is not used; 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)    www.2cto.com  mysql> select * from Tab_no_index where id = 2 for update; Waiting in the example shown in table 20-9, it appears that session_1 only has an exclusive lock on one line, but Session_2 waits for 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.   Create Tab_with_index table, ID field has normal index: mysql> CREATE TABLE Tab_with_index (ID int,name varchar) 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 table 20-10    INNODB storage engine tables when using indexes Use row lock example   www.2cto.com  session_1session_2mysql> 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 the row lock for MySQL is an index-plus lock, not a lock for record addition, it is a record that accesses a non-peer, but if you are using 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, the Name field is not indexed: mysql> ALTER TABLE tab_with_index DROP INDEX name; Query OK, 4 rows affected(0.22 sec) Records:4  duplicates:0  Warnings:0mysql> 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) Table 20-11    INNODB Storage Engine blocking example using the same index key   www.2cto.com &nbs P;session_1session_2mysql> 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, you need 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, in addition to using the primary key index , a unique index, or a normal index, InnoDB uses row locks to lock data.   www.2cto.com   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: mysql> ALTER TABLE Tab_with_index add Index name (name); Query OK, 5 rows affected (0.23 sec) records:5  duplicates:0  warnings:0 table 20-12    INNODB storage engine tables using different indexes Example of blocking            session_1          session_2mysql> 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)    www.2cto.com  session_2 Use the index of name to access records because records are not indexed , so you can get the lock:mysql> select * from tab_with_index where name = ' 2 ' for update;+------+------+| ID   | Name |+------+------+| 2    | 2    |+------+------+1 row in Set (0.00 sec)   Because the record accessed has been session_1 locked, so wait to getLock. :mysql> SELECT * from tab_with_index WHERE name = ' 4 ' for Update; (4) Even if an indexed field is used in the condition, But whether the index is used to retrieve the data is determined by the cost of MySQL judging different execution plans, and if MySQL considers the full table scan to be more efficient, such as for small tables, it will not use the index, in which case the INNODB will use a 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.   www.2cto.com   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 an index, which causes InnoDB to use table locks. We can clearly see this by checking the execution plan of two SQL with explain. The name field of the Tab_with_index table in the   example 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.  mysql> ALTER TABLE Tab_no_index add index name (name); Query OK, 4 rows affected (8.06 sec) records:4  duplicates:0  Warnings:0mysql> explain select * from Tab_wi Th_index WHERE name = 1 \g*************************** 1. Row ***************************id:1select_type:simpletable:tab_with_indextype:allpossible_keys:namekey:nullkey_ Len:nullref:nullrows:4extra:using Where1 Row in Set (0.00 sec) mysql> Explain select * from Tab_with_index where Nam E = ' 1 ' \g**************1. Row ***************************id:1  www.2cto.com  select_type:simpletable:tab_with_indextype: Refpossible_keys:namekey:namekey_len:23ref:constrows:1extra:using Where1 Row in Set (0.00 sec)

Mysql InnoDB Line Lock implementation