mysql表鎖與lock tables

對於MyISAM/Memory/Merge等引擎，mysql只提供表級鎖，分為讀鎖和寫鎖，可透過lock tables手工獲取，而DML會自動獲取寫鎖；

下面著重介紹lock tables的用法

 

所需許可權

使用者必須擁有lock tables和select許可權；

 

選項

Read [local]—可由多個會話同時獲取，local用於MyISAM，允許併發插入

[low_priority] write—阻止其他會話訪問該表，通常寫鎖比讀鎖優先順序要高，如果某表已經被新增讀鎖，此時其他會話申請寫鎖，則所有後續讀鎖請求會阻塞直至寫鎖釋放，這樣會影響讀鎖併發；

而low_priority則允許其他讀鎖優先執行，使用該選項必須謹慎，很容易導致寫鎖飢餓等待；

對於autocommit=0的innodb表，low_priority選項無效，視為普通寫鎖，會導致後續讀鎖請求等待

 

演算法

1 對候選基表採用內部演算法排序，使用者無法干預此行為

2 如果表同時請求讀鎖和寫鎖，優先處理寫鎖，除非使用了low_priority，但此選項對開啟事務的innodb無效

3 一次只鎖定一個表，但釋放時同時執行

 

釋放表鎖

1  呼叫unlock tables，必須由執行Lock tables的同一會話執行

2  若會話再次呼叫lock tables，則其原本獲取到的鎖會被隱式釋放

3   開啟事務會隱含呼叫unlock tables，諸如start transaction

4   佔有鎖的會話一旦終止其鎖會被自動釋放，事務也會被回滾

5   rollback不會釋放表鎖

 

 

表鎖的限制

可用於鎖定檢視，即鎖定該檢視所有基表；

表鎖僅阻止其他會話進行讀寫操作，若當前會話擁有某個表的鎖，可執行drop table，但不可執行truncate table；

當對錶進行insert delayed時不可對其新增表鎖，否則insert delayed會報錯，只因該insert由一個單獨執行緒操作；

所有的單個update語句會預設獲取表鎖；

如要對一系列MyISAM表進行大量操作，可提前鎖定表以提升效能，因為Mysql對加鎖的表不會重新整理其key cache；

預設寫鎖優先順序較高，但可透過low_priority_updates/max_write_lock_count/high_priority等手段提升讀鎖優先順序，也可修改mysys/thr_lock程式碼只使用一個佇列，讓寫鎖/讀鎖擁有相同優先順序；

 

對於非事務引擎表，因為不支援select … for update，必須先鎖定表，然後查詢並更新

LOCK TABLES trans READ, customer WRITE;

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

UPDATE customer

  SET total_value=sum_from_previous_statement

  WHERE customer_id=some_id;

UNLOCK TABLES

 

必須一次鎖定所有要訪問的表，否則會抱錯，而information_schema下的表不需要顯示鎖定也可訪問

mysql> LOCK TABLES t1 READ;

mysql> SELECT COUNT(*) FROM t1;

+----------+

| COUNT(*) |

+----------+

|        3 |

+----------+

mysql> SELECT COUNT(*) FROM t2;

ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

 

不能在一條sql中引用2次被鎖定的表，即便使用也必須改用alias別名

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

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

 

 

表鎖與事務

Lock tables會隱式提交當前事務，unlock tables只有在已經呼叫lock tables的前提下才會隱式commit；

開啟事務(諸如start transaction)會釋放當前獲取的表鎖；

Flush tables with read lock獲取的是全域性鎖，而非表鎖；

 

表鎖與InnoDB

對於Innodb表，若要使用表鎖，必須先設定autocommit=0且innodb_table_locks=1(預設)，否則InnoDB無法偵測表鎖而Mysql也無法感知行鎖的存在；

且在事務提交後再unlock tables，如下例所示：

呼叫lock tables時，innodb引擎會獲取內部表鎖，然後mysql伺服器獲取表鎖；commit時innodb釋放內部表鎖，unlock tables時mysql伺服器才釋放表鎖；

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

SET autocommit=0;

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

COMMIT;

UNLOCK TABLES

 

Lock tables與觸發器

如果基表上建有觸發器，且觸發器引用到了其他表，則鎖定基表時會連帶鎖定這些被引用的表

 

LOCK TABLES t1 WRITE, t2 READ;

 

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW

BEGIN

  UPDATE t4 SET count = count+1

      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);

  INSERT INTO t2 VALUES(1, 2);

END;

--t1新增寫鎖

--t2 新增寫鎖，即便是申請的讀鎖，因為在trigger有insert操作，故被轉換為寫鎖

--t3讀鎖

--t4寫鎖 

 

表鎖與分割槽表

對於MyISAM引擎，DML和lock tables會鎖定整個分割槽表，create view/alter table只會鎖住使用的分割槽，5.6.6引入了partition lock prunning改善了此功能；

而InnoDB，在分割槽修剪前不會實施行級鎖，故不存在鎖定整個分割槽的問題；

 

 

表鎖統計資訊

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+