MySQL對MyISAM和MEMORY引擎實現行表級鎖,對BDB存儲引擎進行頁級鎖,對InnDB存儲引擎表進行行行級鎖。
按照粒度分:從大到小(MySQL僅支持表級鎖,行鎖需要存儲引擎完成;所有引擎都有自己鎖策略)
表鎖:鎖定整張表,開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突概率最高,並發度最低。
頁鎖:鎖定一個數據塊(數據頁面)。開銷和加鎖時間介於表鎖行鎖之間,會出現死鎖;鎖定粒度介於表鎖和行鎖之間,並發度一般。
行鎖:鎖定一個行。開銷大,加鎖慢;會出現死鎖;鎖定粒度介於表鎖和行鎖之間,並發度一般。
一般來說,表鎖適合以查詢為主,只有少量按索引條件愛你更新數據的應用,如web應用。
行級鎖適合大量按索引條件並發更新少量不同數據,同時又有並發查詢能力的應用,如一些在線事務系統(OLTP)
手動加鎖:lock tables 表名 [read|write]
mysql>lock table t9 read;
Query OK, 0 rows affected (0.00 sec)
mysql> show global status like"table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 122 | 發生表鎖定操作, 但表鎖定後馬上釋放
| Table_locks_waited | 0 | 發生表鎖定,並因此具有鎖等待
+-----------------------+-------+
2 rows in set (0.00 sec)
mysql>lock table t9 write;
Query OK, 0 rows affected (0.00 sec)
mysql>unlock tables;
Query OK, 0 rows affected (0.00 sec)
MyISAM存儲引擎只支持表鎖。
mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 1258 |
|Table_locks_waited | 0 | 鎖等待
| Table_open_cache_hits | 99 |
| Table_open_cache_misses | 1 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
如果說TABLE_LOCKS_WAITED的值比較高,則說明存在著較嚴重的爭用情況。
mysql>lock table emp read local
Query OK, 0 rows affected (0.00 sec)
如果在locktables 時加了local,作用是在滿足MyISAM表並發插入條件的情況下,允許其他用戶在表尾插入記錄。
mysql>select * from emp1;
ERROR 1100 (HY000): Table 'emp1' was notlocked with LOCK TABLES
在使用locktables 給表加鎖時,必須同事取得所有涉及表的鎖,並且MySQL不支持鎖升級。也就是說,如果是讀鎖,只能執行查詢操作,不能執行更新操作。
mysql>update emp set store_id=30 where id=25;
ERROR 1099 (HY000): Table 'emp' was lockedwith a READ lock and can't be updated
並且不能通過別名進行訪問,所以需要對別名也要加鎖。
mysql>select a.id from emp a;
ERROR 1100 (HY000): Table 'a' was notlocked with LOCK TABLES
MyISAM表的讀和寫是串行的,但是在一定條件下,MyISAM表也會支持查詢和插入操作的並發進行。
MyISAM存儲引擎有一個系統變量concurrent_insert,是專門用來控制其並發插入的行為,值分別為0、1、2。
當為0時:不允許並發插入。
當為1時:如果MyISAM表中沒有空洞,MyISAM允許在一個進程讀取表,另一個進程從表尾插入記錄。(默認)無論MyISAM表中有沒有空洞都允許在表尾並發插入記錄。
MyISAM存儲引擎的讀寫鎖是互斥的,如果一個進程請求某個MyISAM表的讀鎖,同時另一個進程請求同一個表的寫鎖,那麼MySQL會讓寫進程先獲得鎖。不僅如此,即使讀請求先到鎖等待隊列,寫請求後到,寫鎖也會插入到讀鎖請求之前。這是因為MySQL認為寫請求比一般的讀請求重要。因此,大量的更新操作會造成查詢操作很難獲得讀鎖,從而可能永遠阻塞。這種情況非常糟糕!
幸好我們可以通過一些設置來調節MyISAM的調度行為。
通過指定啟動參數low-priority-updates,使MyISAM引擎默認給予讀請求,以有線的權利。
通過執行命令SETLOW_PRIORITY_UPDATES=1,使該鏈接發出的更新請求優先級降低。
通過指定 insert、update、delete語句的LOW_PRIORITY屬性,降低該語句的優先級。
並且也可以通過給系統參數max_write_lock_count設置一個合適的值,當一個表的讀鎖到達這個值後,MySQL就暫時將寫請求的優先級降低,給讀進程一個獲得鎖的機會。
這裡強調一點:一個需要長時間的運行的查詢操作,也會使寫進程“餓死”!因此要盡量避免長時間查詢操作。