為了給高併發情況下的mysql進行更好的最佳化，有必要了解一下mysql查詢更新時的鎖表機制。

一、概述
MySQL有三種鎖的級別：頁級、表級、行級。
MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-levellocking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下：
表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的機率最高,併發度最低。
行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的機率最低,併發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

二、MyISAM表鎖
MyISAM儲存引擎只支援表鎖，是現在用得最多的儲存引擎。
1、查詢表級鎖爭用情況

可以透過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：
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的值比較高，說明存在著較嚴重的表級鎖爭用情況

行級鎖可以這樣檢視
mysql> show status like '%innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 | ----當前等待鎖的數量
| Innodb_row_lock_time | 15640 | -----等待總時長
| Innodb_row_lock_time_avg | 15640 | -----等待平均時長
| Innodb_row_lock_time_max | 15640 | ----等待最長的一次時間
| Innodb_row_lock_waits | 1 | ----從系統開始到現在的總共等待次數
+-------------------------------+-------+
5 rows in set (0.00 sec)

2、MySQL表級鎖的鎖模式

MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨佔寫鎖（Table WriteLock）。MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖。
所以對MyISAM表進行操作，會有以下情況：
a、對MyISAM表的讀操作（加讀鎖），不會阻塞其他程式對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它程式的寫操作。
b、對MyISAM表的寫操作（加寫鎖），會阻塞其他程式對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它程式的讀寫操作。
下面透過例子來進行驗證以上觀點。資料表gz_phone裡有二百多萬資料，欄位id,phone,ua,day。現在同時用多個客戶端同時對該表進行操作分析。
a、當我用客戶端1進行一個比較長時間的讀操作時，分別用客戶端2進行讀和寫操作：
mysql> update gz_phone set phone=’11111111111′ where id=1001;
Query OK, 0 rows affected (2 min 57.88 sec)
說明當資料表有一個讀鎖時，其它程式的查詢操作可以馬上執行，但更新操作需等待讀鎖釋放後才會執行。
b、當用客戶端1進行一個較長時間的更新操作時，用客戶端2,3分別進行讀寫操作：
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
說明當資料表有一個寫鎖時，其它程式的讀寫操作都需等待讀鎖釋放後才會執行。

3、併發插入

原則上資料表有一個讀鎖時，其它程式無法對此表進行更新操作，但在一定條件下，MyISAM表也支援查詢和插入操作的併發進行。

MyISAM儲存引擎有一個系統變數concurrent_insert，專門用以控制其併發插入的行為，其值分別可以為0、1或2。

a、當concurrent_insert設定為0時，不允許併發插入。

b、當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個程式讀表的同時，另一個程式從表尾插入記錄。這也是MySQL的預設設定。

c、當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾併發插入記錄。

4 、MyISAM的鎖排程
由於MySQL認為寫請求一般比讀請求要重要，所以如果有讀寫請求同時進行的話，MYSQL將會優先執行寫操作。這樣MyISAM表在進行大量的更新操作時（特別是更新的欄位中存在索引的情況下），會造成查詢操作很難獲得讀鎖，從而導致查詢阻塞。

我們可以透過一些設定來調節MyISAM的排程行為：
a、透過指定啟動引數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
b、透過執行命令SET LOW_PRIORITY_UPDATES=1，使該連線發出的更新請求優先順序降低。
c、透過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
上面3種方法都是要麼更新優先，要麼查詢優先的方法。這裡要說明的就是，不要盲目的給mysql設定為讀優先，因為一些需要長時間執行的查詢操作，也會使寫程式“餓死”。只有根據你的實際情況，來決定設定哪種操作優先。這些方法還是沒有從根本上同時解決查詢和更新的問題。
在一個有大資料量高並發表的mysql裡，我們還可採用另一種策略來進行最佳化，那就是透過mysql主從（讀寫）分離來實現負載均衡，這樣可避免優先哪一種操作從而可能導致另一種操作的堵塞。下面將用一個篇幅來說明mysql的讀寫分離技術。

