程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL行鎖深入研究

MySQL行鎖深入研究

編輯:關於MYSQL數據庫

做項目時由於業務邏輯的需要,必須對數據表的一行或多行加入行鎖,舉個最簡單的例子,圖書借閱系統。假設 id=1 的這本書庫存為 1 ,但是有 2 個人同時來借這本書,此處的邏輯為

vIEw plaincopy to clipboardprint?

  1. Select   restnum  from  book  where  id =1 ;     
  2. -- 如果 restnum 大於 0 ,執行 update 
  3. Update   book  set restnum=restnum-1 where id=1 ;  

問題就來了,當 2 個人同時來借的時候,有可能第一個人執行 select 語句的時候,第二個人插了進來,在第一個人沒來得及更新 book 表的時候,第二個人查到數據了,其實是髒數據,因為第一個人會把 restnum 值減 1 ,因此第二個人本來應該是查到 id=1 的書 restnum 為 0 了,因此不會執行 update ,而會告訴它 id=1 的書沒有庫存 了,可是數據庫哪懂這些,數據庫只負責執行一條條 SQL 語句,它才不管中間有沒有其他 sql 語句插進來,它也不知道要把一個 session 的 sql 語句執行完再執行另一個 session 的。因此會導致並發的時候 restnum 最後的結果為 -1 ,顯然這是不合理的,所以,才出現鎖的概念, MySQL 使用 innodb 引擎可以通過索引 對數據行加鎖。以上借書的語句變為:

vIEw plaincopy to clipboardprint?

  1. Begin; 
  2. Select   restnum  from  book  where  id =1  for   update ; 
  3. -- 給 id=1 的行加上排它鎖且 id 有索引 
  4. Update   book  set restnum=restnum-1 where id=1 ; 
  5. Commit;  

這樣,第二個人執行到 select 語句的時候就會處於等待狀態直到第一個人執行 commit 。從而保證了第二個人不會讀到第一個人修改前的數據。

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

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

vIEw plaincopy to clipboardprint?

  1. CREATE TABLE `book` (  
  2.   `id` int(11) NOT NULL auto_increment,  
  3.   `num` int(11) default NULL,  
  4.   `name` varchar(0) default NULL,  
  5.   PRIMARY KEY  (`id`),  
  6.   KEY `asd` (`num`)  
  7. ) ENGINE=InnoDB  DEFAULT CHARSET=gbk  

其中 num 字段加了索引
然後插入數據,運行,

vIEw plaincopy to clipboardprint?

  1. insert into book(num) values(11),(11),(11),(11),(11);  
  2. insert into book(num) values(22),(22),(22),(22),(22);  

然後打開 2 個 MySQL 控制台窗口,其實就是建立 2 個 session 做並發操作
********************************************************************
在第一個 session 裡運行:
begin;
select * from book where num=11 for update;
出現結果:
+----+-----+------+
| 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;
出現結果:
+----+-----+------+
| 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 竟然處於等待狀態 ,可是 num=11 的行不是被第一個 session 自己鎖住的麼,為什麼不能更新呢?好了,打這裡大家也許有自己的答案,先別急,再請看一下操作。
把 2 個 session 都關閉,然後運行:

vIEw plaincopy to clipboardprint?

  1. delete from book where num=11 limit 3;  
  2. delete from book where num=22 limit 3;  

其實就是把 num=11 和 22 的記錄各刪去 3 行,
然後重復 “***********************” 之間的操作
竟然發現,運行 update book set name='abc' where num=11; 後,有結果出現了,說明沒有被鎖住,
這是為什麼呢,難道 2 行數據和 5 行數據,對 MySQL 來說,會產生鎖行和鎖表兩種情況嗎 。經過跟網友討論和翻閱資料,仔細分析後發現:

在以上實驗數據作為測試數據的情況下,由於 num 字段重復率太高,只有 2 個值,分別是 11 和 12. 而數據量相對於這兩個值來說卻是比較大的,是 10 條, 5 倍的關系。
那麼 mysql 在解釋 sql 的時候,會忽略索引,因為它的優化器發現:即使使用了索引,還是要做全表掃描,故而放棄了索引,也就沒有使用行鎖,卻使用了表鎖。 簡單的講,就是 MySQL 無視了你的索引,它覺得與其行鎖,還不如直接表鎖,畢竟它覺得表鎖所花的代價比行鎖來的小。以上問題即便你使用了 force index 強制索引,結果還是一樣,永遠都是表鎖。

所以 MySQL 的行鎖用起來並不是那麼隨心所欲的,必須要考慮索引。再看下面的例子。

vIEw plaincopy to clipboardprint?

  1. select id from items where id in (select id from items where id <6) for update;   
  2. --id字段加了索引 
  3. select id from items where id in (1,2,3,4,5) for update; 

大部分會認為結果一樣沒什麼區別,其實差別大了,區別就是第一條 sql 語句會產生表鎖,而第二個 sql 語句是行鎖,為什麼呢?因為第一個 sql 語句用了子查詢外圍查詢故而沒使用索引,導致表鎖。

好了,回到借書的例子,由於 id 是唯一的,所以沒什麼問題,但是如果有些表出現了索引有重復值,並且 MySQL 會強制使用表鎖的情況,那怎麼辦呢?一般來說只有重新設計表結構和用新的 SQL 語句實現業務邏輯,但是其實上面借書的例子還有一種辦法。請看下面代碼:

vIEw plaincopy to clipboardprint?

  1. Set   sql_mode= 
  2. 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 
  3. Begin; 
  4. Select  restnum  from  book  where  id =1   ;  -- 取消排它鎖 , 設置 restnum 為 unsigned 
  5. Update   book  set restnum=restnum-1 where id=1 ; 
  6. If(update 執行成功 )  commit; 
  7. Else   rollback;  

上面是個小技巧,通過把數據庫模式臨時設置為嚴格模式,當 restnum 被更新為 -1 的時候,由於 restnum 是 unsigned 類型的,因此 update 會執行失敗,無論第二個 session 做了什麼數據庫操作,都會被回滾,從而確保了數據的正確性,這個目的只是為了防止並發的時候極小概率出現的 2 個 session 的 sql 語句嵌套執行導致數據髒讀。當然最好的辦法還是修改表結構和 sql 語句,讓 MYSQL 通過索引來加行鎖, MySQL 測試版本為 5.0.75-log 和 5.1.36-community

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved