mysql鎖表機(jī)制的概述

這篇文章主要介紹“MySQL鎖表機(jī)制的概述”，在日常操作中，相信很多人在mysql鎖表機(jī)制的概述問題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”mysql鎖表機(jī)制的概述”的疑惑有所幫助！接下來，請(qǐng)跟著小編一起來學(xué)習(xí)吧！

成都創(chuàng)新互聯(lián)公司10多年企業(yè)網(wǎng)站建設(shè)服務(wù);為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計(jì)及高端網(wǎng)站定制服務(wù),企業(yè)網(wǎng)站建設(shè)及推廣,對(duì)成都圍欄護(hù)欄等多個(gè)領(lǐng)域擁有豐富的網(wǎng)站營(yíng)銷經(jīng)驗(yàn)的網(wǎng)站建設(shè)公司。

 
一、概述
 
MySQL有三種鎖的級(jí)別：頁級(jí)、表級(jí)、行級(jí)。
 
MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）；BDB存儲(chǔ)引擎采用的是頁面鎖（page-level
locking），但也支持表級(jí)鎖；InnoDB存儲(chǔ)引擎既支持行級(jí)鎖（row-level locking），也支持表級(jí)鎖，但默認(rèn)情況下是采用行級(jí)鎖。
 
MySQL這3種鎖的特性可大致歸納如下：
 
表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。
行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
 
二、MyISAM表鎖
 
MyISAM存儲(chǔ)引擎只支持表鎖，是現(xiàn)在用得最多的存儲(chǔ)引擎。
 
1、查詢表級(jí)鎖爭(zhēng)用情況
 
可以通過檢查table_locks_waited和table_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭(zhēng)奪：
mysql> show status like ‘table%’;
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 76939364 |
| Table_locks_waited | 305089 |
+———————–+———-+
2 rows in set (0.00 sec)Table_locks_waited的值比較高，說明存在著較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。
 
2、MySQL表級(jí)鎖的鎖模式
 
MySQL的表級(jí)鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨(dú)占寫鎖（Table Write
Lock）。MyISAM在執(zhí)行查詢語句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會(huì)自動(dòng)給涉及的表加寫鎖。
 
所以對(duì)MyISAM表進(jìn)行操作，會(huì)有以下情況：
 
a、對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。
b、對(duì)MyISAM表的寫操作（加寫鎖），會(huì)阻塞其他進(jìn)程對(duì)同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作。
 
下面通過例子來進(jìn)行驗(yàn)證以上觀點(diǎn)。數(shù)據(jù)表gz_phone里有二百多萬數(shù)據(jù)，字段id,phone,ua,day?，F(xiàn)在同時(shí)用多個(gè)客戶端同時(shí)對(duì)該表進(jìn)行操作分析。
 
a、當(dāng)我用客戶端1進(jìn)行一個(gè)比較長(zhǎng)時(shí)間的讀操作時(shí)，分別用客戶端2進(jìn)行讀和寫操作：
 
client1:
mysql>select count(*) from gz_phone group by ua;
75508 rows in set (3 min 15.87 sec) client2:
select id,phone from gz_phone limit 1000,10;
+——+——-+
| id | phone |
+——+——-+
| 1001 | 2222 |
| 1002 | 2222 |
| 1003 | 2222 |
| 1004 | 2222 |
| 1005 | 2222 |
| 1006 | 2222 |
| 1007 | 2222 |
| 1008 | 2222 |
| 1009 | 2222 |
| 1010 | 2222 |
+——+——-+
10 rows in set (0.01 sec)
mysql> update gz_phone set phone=’11111111111′where id=1001;
Query OK, 0 rows affected (2 min 57.88 sec)
Rows matched: 1 Changed: 0 Warnings: 0
 
說明當(dāng)數(shù)據(jù)表有一個(gè)讀鎖時(shí)，其它進(jìn)程的查詢操作可以馬上執(zhí)行，但更新操作需等待讀鎖釋放后才會(huì)執(zhí)行。
 
