MySQL中的行級(jí)鎖定示例詳解

更新時(shí)間：2019年05月08日 11:05:11 作者：誠(chéng)信通天下

這篇文章主要給大家介紹了關(guān)于MySQL中行級(jí)鎖定的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

鎖是在執(zhí)行多線程時(shí)用于強(qiáng)行限定資源訪問(wèn)的同步機(jī)制，數(shù)據(jù)庫(kù)鎖根據(jù)鎖的粒度可分為行級(jí)鎖，表級(jí)鎖和頁(yè)級(jí)鎖

行級(jí)鎖

行級(jí)鎖是mysql中粒度最細(xì)的一種鎖機(jī)制，表示只對(duì)當(dāng)前所操作的行進(jìn)行加鎖，行級(jí)鎖發(fā)生沖突的概率很低，其粒度最小，但是加鎖的代價(jià)最大。行級(jí)鎖分為共享鎖和排他鎖。

特點(diǎn)：

開(kāi)銷大，加鎖慢，會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最大，并發(fā)性也高；

實(shí)現(xiàn)原理：

InnoDB行鎖是通過(guò)給索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，這一點(diǎn)mysql和oracle不同，后者是通過(guò)在數(shù)據(jù)庫(kù)中對(duì)相應(yīng)的數(shù)據(jù)行加鎖來(lái)實(shí)現(xiàn)的，InnoDB這種行級(jí)鎖決定，只有通過(guò)索引條件來(lái)檢索數(shù)據(jù)，才能使用行級(jí)鎖，否則，直接使用表級(jí)鎖。特別注意:使用行級(jí)鎖一定要使用索引

舉個(gè)栗子：

創(chuàng)建表結(jié)構(gòu)

CREATE TABLE `developerinfo` (
 `userID` bigint(20) NOT NULL,
 `name` varchar(255) DEFAULT NULL,
 `passWord` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`userID`),
 KEY `PASSWORD_INDEX` (`passWord`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入數(shù)據(jù)

INSERT INTO `developerinfo` VALUES ('1', 'liujie', '123456');
INSERT INTO `developerinfo` VALUES ('2', 'yitong', '123');
INSERT INTO `developerinfo` VALUES ('3', 'tong', '123456');

（1）通過(guò)主鍵索引來(lái)查詢數(shù)據(jù)庫(kù)使用行鎖

打開(kāi)三個(gè)命令行窗口進(jìn)行測(cè)試

命令行窗口1	命令行窗口2	命令行窗口3

mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set 
|mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;
等待
|mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '3' for update;
+--------+------+----------+
| userID | name | passWord |
+--------+------+----------+
| 3 | tong | 123456 |
+--------+------+----------+
1 row in set
|mysql> commit;
Query OK, 0 rows affected mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set

（2）查詢非索引的字段來(lái)查詢數(shù)據(jù)庫(kù)使用行鎖

打開(kāi)兩個(gè)命令行窗口進(jìn)行測(cè)試

命令行窗口1	命令行窗口2

|mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where name = 'liujie' for update;
+--------+--------+----------+
userID name passWord
+--------+--------+----------+
1 liujie 123456
+--------+--------+----------+
1 row in set |mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where name = 'tong' for update;
等待|
mysql> commit;
Query OK, 0 rows affected mysql> select * from developerinfo where name = 'liujie' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set

##### （3）查詢非唯一索引字段來(lái)查詢數(shù)據(jù)庫(kù)使用行鎖鎖住多行

mysql的行鎖是針對(duì)索引假的鎖，不是針對(duì)記錄，所以可能會(huì)出現(xiàn)鎖住不同記錄的場(chǎng)景

打開(kāi)三個(gè)命令行窗口進(jìn)行測(cè)試

命令行窗口1 命令行窗口2 命令行窗口3

mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select * from developerinfo where password = '123456
' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 1 | liujie | 123456 |
| 3 | tong | 123456 |
+--------+--------+----------+
2 rows in set mysql> set autocommit =0 ;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '1' for update;

等待

mysql> set autocommit = 0;
Query OK, 0 rows affected
mysql> select * from developerinfo where userid = '2
' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 2 | yitong | 123 |
+--------+--------+----------+
1 row in set
commit; mysql> select * from developerinfo where userid = '1' for update;
+--------+--------+----------+
| userID | name | passWord |
+--------+--------+----------+
| 1 | liujie | 123456 |
+--------+--------+----------+
1 row in set