Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.