b、當(dāng)用客戶端1進(jìn)行一個(gè)較長(zhǎng)時(shí)間的更新操作時(shí)，用客戶端2,3分別進(jìn)行讀寫操作：
 
client1:
mysql> update gz_phone set phone=’11111111111′;
Query OK, 1671823 rows affected (3 min 4.03 sec)
Rows matched: 2212070 Changed: 1671823 Warnings: 0 client2:
mysql> select id,phone,ua,day from gz_phone limit 10;
+—-+——-+——————-+————+
| id | phone | ua | day |
+—-+——-+——————-+————+
| 1 | 2222 | SonyEricssonK310c | 2007-12-19 |
| 2 | 2222 | SonyEricssonK750c | 2007-12-19 |
| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | LENOVO-I750 | 2007-12-19 |
| 6 | 2222 | BIRD_D636 | 2007-12-19 |
| 7 | 2222 | SonyEricssonS500c | 2007-12-19 |
| 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 |
| 9 | 2222 | NokiaN73-1 | 2007-12-19 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+—-+——-+——————-+————+
10 rows in set (2 min 58.56 sec) client3:
mysql> update gz_phone set phone=’55555′where id=1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
說明當(dāng)數(shù)據(jù)表有一個(gè)寫鎖時(shí)，其它進(jìn)程的讀寫操作都需等待讀鎖釋放后才會(huì)執(zhí)行。
 
3、并發(fā)插入
 
原則上數(shù)據(jù)表有一個(gè)讀鎖時(shí)，其它進(jìn)程無法對(duì)此表進(jìn)行更新操作，但在一定條件下，MyISAM表也支持查詢和插入操作的并發(fā)進(jìn)行。
 
MyISAM存儲(chǔ)引擎有一個(gè)系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。
 
a、當(dāng)concurrent_insert設(shè)置為0時(shí)，不允許并發(fā)插入。
b、當(dāng)concurrent_insert設(shè)置為1時(shí)，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。
c、當(dāng)concurrent_insert設(shè)置為2時(shí)，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。
 
4、MyISAM的鎖調(diào)度
 
由于MySQL認(rèn)為寫請(qǐng)求一般比讀請(qǐng)求要重要，所以如果有讀寫請(qǐng)求同時(shí)進(jìn)行的話，MYSQL將會(huì)優(yōu)先執(zhí)行寫操作。這樣MyISAM表在進(jìn)行大量的更新操作時(shí)（特別是更新的字段中存在索引的情況下），會(huì)造成查詢操作很難獲得讀鎖，從而導(dǎo)致查詢阻塞。
 
我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM的調(diào)度行為：
 
a、通過指定啟動(dòng)參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請(qǐng)求以優(yōu)先的權(quán)利。
b、通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低。
c、通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級(jí)。
 
上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法。這里要說明的就是，不要盲目的給mysql設(shè)置為讀優(yōu)先，因?yàn)橐恍┬枰L(zhǎng)時(shí)間運(yùn)行的查詢操作，也會(huì)使寫進(jìn)程“餓死”。只有根據(jù)你的實(shí)際情況，來決定設(shè)置哪種操作優(yōu)先。這些方法還是沒有從根本上同時(shí)解決查詢和更新的問題。
 
在一個(gè)有大數(shù)據(jù)量高并發(fā)表的mysql里，我們還可采用另一種策略來進(jìn)行優(yōu)化，那就是通過mysql主從（讀寫）分離來實(shí)現(xiàn)負(fù)載均衡，這樣可避免優(yōu)先哪一種操作從而可能導(dǎo)致另一種操作的堵塞。下面將用一個(gè)篇幅來說明mysql的讀寫分離技術(shù)

到此，關(guān)于“mysql鎖表機(jī)制的概述”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！

分享標(biāo)題：mysql鎖表機(jī)制的概述
標(biāo)題URL：http://muchs.cn/article2/gesdoc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、動(dòng)態(tài)網(wǎng)站、電子商務(wù)、做網(wǎng)站、品牌網(wǎng)站設(shè)計(jì)、品牌網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)