1. MySQL鎖表請求有兩種方式: read鎖和write鎖語法 lock tables t read/write兩者的共同點是當執行鎖表後除當前程式外其他程式都無法訪問該表除非發生下面三種情況之一:1.該程式執行解鎖語句unlock tables 2.該程式執行其他鎖表請求 3.該程式退出或斷開與MySQL資料庫連線;兩者不同點是執行read鎖的鎖表程式只可對該表查詢不能修改資料,執行write鎖的程式可以有增刪改查所有許可權可以理解為後者包含前者事實上也是後者的優先順序比前者要高通常我都是執行write鎖的,下面舉例也都以write為例
2. 程式執行lock tables t write鎖表後,如果需要訪問到表t1 ,MySQL會報錯ERROR 1100: Table 't1' was not locked with LOCK TABLES 解決辦法:程式一次對多表鎖定,語法: lock tables t write,t1 write,… 解鎖方法見1,unlock tables 只需執行一次即可

mysql鎖和死鎖
MyISAM和MEMORY儲存引擎採用的是表級鎖table-level locking
BDB儲存引擎採用的是頁面鎖page-level locking,但也支援表級鎖
InnoDB儲存引擎既支援行級鎖row-level locking,也支援表級鎖,但預設情況下是採用行級鎖
表級鎖開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖衝突的機率最高,併發度最低
行級鎖開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖衝突的機率最低,併發度也最高
頁面鎖開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間,併發度一般
僅從鎖的角度來說:
表級鎖更適合於以查詢為主,只有少量按索引條件更新資料的應用,如Web應用
行級鎖則更適合於有大量按索引條件併發更新少量不同資料,同時又有併發查詢的應用,如一些線上事務處理系統

死鎖
所謂死鎖<DeadLock>: 是指兩個或兩個以上的程式在執行過程中,
因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.
此時稱系統處於死鎖狀態或系統產生了死鎖,這些永遠在互相等竺的程式稱為死鎖程式.
表級鎖不會產生死鎖.所以解決死鎖主要還是真對於最常用的InnoDB.
在遇到問題時
先執行show processlist找到死鎖執行緒號.然後Kill　processNo
當然主要解決還是需要去看一下具體的操作.可能產生死鎖
Show innodb status檢查引擎狀態 ,可以看到哪些語句產生死鎖
然後就是解決了.
怎麼解決還是要看具體什麼問題.
MyISAM使用的是 flock 類的函式，直接就是對整個檔案進行鎖定（叫做檔案鎖定），InnoDB使用的是 fcntl 類的函式，可以對檔案中區域性資料進行鎖定（叫做行鎖定），所以區別就是在這裡。
另外MyISAM的資料表是按照單個檔案儲存的，可以針對單個表檔案進行鎖定，但是InnoDB是一整個檔案，把索引、資料、結構全部儲存在 ibdata 檔案裡，所以必須用行鎖定。

1、對於MySQL來說，有三種鎖的級別：頁級、表級、行級。頁級的典型代表引擎為BDB。
表級的典型代表引擎為MyISAM,MEMORY以及很久以前的ISAM。
行級的典型代表引擎為INNODB。
2、我們實際應用中用的最多的就是行鎖了。

行級鎖的優點如下：
1）、當很多連線分別進行不同的查詢時減小LOCK狀態。
2）、如果出現異常，可以減少資料的丟失。因為一次可以只回滾一行或者幾行少量的資料。
行級鎖的缺點如下：
1）、比頁級鎖和表級鎖要佔用更多的記憶體。
2）、進行查詢時比頁級鎖和表級鎖需要的I/O要多，所以我們經常把行級鎖用在寫操作而不是讀操作。
3）、容易出現死鎖。
3、MySQL用寫佇列和讀佇列來實現對資料庫的寫和讀操作。

對於寫鎖定如下：
1）、如果表沒有加鎖，那麼對其加寫鎖定。
2）、否則，那麼把請求放入寫鎖佇列中。
對於讀鎖定如下：
1）、如果表沒有加寫鎖，那麼加一個讀鎖。
2）、否則，那麼把請求放到讀鎖佇列中。
當然我們可以分別用low_priority 以及high_priority在寫和讀操作上來改變這些行為。

