為了給高并發情況下的MySQL進行更好的優化，有必要了解一下mysql查詢更新時的鎖表機制。
一、概述
MySQL有三種鎖的級別：頁級、表級、行級。
MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；BDB存儲引擎采用的是頁面鎖（page-level
locking），但也支持表級鎖；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的值比較高，說明存在著較嚴重的表級鎖爭用情況。

2、MySQL表級鎖的鎖模式
MySQL的表級鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write
Lock）。MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖。
所以對MyISAM表進行操作，會有以下情況：
a、對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其它進程的寫操作。
b、對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其它進程的讀寫操作。
下面通過例子來進行驗證以上觀點。數據表gz_phone里有二百多萬數據，字段id,phone,ua,day。現在同時用多個客戶端同時對該表進行操作分析。
a、當我用客戶端1進行一個比較長時間的讀操作時，分別用客戶端2進行讀和寫操作：
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
說明當數據表有一個讀鎖時，其它進程的查詢操作可以馬上執行，但更新操作需等待讀鎖釋放后才會執行。
b、當用客戶端1進行一個較長時間的更新操作時，用客戶端2,3分別進行讀寫操作：
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
說明當數據表有一個寫鎖時，其它進程的讀寫操作都需等待讀鎖釋放后才會執行。
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> select count(*) from content group by content;
…
客戶端2：
mysql> update content set content = ‘I love you’ where id = 444;
Query OK, 1 row affected (30.68 sec)
Rows matched: 1Changed: 1Warnings: 0
用了半分鐘。
2）、我們現在終止客戶端1。
此時客戶端2：
mysql> update content set content = ‘I hate you’ where id = 444;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1Changed: 1Warnings: 0
僅僅用了20毫秒。

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

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

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

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

　　背景模擬：

　　為了更加清楚的說明這個問題，筆者先模擬一個日常的案例。通過案例大家來閱讀下面的內容，可能條理會更加的清晰?，F在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，當所有的并發讀鎖都釋放后，所有操作的寫鎖開始起作用，多個并發的寫操作阻塞執行，依次將該位置為1。這樣多個并發的操作讀的都是一條數據。

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

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

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

當這個分配的id大于密碼表里的id總數時候，表示密碼全部發送完。

這個原理可以用于其它需要防止并發讀臟數據的程序，mysql的讀寫鎖可以幫助我們來做其它的互斥程序。那天聽到吳浩說memca c he也有類似的讀寫鎖，不知道如果用那個是不是可以提高并發效率，要比操作mysql快。

LOCK TABLES

tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] …

UNLOCK TABLES

LOCK TABLES可以鎖定用于當前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。UNLOCK TABLES可以釋放被當前線程保持的任何鎖定。當線程發布另一個LOCK TABLES時，或當與服務器的連接被關閉時，所有由當前線程鎖定的表被隱含地解鎖。

表鎖定只用于防止其它客戶端進行不正當地讀取和寫入。保持鎖定（即使是讀取鎖定）的客戶端可以進行表層級的操作，比如DROP TABLE。

注意，下面是對事務表使用LOCK TABLES的說明：

·在嘗試鎖定表之前，LOCK TABLES不是事務安全型的，會隱含地提交所有活性事務。同時，開始一項事務（例如，使用START TRANSACTION），會隱含地執行UNLOCK TABLES

·對事務表（如InnoDB）使用LOCK TABLES的正確方法是，設置AUTOCOMMIT=0并且不能調用UNLOCK TABLES，直到您明確地提交事務為止。當您調用LOCK TABLES時，InnoDB會內部地取其自己的表鎖定，MySQL取其自己的表鎖定。InnoDB在下一個提交時釋放其表鎖定，但是，對于MySQL，要釋放表鎖定，您必須調用UNLOCK TABLES。您不應該讓AUTOCOMMIT=1，因為那樣的話，InnoDB會在調用LOCK TABLES之后立刻釋放表鎖定，并且很容易形成死鎖定。注意，如果AUTOCOMMIT=1，我們根本不能獲取InnoDB表鎖定，這樣就可以幫助舊的應用軟件避免不必要的死鎖定。

·ROLLBACK不會釋放MySQL的非事務表鎖定。

要使用LOCK TABLES，您必須擁有相關表的LOCK TABLES權限和SELECT權限。

使用LOCK TABLES的主要原因是仿效事務，或在更新表時加快速度。這將在后面進行更詳細的解釋。

如果一個線程獲得對一個表地READ鎖定，該線程（和所有其它線程）只能從該表中讀取。如果一個線程獲得對一個表的WRITE鎖定，只有保持鎖定的線程可以對表進行寫入。其它的線程被阻止，直到鎖定被釋放時為止。

READ LOCAL和READ之間的區別是，READ LOCAL允許在鎖定被保持時，執行非沖突性INSERT語句（同時插入）。但是，如果您正打算在MySQL外面操作數據庫文件，同時您保持鎖定，則不能使用READ LOCAL。對于InnoDB表，READ LOCAL與READ相同。

當您使用LOCK TABLES時，您必須鎖定您打算在查詢中使用的所有的表。雖然使用LOCK TABLES語句獲得的鎖定仍然有效，但是您不能訪問沒有被此語句鎖定的任何的表。同時，您不能在一次查詢中多次使用一個已鎖定的表——使用別名代替，在此情況下，您必須分別獲得對每個別名的鎖定。

