對MySQL行鎖的深入研究

作者：佚名 2010-05-31 17:45:50

此文章主要介紹的MySQL行鎖的深入研究，如果你對MySQL行鎖的相關(guān)的實際操作有興趣的話，你就可以對以下的文章點擊觀看了。

以下的文章主要向大家介紹的是MySQL行鎖的深入研究，前今天我們在做項目時因為業(yè)務(wù)邏輯的需要，我們應對數(shù)據(jù)表的一行或多行加入MySQL行鎖，舉個最簡單的例子，圖書借閱系統(tǒng)。假設(shè) id=1 的這本書庫存為 1 ，但是有 2 個人同時來借這本書，此處的邏輯為，

view plaincopy to clipboardprint?  
Select restnum from book where id =1 ;

如果 restnum 大于 0 ，執(zhí)行 update

Update book set restnumrestnum=restnum-1 where id=1 ;

問題就來了，當 2 個人同時來借的時候，有可能第一個人執(zhí)行 select 語句的時候，第二個人插了進來，在第一個人沒來得及更新 book 表的時候，第二個人查到數(shù)據(jù)了，其實是臟數(shù)據(jù)，因為第一個人會把 restnum 值減 1 ，因此第二個人本來應該是查到 id=1 的書 restnum 為 0 了，因此不會執(zhí)行 update ，而會告訴它 id=1 的書沒有庫存了，可是數(shù)據(jù)庫哪懂這些，數(shù)據(jù)庫只負責執(zhí)行一條條 SQL 語句，它才不管中間有沒有其他 sql 語句插進來，它也不知道要把一個 session 的 sql 語句執(zhí)行完再執(zhí)行另一個 session 的。

因此會導致并發(fā)的時候 restnum 最后的結(jié)果為 -1 ，顯然這是不合理的，所以，才出現(xiàn)鎖的概念， Mysql行鎖使用 innodb 引擎可以通過索引對數(shù)據(jù)行加鎖。以上借書的語句變?yōu)椋?/p>

view plaincopy to clipboardprint?  
Begin;   
Select restnum from book where id =1 for update ;

給 id=1 的行加上排它鎖且 id 有索引

Update book set restnumrestnum=restnum-1 where id=1 ;   
Commit;

這樣，第二個人執(zhí)行到 select 語句的時候就會處于等待狀態(tài)直到第一個人執(zhí)行 commit 。從而保證了第二個人不會讀到第一個人修改前的數(shù)據(jù)。

那這樣是不是萬無一失了呢，答案是否定的。看下面的例子。

跟我一步一步來，先建立表

view plaincopy to clipboardprint?  
CREATE TABLE `book` (   
`id` int(11) NOT NULL auto_increment,   
`num` int(11) default NULL,   
`name` varchar(0) default NULL,   
PRIMARY KEY (`id`),   
KEY `asd` (`num`)   
) ENGINE=InnoDB DEFAULT CHARSET=gbk

其中 num 字段加了索引

然后插入數(shù)據(jù)，運行，

view plaincopy to clipboardprint?  
insert into book(num) values(11),(11),(11),(11),(11);   
insert into book(num) values(22),(22),(22),(22),(22);

然后打開 2 個 mysql行鎖控制臺窗口，其實就是建立 2 個 session 做并發(fā)操作

在第一個 session 里運行：

begin;   
select * from book where num=11 for update;

出現(xiàn)結(jié)果：

+----+-----+------+   
| id | num | name |   
+----+-----+------+   
| 11 | 11 | NULL |   
| 12 | 11 | NULL |   
| 13 | 11 | NULL |   
| 14 | 11 | NULL |   
| 15 | 11 | NULL |   
+----+-----+------+   
5 rows in set

然后在第二個 session 里運行：

begin;   
select * from book where num=22 for update;

出現(xiàn)結(jié)果：

+----+-----+------+   
| id | num | name |   
+----+-----+------+   
| 16 | 22 | NULL |   
| 17 | 22 | NULL |   
| 18 | 22 | NULL |   
| 19 | 22 | NULL |   
| 20 | 22 | NULL |   
+----+-----+------+   
5 rows in set

好了，到這里什么問題都沒有，是吧，可是接下來問題就來了，大家請看：

回到第一個 session ，運行：

update book set name='abc' where num=11;

