Mysql中怎么實(shí)現(xiàn) InnoDB行鎖

195次閱讀

共計(jì) 5013 個(gè)字符，預(yù)計(jì)需要花費(fèi) 13 分鐘才能閱讀完成。

Mysql 中怎么實(shí)現(xiàn) InnoDB 行鎖，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來學(xué)習(xí)下，希望你能有所收獲。

Mysql InnoDB 行鎖實(shí)現(xiàn)方式

InnoDB 行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn) MySQL 與 Oracle 不同，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB 這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB 才使用行級(jí)鎖，否則，InnoDB 將使用表鎖！

在實(shí)際應(yīng)用中，要特別注意 InnoDB 行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。下面通過一些實(shí)際例子來加以說明。
（1）在不通過索引條件查詢的時(shí)候，InnoDB 確實(shí)使用的是表鎖，而不是行鎖。

在如表 20- 9 所示的例子中，開始 tab_no_index 表沒有索引：
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
表 20-9 InnoDB 存儲(chǔ)引擎的表在不使用索引時(shí)使用表鎖例子
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;
等待
在如表 20- 9 所示的例子中，看起來 session_1 只給一行加了排他鎖，但 session_2 在請(qǐng)求其他行的排他鎖時(shí)，卻出現(xiàn)了鎖等待！原因就是在沒有索引的情況下，InnoDB 只能使用表鎖。當(dāng)我們給其增加一個(gè)索引后，InnoDB 就只鎖定了符合條件的行，如表 20-10 所示。

創(chuàng)建 tab_with_index 表，id 字段有普通索引：

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
表 20-10 InnoDB 存儲(chǔ)引擎的表在使用索引時(shí)使用行鎖例子
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）由于 MySQL 的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的。應(yīng)用設(shè)計(jì)的時(shí)候要注意這一點(diǎn)。

在如表 20-11 所示的例子中，表 tab_with_index 的 id 字段有索引，name 字段沒有索引：

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 sec)
表 20-11 InnoDB 存儲(chǔ)引擎使用相同索引鍵的阻塞例子
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)

雖然 session_2 訪問的是和 session_1 不同的記錄，但是因?yàn)槭褂昧讼嗤乃饕孕枰却i：
mysql select * from tab_with_index where id = 1 and name = 4 for update;
等待
（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖。
www.2cto.com
在如表 20-12 所示的例子中，表 tab_with_index 的 id 字段有主鍵索引，name 字段有普通索引：

mysql alter table tab_with_index add index name(name);
Query OK, 5 rows affected (0.23 sec)
Records: 5 Duplicates: 0 Warnings: 0
表 20-12 InnoDB 存儲(chǔ)引擎的表使用不同索引的阻塞例子

middot; session_1
middot; 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 使用 name 的索引訪問記錄，因?yàn)橛涗洓]有被索引，所以可以獲得鎖：
mysql select * from tab_with_index where name = 2 for update;
+——+——+
| id | name |
+——+——+
| 2 | 2 |
+——+——+
1 row in set (0.00 sec)

由于訪問的記錄已經(jīng)被 session_1 鎖定，所以等待獲得鎖。：
mysql select * from tab_with_index where name = 4 for update;
（4）即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的，如果 MySQL 認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表，它就不會(huì)使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時(shí)，別忘了檢查 SQL 的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。關(guān)于 MySQL 在什么情況下不使用索引的詳細(xì)討論，參見本章“索引問題”一節(jié)的介紹。
www.2cto.com
在下面的例子中，檢索值的數(shù)據(jù)類型與索引字段不同，雖然 MySQL 能夠進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，但卻不會(huì)使用索引，從而導(dǎo)致 InnoDB 使用表鎖。通過用 explain 檢查兩條 SQL 的執(zhí)行計(jì)劃，我們可以清楚地看到了這一點(diǎn)。

例子中 tab_with_index 表的 name 字段有索引，但是 name 字段是 varchar 類型的，如果 where 條件中不是和 varchar 類型進(jìn)行比較，則會(huì)對(duì) name 進(jìn)行類型轉(zhuǎn)換，而執(zhí)行的全表掃描。

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: 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)