mysql怎么判斷表別鎖死了

rubylc_unix

RT，想監(jiān)控DB的運(yùn)行狀態(tài)，一個(gè)是連接數(shù)一個(gè)是判斷表有沒有被鎖住，怎么判斷表被鎖住了呢？

lyhabc

MySQL官方文檔 http://dev.mysql.com/doc/refman/5.1/zh/index.html

13.4.5. LOCK TABLES和UNLOCK TABLES語法
LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES
LOCK TABLES可以鎖定用于當(dāng)前線程的表。如果表被其它線程鎖定，則造成堵塞，直到可以獲取所有鎖定為止。UNLOCK TABLES可以釋放被當(dāng)前線程保持的任何鎖定。當(dāng)線程發(fā)布另一個(gè)LOCK TABLES時(shí)，或當(dāng)與服務(wù)器的連接被關(guān)閉時(shí)，所有由當(dāng)前線程鎖定的表被隱含地解鎖。

  表鎖定只用于防止其它客戶端進(jìn)行不正當(dāng)?shù)刈x取和寫入。保持鎖定（即使是讀取鎖定）的客戶端可以進(jìn)行表層級(jí)的操作，比如DROP TABLE。

注意，下面是對(duì)事務(wù)表使用LOCK TABLES的說明：

·         在嘗試鎖定表之前，LOCK TABLES不是事務(wù)安全型的，會(huì)隱含地提交所有活性事務(wù)。同時(shí)，開始一項(xiàng)事務(wù)（例如，使用START TRANSACTION），會(huì)隱含地執(zhí)行UNLOCK TABLES。（見13.4.3節(jié)，“會(huì)造成隱式提交的語句”。

·         對(duì)事務(wù)表（如InnoDB）使用LOCK TABLES的正確方法是，設(shè)置AUTOCOMMIT=0并且不能調(diào)用UNLOCK TABLES，直到您明確地提交事務(wù)為止。當(dāng)您調(diào)用LOCK TABLES時(shí)，InnoDB會(huì)內(nèi)部地取其自己的表鎖定，MySQL取其自己的表鎖定。InnoDB在下一個(gè)提交時(shí)釋放其表鎖定，但是，對(duì)于MySQL，要釋放表鎖定，您必須調(diào)用UNLOCK TABLES。您不應(yīng)該讓AUTOCOMMIT=1，因?yàn)槟菢拥脑�，InnoDB會(huì)在調(diào)用LOCK TABLES之后立刻釋放表鎖定，并且很容易形成死鎖定。注意，如果AUTOCOMMIT=1，我們根本不能獲取InnoDB表鎖定，這樣就可以幫助舊的應(yīng)用軟件避免不必要的死鎖定。

·         ROLLBACK不會(huì)釋放MySQL的非事務(wù)表鎖定。

要使用LOCK TABLES，您必須擁有相關(guān)表的LOCK TABLES權(quán)限和SELECT權(quán)限。

使用LOCK TABLES的主要原因是仿效事務(wù)，或在更新表時(shí)加快速度。這將在后面進(jìn)行更詳細(xì)的解釋。

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

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

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

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;
如果您的查詢使用一個(gè)別名引用一個(gè)表，那么您必須使用同樣的別名鎖定該表。如果沒有指定別名，則不會(huì)鎖定該表。

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
相反的，如果您使用一個(gè)別名鎖定一個(gè)表，您必須使用該別名在您的查詢中引用該表。

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鎖定擁有更高的優(yōu)先權(quán)，以確保更新被盡快地處理。這意味著，如果一個(gè)線程獲得了一個(gè)READ鎖定，則另一個(gè)線程會(huì)申請(qǐng)一個(gè)WRITE鎖定，后續(xù)的READ鎖定申請(qǐng)會(huì)等待，直到WRITE線程獲得鎖定并釋放鎖定。您可以使用LOW_PRIORITY WRITE鎖定來允許其它線程在該線程正在等待WRITE鎖定時(shí)獲得READ鎖定。只有當(dāng)您確定最終將有一個(gè)時(shí)機(jī)，此時(shí)沒有線程擁有READ鎖定時(shí)，您才應(yīng)該使用LOW_PRIORITY WRITE鎖定。

LOCK TABLES按照如下方式執(zhí)行：

1.    按照內(nèi)部定義的順序，對(duì)所有要被鎖定的表進(jìn)行分類。從用戶的角度，此順序是未經(jīng)定義的。

