How to understand the row locking mode of mysql innodb
This article focuses on "how to understand the row locking mode of mysql innodb". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to understand the row locking mode of mysql innodb".
The real way is to find the corresponding row based on the index (instead of using all the where conditions to find the corresponding row) and then lock it based on the row data.
Root@sakila 10:27:15 > show indexes from tab_no_index\ G
* * 1. Row *
Table: tab_no_index
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
* 2. Row * *
Table: tab_no_index
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 6
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
Session 1:
Root@sakila 10:26:17 > select * from tab_no_index where id=1 for update
+-+ +
| | id | name |
+-+ +
| | 1 | 1 |
| | 1 | 4 |
+-+ +
2 rows in set (0.00 sec)
Session 2: this record of id=1 and name=4 was found through name index. I tried to lock it, but it was not successful because it was already locked by session 1.
Root@sakila 10:27:04 > select * from tab_no_index where name='4' for update
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
At this point, I believe you have a deeper understanding of "how to understand the row locking mode of mysql innodb". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