問題來了， session 竟然處于等待狀態(tài) ，可是 num=11 的行不是被第一個 session 自己鎖住的么，為什么不能更新呢？好了，打這里大家也許有自己的答案，先別急，再請看一下操作。

把 2 個 session 都關(guān)閉，然后運行：

view plaincopy to clipboardprint?  
delete from book where num=11 limit 3;   
delete from book where num=22 limit 3;

其實就是把 num=11 和 22 的記錄各刪去 3 行，

然后重復 “***********************” 之間的操作

竟然發(fā)現(xiàn)，運行 update book set name='abc' where num=11; 后，有結(jié)果出現(xiàn)了，說明沒有被鎖住，

這是為什么呢，難道 2 行數(shù)據(jù)和 5 行數(shù)據(jù)，對 MySQL 來說，會產(chǎn)生鎖行和鎖表兩種情況嗎。經(jīng)過跟網(wǎng)友討論和翻閱資料，仔細分析后發(fā)現(xiàn)：

在以上實驗數(shù)據(jù)作為測試數(shù)據(jù)的情況下，由于 num 字段重復率太高，只有 2 個值，分別是 11 和 12. 而數(shù)據(jù)量相對于這兩個值來說卻是比較大的，是 10 條， 5 倍的關(guān)系。

那么 mysql 在解釋 sql 的時候，會忽略索引，因為它的優(yōu)化器發(fā)現(xiàn)：即使使用了索引，還是要做全表掃描，故而放棄了索引，也就沒有使用行鎖，卻使用了表鎖。簡單的講，就是 MYSQL 無視了你的索引，它覺得與其行鎖，還不如直接表鎖，畢竟它覺得表鎖所花的代價比MySQL行鎖來的小。以上問題即便你使用了 force index 強制索引，結(jié)果還是一樣，永遠都是表鎖。

所以 mysql 的行鎖用起來并不是那么隨心所欲的，必須要考慮索引。再看下面的例子。

view plaincopy to clipboardprint?  
select id from items where id in (select id from items where id <6) for update;

--id字段加了索引

select id from items where id in (1,2,3,4,5) for update;

大部分會認為結(jié)果一樣沒什么區(qū)別，其實差別大了，區(qū)別就是第一條 sql 語句會產(chǎn)生表鎖，而第二個 sql 語句是MySQL行鎖，為什么呢？因為第一個 sql 語句用了子查詢外圍查詢故而沒使用索引，導致表鎖。

好了，回到借書的例子，由于 id 是唯一的，所以沒什么問題，但是如果有些表出現(xiàn)了索引有重復值，并且 mysql 會強制使用表鎖的情況，那怎么辦呢？一般來說只有重新設(shè)計表結(jié)構(gòu)和用新的 SQL 語句實現(xiàn)業(yè)務(wù)邏輯，但是其實上面借書的例子還有一種辦法。請看下面代碼：

view plaincopy to clipboardprint?  
Set sql_mode=   
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';   
Begin;   
Select restnum from book where id =1 ; -- 取消排它鎖 , 設(shè)置 restnum 為 unsigned   
Update book set restnumrestnum=restnum-1 where id=1 ;   
If(update 執(zhí)行成功 ) commit;   
Else rollback;

上面是個小技巧，通過把數(shù)據(jù)庫模式臨時設(shè)置為嚴格模式，當 restnum 被更新為 -1 的時候，由于 restnum 是 unsigned 類型的，因此 update 會執(zhí)行失敗，無論第二個 session 做了什么數(shù)據(jù)庫操作，都會被回滾，從而確保了數(shù)據(jù)的正確性，這個目的只是為了防止并發(fā)的時候極小概率出現(xiàn)的 2 個 session 的 sql 語句嵌套執(zhí)行導致數(shù)據(jù)臟讀。

當然最好的辦法還是修改表結(jié)構(gòu)和 sql 語句，讓 MYSQL 通過索引來加MySQL行鎖, MySQL 測試版本為 5.0.75-log 和 5.1.36-community

【編輯推薦】

MySQL基本操作，新手入門寶典
使用MySQL 數(shù)據(jù)庫出現(xiàn)的困難解決
MySQL mysqldump命令的正確應用
MySQL移植問題的正確解決方案的描述
MySQL使用方法的經(jīng)驗大匯總

責任編輯：佚名來源： cnblogs

MySQL行鎖