4、下面我來一個簡單的例子解釋上面的說法。我們來執行一個時間很長的查詢
1）、客戶端1：
mysql》查詢* from t1 ---用select 和update就攔截髮不出去有病啊！！怎麼攔截的

客戶端2：
mysql》更新 t1
   用了半分鐘。
2）、我們現在終止客戶端1。
此時客戶端2：
僅僅用了20毫秒。

這個例子很好的說明了讀寫佇列的執行。
對於1中的客戶端1，此時表沒有加鎖，當然也沒有加寫鎖了，那麼此時客戶端1對錶加了一個讀鎖。
對於1中的客戶端2，此時因為表有一個讀鎖，所以把UPDATE請求放到寫鎖定佇列中。
當讀鎖釋放的時候，也就是SHOW PROCESSLIST中STATUS 為COPY TO TMP TABLE的時候，UPDATE操作開始執行。

5、可以在REPLICATION中對MASTER 和SLAVE運用不同的鎖定使系統達到最佳的效能。（當然這個前提是SQL語句都是最優的。）

透過鎖機制，可以實現多執行緒同時對某個表進行操作。如下圖所示，在某個時刻，使用者甲、使用者乙、使用者丙可能會同時或者先後(前面一個作業還沒有完成) 對資料表A進行查詢或者更新的操作。當某個執行緒涉及到更新操作時，就需要獲得獨佔的訪問權。在更新的過程中，所有其它想要訪問這個表的執行緒必須要等到其更新完成為止。此時就會導致鎖競爭的問題。從而導致使用者等待時間的延長。在這篇文章中，筆者將跟大家討論，採取哪些措施可以有效的避免鎖競爭，減少 MySQL使用者的等待時間。

降低鎖競爭減少MySQL使用者等待時間

　　背景模擬：

　　為了更加清楚的說明這個問題，筆者先模擬一個日常的案例。透過案例大家來閱讀下面的內容，可能條理會更加的清晰。現在MySQL資料庫遇到如上圖所示這種情況。

　　首先，使用者甲對資料表A發出了一個查詢請求。

　　然後，使用者乙又對資料表A發出了一個更新請求。此時使用者乙的請求只有在使用者甲的作業完成之後才能夠得到執行。

　　最後，使用者丙又對資料表A發出了一個查詢請求。在MySQL資料庫中，更新語句的優先順序要比查詢語句的優先順序高，為此使用者丙的查詢語句只有在使用者乙的更新作業完成之後才能夠執行。而使用者乙的更新作業又必須在使用者甲的查詢語句完成之後才能夠執行。此時就存在比較嚴重的鎖競爭問題。

　　現在資料庫工程師所要做的就是在資料庫設計與最佳化過程中，採取哪些措施來降低這種鎖競爭的不利情況?

　　措施一：利用Lock Tables來提高更新速度
　　對於更新作業來說，在一個鎖定中進行許多更新要比所有鎖定的更新要來得快。為此如果一個表更新頻率比較高，如超市的收銀系統，那麼可以透過使用Lock Tables選項來提高更新速度。更新的速度提高了，那麼與Select查詢作業的衝突就會明顯減少，鎖競爭的現象也能夠得到明顯的抑制。

　　措施二：將某個表分為幾個表來降低鎖競爭
　　如一個大型的購物超市，如沃爾瑪，其銷售紀錄表每天的更新操作非常的多。此時如果使用者在更新的同時，另外有使用者需要對其進行查詢，顯然鎖競爭的現象會比較嚴重。針對這種情況，其實可以人為的將某張表分為幾個表。如可以為每一臺收銀機專門設定一張資料表。如此的話，各臺收銀機之間使用者的操作都是在自己的表中完成，相互之間不會產生干擾。在資料統計分析時，可以透過檢視將他們整合成一張表。

　　措施三：調整某個作業的優先順序
　　預設情況下，在MySQL資料庫中，更新操作比Select查詢有更高的優先順序。如上圖所示，如果使用者乙先發出了一個查詢申請，然後使用者丙再發出一個更新請求。當使用者甲的查詢作業完成之後，系統會先執行誰的請求呢?注意，預設情況下系統並不遵循先來後到的規則，即不會先執行使用者乙的查詢請求，而是執行使用者丙的更新程式。這主要是因為，更新程式比查詢程式具有更高的優先順序。

