鎖概述

mysql鎖機(jī)制的特點(diǎn)： 不同存儲(chǔ)引擎支持不同的鎖機(jī)制。

MyISAM和MEMORY存儲(chǔ)引擎支持表級(jí)鎖；

BDB存儲(chǔ)引擎采用頁(yè)面鎖；

InnoDB存儲(chǔ)引擎支持行級(jí)鎖。

• 表級(jí)鎖：

  開(kāi)銷(xiāo)小，加鎖快，不會(huì)出現(xiàn)死鎖，鎖定粒度大，加鎖沖突概率高，并發(fā)度最低；

  適用于以查詢(xún)?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；

• 行級(jí)鎖

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

  適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢(xún)的應(yīng)用，如一些在線(xiàn)事務(wù)處理（OLTP）系統(tǒng)。

• 頁(yè)面鎖

  開(kāi)銷(xiāo)和加鎖時(shí)間介于表鎖和行鎖之間，會(huì)出現(xiàn)死鎖，鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般；

MyISAM表鎖

查詢(xún)表級(jí)鎖的爭(zhēng)用情況

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 19 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)

成都創(chuàng)新互聯(lián)公司主營(yíng)溫宿網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,成都App定制開(kāi)發(fā),溫宿h5小程序開(kāi)發(fā)搭建,溫宿網(wǎng)站營(yíng)銷(xiāo)推廣歡迎溫宿等地區(qū)企業(yè)咨詢(xún)

table_locks_waited的值越大，表級(jí)鎖的爭(zhēng)用情況越嚴(yán)重

表級(jí)鎖的鎖模式

• 表共享讀鎖

• 表共享寫(xiě)鎖

MyISAM表讀操作，不會(huì)阻塞其他用戶(hù)對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求；
MyISAM表的寫(xiě)操作，會(huì)阻塞其他用戶(hù)對(duì)同一表的讀請(qǐng)求和寫(xiě)請(qǐng)求。
MyISAM表的讀操作和寫(xiě)操作之間，以及寫(xiě)操作之間是串行的

如何加表鎖

MyISAM表在執(zhí)行SELECT語(yǔ)句之前，會(huì)自動(dòng)給涉及到的表加讀鎖，在執(zhí)行UPDATE、DELETE、INSERT之前，自動(dòng)給涉及到的表加寫(xiě)鎖。

當(dāng)然也可以顯示手動(dòng)加鎖，用來(lái)模擬事務(wù)操作。

mysql> lock table tbl_name1 read/write；
mysql> lock tables tbl_name1 read [local], tbl_name2 write [local];

mysql> unlock tables;

注意：對(duì)表加鎖之后，使用該表的別名也是不允許的，需要對(duì)別名也進(jìn)行鎖定，如下：

mysql> lock table tbl_name1 as tb1 read;
mysql> select a.id from tbl_name tb1;

MyISAM的并發(fā)插入

MyISAM的讀寫(xiě)操作是串行的，但一定程度上，也是支持查詢(xún)和插入的并發(fā)進(jìn)行，但不可以刪除和更新。

在MyISAM引擎中有一個(gè)系統(tǒng)變量 concurrent_insert ，專(zhuān)門(mén)用來(lái)控制并發(fā)插入的行為，有三種取值：

0-> 不允許并發(fā)插入；
1-> 在MyISAM表沒(méi)有孔洞的前提下，允許在讀的同時(shí)，另一個(gè)進(jìn)程從尾部插入記錄（默認(rèn)）
2-> 不論有無(wú)空洞，都可以在表尾并發(fā)插入

整理空間碎片：

mysql> optimize table tbl_name;

MyISAM的鎖調(diào)度

已經(jīng)知道，MyISAM存儲(chǔ)引擎的讀鎖和寫(xiě)鎖是互斥的，讀寫(xiě)操作是串行的，但是，及時(shí)讀請(qǐng)求先到達(dá)等待隊(duì)列，寫(xiě)請(qǐng)求后到達(dá)等待隊(duì)列，寫(xiě)鎖也會(huì)插入到讀鎖之前，因?yàn)镸ySQL認(rèn)為寫(xiě)操作比讀操作重要。

同時(shí)，這也是MyISAM表不適合有大量更新和查詢(xún)操作的原因，因?yàn)榇罅康母潞筒樵?xún)操作會(huì)占據(jù)鎖等待隊(duì)列，讀鎖會(huì)被長(zhǎng)時(shí)間等待。

為了解決這個(gè)問(wèn)題，我們有一些參數(shù)設(shè)置來(lái)調(diào)節(jié)MyISAM的調(diào)度行為。

• 啟動(dòng)low-priority-updates,使得MyISAM 默認(rèn)基于讀請(qǐng)求以?xún)?yōu)先的權(quán)利；

• 降低更新請(qǐng)求的的優(yōu)先級(jí)

mysql> set LOW_PRIORITY_UPDATES=1

• 指定INSERT、UPDATE、DELETE語(yǔ)句的LOW_PRIORITY屬性，降低該語(yǔ)句的優(yōu)先級(jí)

• 一種折中的方案：給系統(tǒng)參數(shù)max_write_locl_count設(shè)置一個(gè)合適的值，當(dāng)表的讀操作達(dá)到該值之后，MySQL就暫時(shí)將寫(xiě)請(qǐng)求的優(yōu)先級(jí)降低。

InnoDB鎖

InnoDB與MyISAM的大不同支出在于：1、支持事務(wù)；2、采用了行級(jí)鎖。

查詢(xún)InnoDB行鎖的爭(zhēng)用情況

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，表級(jí)鎖的爭(zhēng)用情況越嚴(yán)重

InnoDB行鎖模式

• 共享鎖（S）

  允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖

• 排他鎖（X）

  允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫(xiě)鎖

為了行鎖表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖，這兩種意向鎖都是表鎖

• 意向共享鎖（IS）

  事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖之前，必須先取得該表的IS鎖

• 意向排他鎖（IX）

  事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖之前，必須先取得該表的IX鎖

意向鎖是InnoDB自動(dòng)添加的，不需要用戶(hù)干預(yù)

對(duì)于UPDATE、DELETE、INSERT語(yǔ)句，InnoDB自動(dòng)給涉及數(shù)據(jù)集加排他鎖；對(duì)于SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖。

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線(xiàn)，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性?xún)r(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿(mǎn)足用戶(hù)豐富、多元化的應(yīng)用場(chǎng)景需求。