In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
Mysql in how to achieve InnoDB row lock, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.
Mysql InnoDB Row Lock implementation
InnoDB row locking is achieved by locking the index items on the index, unlike Oracle, which is achieved by locking the corresponding data rows in the data block. The row lock implementation feature of InnoDB means that InnoDB uses row-level locks only if the data is retrieved by index conditions, otherwise InnoDB will use table locks!
In practical applications, special attention should be paid to this feature of InnoDB row locks, otherwise it may lead to a large number of lock conflicts, thus affecting concurrency performance. The following is illustrated by some practical examples.
(1) InnoDB does use table locks instead of row locks when querying without index conditions.
In the example shown in Table 20-9, the tab_no_index table does not have an index at first:
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), (2) (2), (3), (4)
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
Table 20-9 example of using table locks for InnoDB storage engine tables without indexes
Www.2cto.com
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)
Www.2cto.com
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.
Create a tab_with_ index table. The id field has a normal 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
Table 20-10 examples of InnoDB storage engine tables using row locks when using indexes
Www.2cto.com
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:
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)
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)
Table 20-11 blocking examples of the InnoDB storage engine using the same index key
Www.2cto.com
Session_1
Session_2
Mysql > 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 a different record than session_1, because it uses the same index, you need to wait for the lock:
Mysql > select * from tab_with_index where id = 1 and name ='4' for update
wait for
(3) when the table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB will use row locks to lock the data, whether using primary key indexes, unique indexes, or normal indexes.
Www.2cto.com
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:
Mysql > alter table tab_with_index add index name (name)
Query OK, 5 rows affected (0.23 sec)
Records: 5 Duplicates: 0 Warnings: 0
Table 20-12 blocking examples of InnoDB storage engine tables using different indexes
Session_1
Session_2
Mysql > 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)
Www.2cto.com
Session_2 uses name's index to access the record, and because the record is not indexed, the lock can be obtained:
Mysql > select * from tab_with_index where name ='2' for update
+-+ +
| | id | name |
+-+ +
| | 2 | 2 |
+-+ +
1 row in set (0.00 sec)
Since the accessed record has been locked by session_1, wait for the lock to be acquired. :
Mysql > select * from tab_with_index where name ='4' for update
(4) even if the index field is used in the condition, whether or not to use the index to retrieve data is decided by MySQL by judging the cost of different execution plans. If MySQL thinks that a full table scan is more efficient, for example, for some very small tables, it will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don't forget to check the execution plan of SQL to confirm that indexes are actually used. For a detailed discussion of when MySQL does not use indexes, see the introduction to the "indexing issues" section of this chapter.
Www.2cto.com
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.
In the example, the name field of the tab_with_index table has an index, but the name field is of type varchar. If the where condition is not compared with the varchar type, the name will be converted and the full table scan will be performed.
Mysql > alter table tab_no_index add index name (name)
Query OK, 4 rows affected (8.06sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql > explain select * from tab_with_index where name = 1\ G
* * 1. Row *
Id: 1
Select_type: SIMPLE
Table: tab_with_index
Type: ALL
Possible_keys: name
Key: NULL
Key_len: NULL
Ref: NULL
Rows: 4
Extra: Using where
1 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: SIMPLE
Table: tab_with_index
Type: ref
Possible_keys: name
Key: name
Key_len: 23
Ref: const
Rows: 1
Extra: Using where
1 row in set (0.00 sec)
Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
ORACLE_HOME/network/admin/Status: Failure-Test failed: Listener refused the connection with the fo
© 2024 shulou.com SLNews company. All rights reserved.