Mysql InnoDB row lock Implementation Method

Mysql InnoDB row lock implementation method InnoDB row lock is 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: 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 tables 20-9 InnoDB Storage engine tables use table lock when no indexes are used. Example: www.2cto.com session_1session_2mysql> 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) www.2cto.com mysql> select * from tab_no_index where id = 2 for update; In the example shown in Table 20-9, session_1 only adds an exclusive lock to one row, however, when session_2 requests an exclusive lock from other rows, the lock waits! 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: 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 tables 20-10 InnoDB Storage engine tables use row lock when using indexes 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 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: 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) Example of blocking when table 20-11 InnoDB Storage engine uses the same index key: www.2cto.com 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 records different from session_1, because the same index is used Waiting for the lock: mysql> select * from tab_with_index where id = 1 and name = '4' for update; waiting (3) when the table has 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 indexes, unique indexes, or common indexes. Www.2cto.com In the example shown in table 20-12, the id field of the tab_with_index table has a primary key index, and the name field has a common 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 tables 20-12 InnoDB Storage engine tables use different index blocking example · 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_ind Ex where id = 1 for update; + ------ + | id | name | + ------ + | 1 | 1 | 1 | 4 | + ------ + 2 rows in set (0.00 sec) www.2cto.com Session_2 uses the name index to access records. Because the records are not indexed, you can obtain the lock: 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 you are waiting to get the lock.: Mysql> select * from tab_with_index where name = '4' for update; (4) even if the index field is used in the condition, however, whether to use indexes to retrieve data is determined by MySQL's cost of determining different execution plans. If MySQL considers that full table scan is more efficient, such as for some small tables, in this case, InnoDB uses 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 www.2cto.com, 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 indexes, resulting in InnoDB using Table locks. 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. 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: 1select_type: SIMPLEtable: tab_with_indextype: ALLpossible_keys: namekey: NULLkey_len: NULLref: NULLrows: 4 Extra: Using where1 row in set (0.00 sec) mysql> explain select * from tab_with_index where name = '1' \ G ************************* ** 1. row ************************* id: 1 www.2cto.com select_type: SIMPLEtable: tab_with_indextype: refpossible_keys: namekey: namekey_len: 23ref: constrows: 1 Extra: Using where1 row in set (0.00 sec) from the CS programmer window