2.    如果使用一個(gè)讀取和一個(gè)寫入鎖定對(duì)一個(gè)表進(jìn)行鎖定，則把寫入鎖定放在讀取鎖定之前。

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

該規(guī)則確保表鎖定不會(huì)出現(xiàn)死鎖定。但是，對(duì)于該規(guī)則，您需要注意其它的事情：

如果您正在對(duì)一個(gè)表使用一個(gè)LOW_PRIORITY WRITE鎖定，這只意味著，MySQL等待特定的鎖定，直到?jīng)]有申請(qǐng)READ鎖定的線程時(shí)為止。當(dāng)線程已經(jīng)獲得WRITE鎖定，并正在等待得到鎖定表清單中的用于下一個(gè)表的鎖定時(shí)，所有其它線程會(huì)等待WRITE鎖定被釋放。如果這成為對(duì)于應(yīng)用程序的嚴(yán)重的問題，則您應(yīng)該考慮把部分表轉(zhuǎn)化為事務(wù)安全型表。

您可以安全地使用KILL來結(jié)束一個(gè)正在等待表鎖定的線程。請(qǐng)參見13.5.5.3節(jié)，“KILL語法”。

注意，您不能使用INSERT DELAYED鎖定任何您正在使用的表，因?yàn)�，在這種情況下，INSERT由另一個(gè)線程執(zhí)行。

通常，您不需要鎖定表，因?yàn)樗�有的單個(gè)UPDATE語句都是原子性的；沒有其它的線程可以干擾任何其它當(dāng)前正在執(zhí)行的SQL語句。但是，在幾種情況下，鎖定表會(huì)有好處：

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

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

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

·                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，有可能另一個(gè)線程會(huì)在執(zhí)行SELECT和UPDATE語句之間在trans表中插入一個(gè)新行。

通過使用相對(duì)更新（UPDATE customer SET value=value+new_value）或LAST_INSERT_ID()函數(shù)，您可以在許多情況下避免使用LOCK TABLES。請(qǐng)參見1.8.5.3節(jié)，“事務(wù)和原子操作”。

通過使用用戶層級(jí)的顧問式鎖定函數(shù)GET_LOCK()和RELEASE_LOCK()，您也可以在有些情況下避免鎖定表。這些鎖定被保存在服務(wù)器中的一個(gè)混編表中，使用pthread_mutex_lock() 和pthread_mutex_unlock()，以加快速度。請(qǐng)參見12.9.4節(jié)，“其他函數(shù)”。

要了解更多有關(guān)鎖定規(guī)則的說明，請(qǐng)參見7.3.1節(jié)，“鎖定方法”。

您可以使用FLUSH TABLES WITH READ LOCK語句鎖定位于所有帶有讀取鎖定的數(shù)據(jù)庫中的所有表。請(qǐng)參見13.5.5.2節(jié)，“FLUSH語法”。如果您有一個(gè)可以及時(shí)拍攝快照的文件系統(tǒng)，比如Veritas，這是獲得備份的一個(gè)非常方便的方式。

注釋：如果您對(duì)一個(gè)已鎖定的表使用ALTER TABLE，該表可能會(huì)解鎖。

lyhabc

show OPEN TABLES where In_use > 0;


1:什么情況下mysql會(huì)自動(dòng)加鎖
SELECT FOR UPDATE，TRANSATION ，還中LOCK時(shí)都會(huì)鎖表。

2:為什么要加鎖
防止同一數(shù)據(jù)被不同人同時(shí)寫，比較兩個(gè)人同時(shí)執(zhí)行 update xx set col=col+2 where id=1 ;


3:鎖上了怎么可以自動(dòng)解鎖(不要重啟數(shù)據(jù)庫)
根據(jù)加鎖時(shí)的不同，解鎖方式也不樣。有自動(dòng)的，有的需要手工UNLOCK TABLES

lyhabc

在InnoDB事務(wù)模式中，目的是把多版本數(shù)據(jù)庫的最好特性與傳統(tǒng)的二相鎖定合并起來。InnoDB以O(shè)racle的風(fēng)格，對(duì)行級(jí)進(jìn)行鎖定，并且默認(rèn)運(yùn)行查詢作為非鎖定持續(xù)讀。在InnoDB中鎖定的表被存儲(chǔ)得如此節(jié)省空間，以至于不需要鎖定增大：典型地，數(shù)個(gè)用戶被允許在數(shù)據(jù)庫中鎖定每一行，或者行的任何