MysqlInnoDB row lock implementation method _ MySQL

MysqlInnoDB row lock implementation method bitsCN.com
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 the table lock example 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) mysql> select * from tab_no_index where id = 2 for update; waiting in the example shown in Table 20-9, it seems that 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 the row lock example session_1session_2mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) when using indexes) mysql> select * from tab_wit H_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 For example, 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, you need to wait for the lock: mysql> se Lect * from tab_with_index where id = 1 and name = '4' for update; wait (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. 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: 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_index where id = 1 for update; + ------ + | id | name | + ------ + | 1 | 1 | 1 | 4 | + ------ + 2 rows in set (0.00 sec) session_2 uses name index 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, 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. 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 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 bitsCN.com