　　但是在有些特定的情況下，可能這種優先順序不符合企業的需求。此時資料庫管理員需要根據實際情況來調整語句的優先順序。如果確實需要的話，那麼可以透過以下三種方式來實現。

　　一是透過LOW_PRIOITY屬性。這個屬性可以將某個特定的語句的優先順序降低。如可以調低某個特定的更新語句或者插入語句的優先順序。不過需要注意的是，這個屬性只有對特定的語句有用。即其作用域只針對某個特定的語句，而不會對全域性造成影響。

　　二是透過HIGH_PRIOITY屬性。與透過LOW_PRIOITY屬性對應，有一個HIGH_PRIOITY屬性。顧名思義，這個屬性可以用來提高某個特定的Select查詢語句的優先順序。如上面這個案例，在使用者丙的查詢語句中加入HIGH_PRIOITY屬性的話，那麼使用者甲查詢完畢之後，會立即執行使用者丙的查詢語句。等到使用者丙執行完畢之後，才會執行使用者乙的更新操作。可見，此時查詢語句的優先順序得到了提升。這裡需要注意，跟上面這個屬性一樣，這個作用域也只限於特定的查詢語句。而不會對沒有加這個引數的其他查詢語句產生影響。也就是說，其他查詢語句如果沒有加這個屬性，那麼其優先順序別仍然低於更新程式。

　　三是透過Set LOW_PRIORIT_UPDATES=1選項。以上兩個屬性都是針對特定的語句，而不會造成全域性的影響。如果現在資料庫管理員需要對某個連線來調整優先順序別，該如何實現呢?如上例，現在使用者需要將使用者丙連線的查詢語句的優先順序別提高，而不是每次查詢時都需要使用上面的屬性。此時就需要使用Set LOW_PRIORIT_UPDATES=1選項。透過這個選項可以制定具體連線中的所有更新程式都是用比較低的優先順序。注意這個選項只針對特定的連線有用。對於其他的連線，就不適用。

　　四是採用Low_Priority_updates選項。上面談到的屬性，前面兩個針對特定的語句，後面一個是針對特定的連線，都不會對整個資料庫產生影響。如果現在需要在整個資料庫範圍之內，降低更新語句的優先順序，是否可以實現?如上面這個案例，在不使用其他引數的情況下，就讓使用者丙的查詢語句比使用者乙的更新具有更先執行?如果使用者有這種需求的話，可以使用 Low_Priority_updates選項來啟動資料庫。採用這個選項啟動資料庫時，系統會給資料庫中所有的更新語句比較低的優先順序。此時使用者丙的查詢語句就會比使用者使用者乙的更新請求更早的執行。而對於查詢作業來說，不存在鎖定的情況。為此使用者甲的查詢請求與使用者丙的查詢請求可以同時進行。為此透過調整語句執行的優先順序，可以有效的降低鎖競爭的情況。

　　可見，可以利用屬性或者選項來調整某條語句的優先順序。如現在有一個應用，主要供使用者來進行查詢。更新的操作一般都是有管理員來完成，並且對於使用者來說更新的資料並不敏感。此時基於使用者優先的原則，可以考慮將查詢的優先順序別提高。如此的話，對於使用者來說，其遇到鎖競爭的情況就會比較少，從而可以縮短使用者的等待時間。在調整使用者優先順序時，需要考慮其調整的範圍。即只是調整特定的語句、還是調整特定的連線，又或者對整個資料庫生效。

　　措施四：對於混合操作的情況，可以採用特定的選項
　　有時候會遇到混合操作的作業，如即有更新操作又有插入操作又有查詢操作時，要根據特定的情況，採用特定的選項。如現在需要對資料表同時進行插入和刪除的作業，此時如果能夠使用Insert Delayed選項，將會給使用者帶來很大的幫助。再如對同一個資料表執行Select和Delete語句會有鎖競爭的情況。此時資料庫管理員也可以根據實際情況來選擇使用Delete Limint選項來解決所遇到速度問題。

