Implementation method of InnoDB Row Lock
This article mainly explains "the implementation of InnoDB row lock". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the implementation of InnoDB row locks"!
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 for
In the example shown in Table 20-9, it looks like session_1 only adds an exclusive lock to one row, but session_2 waits for a lock when it requests an exclusive lock for another row! The reason is that without an index, InnoDB can only use table locks. When we add an index to it, InnoDB locks only eligible rows, as shown in tables 20-10.
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) because the row lock of MySQL is a lock for index, not a lock for records, although it accesses records of different rows, lock conflicts will occur if the same index key is used. You should pay attention to this when applying design.
In the example shown in tables 20-11, the id field of table tab_with_index has an index, and the name field has no index:
Table 20-11 blocking examples of the InnoDB storage engine using the same index key
In the example shown in tables 20-12, the id field of table tab_with_index has a primary key index and the name field has a normal index:
Table 20-12 blocking examples of InnoDB storage engine tables using different indexes
In the following example, the data type of the retrieval value is different from that of the index field, and although MySQL can convert the data type, it does not use the index, which causes InnoDB to use table locks. We can clearly see this by examining the execution plans of the two SQL with explain.