mysql> LOCK TABLE t WRITE, t AS t1 WRITE;

mysql> INSERT INTO t SELECT * FROM t;

ERROR 1100: Table ‘t’ was not locked with LOCK TABLES

mysql> INSERT INTO t SELECT * FROM t AS t1;

如果您的查詢使用一個別名引用一個表，那么您必須使用同樣的別名鎖定該表。如果沒有指定別名，則不會鎖定該表。

mysql> LOCK TABLE t READ;

mysql> SELECT * FROM t AS myalias;

ERROR 1100: Table ‘myalias’ was not locked with LOCK TABLES

相反的，如果您使用一個別名鎖定一個表，您必須使用該別名在您的查詢中引用該表。

mysql> LOCK TABLE t AS myalias READ;

mysql> SELECT * FROM t;

ERROR 1100: Table ‘t’ was not locked with LOCK TABLES

mysql> SELECT * FROM t AS myalias;

WRITE鎖定通常比READ鎖定擁有更高的優先權，以確保更新被盡快地處理。這意味著，如果一個線程獲得了一個READ鎖定，則另一個線程會申請一個WRITE鎖定，后續的READ鎖定申請會等待，直到WRITE線程獲得鎖定并釋放鎖定。您可以使用LOW_PRIORITY WRITE鎖定來允許其它線程在該線程正在等待WRITE鎖定時獲得READ鎖定。只有當您確定最終將有一個時機，此時沒有線程擁有READ鎖定時，您才應該使用LOW_PRIORITY WRITE鎖定。

LOCK TABLES按照如下方式執行：

1.按照內部定義的順序，對所有要被鎖定的表進行分類。從用戶的角度，此順序是未經定義的。

2.如果使用一個讀取和一個寫入鎖定對一個表進行鎖定，則把寫入鎖定放在讀取鎖定之前。

3.一次鎖定一個表，直到線程得到所有鎖定為止。

該規則確保表鎖定不會出現死鎖定。但是，對于該規則，您需要注意其它的事情：

如果您正在對一個表使用一個LOW_PRIORITY WRITE鎖定，這只意味著，MySQL等待特定的鎖定，直到沒有申請READ鎖定的線程時為止。當線程已經獲得WRITE鎖定，并正在等待得到鎖定表清單中的用于下一個表的鎖定時，所有其它線程會等待WRITE鎖定被釋放。如果這成為對于應用程序的嚴重的問題，則您應該考慮把部分表轉化為事務安全型表。

您可以安全地使用KILL來結束一個正在等待表鎖定的線程。

注意，您不能使用INSERT DELAYED鎖定任何您正在使用的表，因為，在這種情況下，INSERT由另一個線程執行。

通常，您不需要鎖定表，因為所有的單個UPDATE語句都是原子性的；沒有其它的線程可以干擾任何其它當前正在執行的SQL語句。但是，在幾種情況下，鎖定表會有好處：

·如果您正在對一組MyISAM表運行許多操作，鎖定您正在使用的表，可以快很多。鎖定MyISAM表可以加快插入、更新或刪除的速度。不利方面是，沒有線程可以更新一個用READ鎖定的表（包括保持鎖定的表），也沒有線程可以訪問用WRITE鎖定的表（除了保持鎖定的表以外）。

有些MyISAM操作在LOCK TABLES之下更快的原因是，MySQL不會清空用于已鎖定表的關鍵緩存，直到UNLOCK TABLE被調用為止。通常，關鍵緩存在每個SQL語句之后被清空。

·如果您正在使用MySQL中的一個不支持事務的存儲引擎，則如果您想要確定在SELECT和UPDATE之間沒有其它線程，您必須使用LOCK TABLES。本處所示的例子要求LOCK TABLES，以便安全地執行：

·mysql> LOCK TABLES trans READ, customer WRITE;

·mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;

·mysql> UPDATE customer

·->SET total_value=sum_from_previous_statement

·->WHERE customer_id=some_id;

·mysql> UNLOCK TABLES;

如果沒有LOCK TABLES，有可能另一個線程會在執行SELECT和UPDATE語句之間在trans表中插入一個新行。

通過使用相對更新（UPDATE customer SET value=value+new_value）或LAST_INSERT_ID()函數，您可以在許多情況下避免使用LOCK TABLES。

通過使用用戶層級的顧問式鎖定函數GET_LOCK()和RELEASE_LOCK()，您也可以在有些情況下避免鎖定表。這些鎖定被保存在服務器中的一個混編表中，使用pthread_mutex_lock() 和pthread_mutex_unlock()，以加快速度。

要了解更多有關鎖定規則的說明

您可以使用FLUSH TABLES WITH READ LOCK語句鎖定位于所有帶有讀取鎖定的數據庫中的所有表。如果您有一個可以及時拍攝快照的文件系統，比如Veritas，這是獲得備份的一個非常方便的方式。

注釋：如果您對一個已鎖定的表使用ALTER TABLE，該表可能會解鎖。

總結

以上是生活随笔為你收集整理的mysql 锁表详解(Downloads)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。