建站
咨詢
售后
建站咨詢
新聞中心
以下的文章主要向大家介紹的是MySQL行鎖的深入研究，前今天我們?cè)谧鲰?xiàng)目時(shí)因?yàn)闃I(yè)務(wù)邏輯的需要，我們應(yīng)對(duì)數(shù)據(jù)表的一行或多行加入MySQL行鎖，舉個(gè)最簡(jiǎn)單的例子，圖書借閱系統(tǒng)。假設(shè) id=1 的這本書庫(kù)存為 1 ，但是有 2 個(gè)人同時(shí)來(lái)借這本書，此處的邏輯為，
創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、網(wǎng)站建設(shè)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)、伊犁網(wǎng)絡(luò)推廣、微信小程序定制開(kāi)發(fā)、伊犁網(wǎng)絡(luò)營(yíng)銷、伊犁企業(yè)策劃、伊犁品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供伊犁建站搭建服務(wù)，24小時(shí)服務(wù)熱線：18980820575，官方網(wǎng)址：www.cdcxhl.com
- view plaincopy to clipboardprint?
- Select restnum from book where id =1 ;
如果 restnum 大于 0 ，執(zhí)行 update
- Update book set restnumrestnum=restnum-1 where id=1 ;
問(wèn)題就來(lái)了，當(dāng) 2 個(gè)人同時(shí)來(lái)借的時(shí)候，有可能第一個(gè)人執(zhí)行 select 語(yǔ)句的時(shí)候，第二個(gè)人插了進(jìn)來(lái)，在第一個(gè)人沒(méi)來(lái)得及更新 book 表的時(shí)候，第二個(gè)人查到數(shù)據(jù)了，其實(shí)是臟數(shù)據(jù)，因?yàn)榈谝粋€(gè)人會(huì)把 restnum 值減 1 ，因此第二個(gè)人本來(lái)應(yīng)該是查到 id=1 的書 restnum 為 0 了，因此不會(huì)執(zhí)行 update ，而會(huì)告訴它 id=1 的書沒(méi)有庫(kù)存 了，可是數(shù)據(jù)庫(kù)哪懂這些，數(shù)據(jù)庫(kù)只負(fù)責(zé)執(zhí)行一條條 SQL 語(yǔ)句，它才不管中間有沒(méi)有其他 sql 語(yǔ)句插進(jìn)來(lái)，它也不知道要把一個(gè) session 的 sql 語(yǔ)句執(zhí)行完再執(zhí)行另一個(gè) session 的。
因此會(huì)導(dǎo)致并發(fā)的時(shí)候 restnum 最后的結(jié)果為 -1 ，顯然這是不合理的，所以，才出現(xiàn)鎖的概念， Mysql行鎖 使用 innodb 引擎可以通過(guò)索引 對(duì)數(shù)據(jù)行加鎖。以上借書的語(yǔ)句變?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;
這樣，第二個(gè)人執(zhí)行到 select 語(yǔ)句的時(shí)候就會(huì)處于等待狀態(tài)直到第一個(gè)人執(zhí)行 commit 。從而保證了第二個(gè)人不會(huì)讀到第一個(gè)人修改前的數(shù)據(jù)。
那這樣是不是萬(wàn)無(wú)一失了呢，答案是否定的。看下面的例子。
跟我一步一步來(lái)，先建立表
- 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ù)，運(yùn)行，
- view plaincopy to clipboardprint?
- insert into book(num) values(11),(11),(11),(11),(11);
- insert into book(num) values(22),(22),(22),(22),(22);
然后打開(kāi) 2 個(gè) mysql行鎖 控制臺(tái)窗口，其實(shí)就是建立 2 個(gè) session 做并發(fā)操作
在第一個(gè) session 里運(yùn)行：
- 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
然后在第二個(gè) session 里運(yùn)行：
- 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
好了，到這里什么問(wèn)題都沒(méi)有，是吧，可是接下來(lái)問(wèn)題就來(lái)了，大家請(qǐng)看：
回到第一個(gè) session ，運(yùn)行：
- update book set name='abc' where num=11;
問(wèn)題來(lái)了， session 竟然處于等待狀態(tài) ，可是 num=11 的行不是被第一個(gè) session 自己鎖住的么，為什么不能更新呢？好了，打這里大家也許有自己的答案，先別急，再請(qǐng)看一下操作。
把 2 個(gè) session 都關(guān)閉，然后運(yùn)行：
- view plaincopy to clipboardprint?
- delete from book where num=11 limit 3;
- delete from book where num=22 limit 3;
其實(shí)就是把 num=11 和 22 的記錄各刪去 3 行，
然后重復(fù) “***********************” 之間的操作
竟然發(fā)現(xiàn)，運(yùn)行 update book set name='abc' where num=11; 后，有結(jié)果出現(xiàn)了，說(shuō)明沒(méi)有被鎖住，
這是為什么呢，難道 2 行數(shù)據(jù)和 5 行數(shù)據(jù)，對(duì) MySQL 來(lái)說(shuō)，會(huì)產(chǎn)生鎖行和鎖表兩種情況嗎 。經(jīng)過(guò)跟網(wǎng)友討論和翻閱資料，仔細(xì)分析后發(fā)現(xiàn)：
在以上實(shí)驗(yàn)數(shù)據(jù)作為測(cè)試數(shù)據(jù)的情況下，由于 num 字段重復(fù)率太高，只有 2 個(gè)值，分別是 11 和 12. 而數(shù)據(jù)量相對(duì)于這兩個(gè)值來(lái)說(shuō)卻是比較大的，是 10 條， 5 倍的關(guān)系。
那么 mysql 在解釋 sql 的時(shí)候，會(huì)忽略索引，因?yàn)樗膬?yōu)化器發(fā)現(xiàn)：即使使用了索引，還是要做全表掃描，故而放棄了索引，也就沒(méi)有使用行鎖，卻使用了表鎖。 簡(jiǎn)單的講，就是 MYSQL 無(wú)視了你的索引，它覺(jué)得與其行鎖，還不如直接表鎖，畢竟它覺(jué)得表鎖所花的代價(jià)比MySQL行鎖來(lái)的小。以上問(wèn)題即便你使用了 force index 強(qiáng)制索引，結(jié)果還是一樣，永遠(yuǎn)都是表鎖。
所以 mysql 的行鎖用起來(lái)并不是那么隨心所欲的，必須要考慮索引。再看下面的例子。
- 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;
大部分會(huì)認(rèn)為結(jié)果一樣沒(méi)什么區(qū)別，其實(shí)差別大了，區(qū)別就是第一條 sql 語(yǔ)句會(huì)產(chǎn)生表鎖，而第二個(gè) sql 語(yǔ)句是MySQL行鎖，為什么呢？因?yàn)榈谝粋€(gè) sql 語(yǔ)句用了子查詢外圍查詢故而沒(méi)使用索引，導(dǎo)致表鎖。
好了，回到借書的例子，由于 id 是唯一的，所以沒(méi)什么問(wèn)題，但是如果有些表出現(xiàn)了索引有重復(fù)值，并且 mysql 會(huì)強(qiáng)制使用表鎖的情況，那怎么辦呢？一般來(lái)說(shuō)只有重新設(shè)計(jì)表結(jié)構(gòu)和用新的 SQL 語(yǔ)句實(shí)現(xiàn)業(yè)務(wù)邏輯，但是其實(shí)上面借書的例子還有一種辦法。請(qǐng)看下面代碼：
- 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;
上面是個(gè)小技巧，通過(guò)把數(shù)據(jù)庫(kù)模式臨時(shí)設(shè)置為嚴(yán)格模式，當(dāng) restnum 被更新為 -1 的時(shí)候，由于 restnum 是 unsigned 類型的，因此 update 會(huì)執(zhí)行失敗，無(wú)論第二個(gè) session 做了什么數(shù)據(jù)庫(kù)操作，都會(huì)被回滾，從而確保了數(shù)據(jù)的正確性，這個(gè)目的只是為了防止并發(fā)的時(shí)候極小概率出現(xiàn)的 2 個(gè) session 的 sql 語(yǔ)句嵌套執(zhí)行導(dǎo)致數(shù)據(jù)臟讀。
當(dāng)然最好的辦法還是修改表結(jié)構(gòu)和 sql 語(yǔ)句，讓 MYSQL 通過(guò)索引來(lái)加MySQL行鎖, MySQL 測(cè)試版本為 5.0.75-log 和 5.1.36-community
【編輯推薦】
- MySQL基本操作，新手入門寶典
- 使用MySQL 數(shù)據(jù)庫(kù)出現(xiàn)的困難解決
- MySQL mysqldump命令的正確應(yīng)用
- MySQL移植問(wèn)題的正確解決方案的描述
- MySQL使用方法的經(jīng)驗(yàn)大匯總
網(wǎng)站名稱：對(duì)MySQL行鎖的深入研究
鏈接分享：http://m.5511xx.com/article/ccseccd.html