　　通常情況下，鎖競爭與死鎖不同，並不會對資料庫的執行帶來很大的影響。只是可能會延長使用者的等待時間。如果使用者併發訪問的機率並不是很高，此時鎖競爭的現象就會很少。那麼採用上面的這些措施並不會帶來多大的收益。相反，如果使用者對某個表的併發訪問比較多，特別是不同的使用者會對錶執行查詢、更新、刪除、插入等混合作業，那麼採取上面這些措施可以在很大程度上降低鎖衝突，減少使用者的等待時間。

感覺我們技術部的總結會，每回都能學到很多新知識，希望以後多有分享技術心得的環節。其實分享往往就寥寥幾句，有的時候聽完了，可能會不知道原理、實現的過程，甚至只能聽明白一小部分。但這都沒關係，重要的是，知道有那麼回事，用到的時候會少走很多彎路。關於上次總結會顯功提出的 “ 雙表併發 ” ，猜想原理如下：

     在分發註冊密碼的web互動程式中，普通的，不考慮併發的設計如下：

     設計一個表：一個欄位是id，一個欄位是需要分發的密碼，另一個是標誌位，標誌該密碼是否已經分發，初始是0；

      程式設計：從表裡找到一個標誌位為0的密碼，設定該標誌位1，然後發給使用者。

但這個方法問題是當使用者的訪問是高併發的時候，多個使用者會得到相同的密碼，

原因是(猜想僅供參考 )：

      mysql的資料庫操作方式是類似作業系統的讀寫鎖，就是允許多個讀鎖同時操作，此時是不允許寫的，當讀鎖放開的時候允許寫，同理當寫鎖起作用的時候，讀鎖是阻塞的。所以，當使用者高併發的時候，多個讀鎖可以一起讀，第一個讀鎖釋放後，它要將標誌位置為1，但由於有其它讀鎖在讀，所以第一個操作的寫鎖阻塞在這裡，不能夠將剛讀到的這一行的標誌欄位，及時設定為1。併發的其他讀鎖讀到的標誌位還是0（所以獲得了相同的id即密碼），當所有的併發讀鎖都釋放後，所有操作的寫鎖開始起作用，多個併發的寫操作阻塞執行，依次將該位置為1。這樣多個併發的操作讀的都是一條資料。

     解決這個問題的方法是，仍舊利用mysql的讀寫鎖的機制，對於這種機制，寫鎖一定互斥的，雖然允許同時多個讀操作，但永遠只允許一個寫操作。剛才的問題是多個讀資料的操作併發執行造成的，要避免這個，需要對讀取的時候也加鎖，不允許併發讀取。我不知道mysql有沒有這方面的設定直接來實現，但可以透過如下取巧的方式解決。

      由於在寫入的時候鎖是互斥的，所以再建立一個表，只儲存一個欄位即可，就是一個自增的id，當有操作需要申請密碼的時候，先在這個表裡插入一條空資料，這樣返回一個mysql分配的自增的id，用這個id去第一個表裡取相應該id的密碼就可以了。

      不會出現多個使用者得到同樣密碼的解釋是，此時多個併發的操作肯定可以得到不同的id，因為在插入的時候寫鎖是互斥的，併發的多個操作要想寫資料庫，就會阻塞排隊，第一個操作寫入後，釋放了該鎖，獲得 mysql分配的id，其後的操作需要執行insert操作，mysql就會將這個操作順序插入資料庫的不同行，返回不同的id，此時雖然操作是併發的，同時到達的，但對於mysql來說，是一條一條執行插入語句，所以當然操作的是不同的行，返回不同的id，這樣在第一個表裡找到的就是不同的密碼，使用者分配到的也是不同的密碼。

當這個分配的id大於密碼錶裡的id總數時候，表示密碼全部傳送完。

以上講的主要就是myisam引擎的特點~~~~innodb引擎是有區別的！比如一致性非鎖定讀~如：當session1更新t1 表 session2還是可以立馬讀取t1，只不過是從以前的映象中讀取出來的，這是innodb的MVCC多版本併發控制，和oracle的很像啊~從undo段中讀取映象！其它的區別還有很多！！！！！！！！！！！！！！

MySQL鎖詳解

相關文章