mysql使用內部鎖和外部鎖，而5.5.3又引入了後設資料鎖

外部鎖

僅用於MyISAM表，當表可能由多個mysql伺服器或者myisamchk線上訪問時，用以協同訪問；

當伺服器啟用外部鎖時，程式訪問表之前需要申請檔案系統鎖，如果無法獲取則等待；

可任意時刻使用myisamchk進行讀操作，當進行諸如修復或最佳化表等寫操作時，還是要確保mysqld此刻沒有用到這個表，否則同時訪問表可能會造成其損壞；

可使用repair/optimize/check table替代myisamchk；

引數skip_external_locking用於控制是否啟用外部鎖，預設不啟用

後設資料鎖

執行事務時基表會被新增metadata lock，以阻止其他會話對該表進行DDL；

5.5.3之前，一旦語句執行完就會釋放該鎖，但若此時其他事務對基表執行了DDL，則二進位制日誌的順序可能會亂掉，故5.5.3起只在事務結束後才釋放metadata lock；

倘若獲取metadata lock的語句解析成功但執行失敗，依舊不會提前釋放此鎖，只因該失敗的sql會被寫入二進位制日誌，需要鎖保護日誌一致性；

後設資料鎖在表鎖之前獲取，如果有會話採用lock table write鎖住了表(獲取了排他後設資料鎖)，則後續試圖訪問該表的其他會話被阻塞於後設資料鎖等待，而table_locks_waited不會增加；

內部鎖

根據儲存引擎，可劃分為表鎖和行級鎖；

對於MyISAM/Memory/Merge，使用表鎖，而innodb使用行級鎖；

下面只介紹InnoDB相關鎖

行鎖分為共享鎖s和排他鎖x；此外還有gap鎖，兩者一起組成了next-key鎖，主要用於保證repeatable read；

行鎖提供良好的並非性，但是相對錶鎖，有一定的額外開銷

意向鎖

innodb支援多粒度鎖，允許行鎖和表鎖共存，即意向鎖；意向鎖就是InnoDB的表級鎖，在一個事務中揭示下一行將被請求的鎖型別，包括2種型別：

意向共享鎖IS lock：事務想獲取一個表中某幾行的共享鎖，select … lock in share mode

意向排他鎖IX lock：事務想獲取一個表中某幾行的排他鎖，select … for update

select for update/lock in share mode會採用read-commited的事務級別，即讀取最新的已提交行，insert select也是如此；

意向鎖除了全表請求，諸如lock tables … write外不阻塞其他操作

意向鎖和行鎖相容關係，如下表所示

表鎖

對於Innodb表，若要使用表鎖，必須先設定autocommit=0，且在事務提交後再unlock tables，如下例所示：

SET autocommit=0;

LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...

COMMIT;

UNLOCK TABLES

呼叫lock tables時，除了mysql層的表鎖，還需要獲取innodb表鎖；commit時innodb釋放內部表鎖，unlock tables時mysql伺服器才釋放表鎖；

如果autocommit=1，innodb不會獲取內部表鎖，極易導致死鎖發生；

相關引數

innodb使用mutex實現鎖，mutex_enter申請鎖，mutex_exit釋放，在mutex_exit前後續mutex_enter會被阻塞

innodb_lock_wait_timeout用於控制等待時間，預設50秒，動態引數

innodb_rollback_on_timeout超時後是否對事務回滾，預設off只撤銷失敗sql，靜態引數

外來鍵與鎖

innodb為自動為外來鍵約束加鎖，當對外來鍵值進行DML時，會以select ... lock in share mode方式對父表記錄新增s鎖

mysql的鎖機制

相關文章