程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 每天進步一點點————MySQL鎖

每天進步一點點————MySQL鎖

編輯:MySQL綜合教程

每天進步一點點————MySQL鎖


一、 鎖

MySQL對MyISAM和MEMORY引擎實現行表級鎖,對BDB存儲引擎進行頁級鎖,對InnDB存儲引擎表進行行行級鎖。

按照粒度分:從大到小(MySQL僅支持表級鎖,行鎖需要存儲引擎完成;所有引擎都有自己鎖策略)

表鎖:鎖定整張表,開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突概率最高,並發度最低。

頁鎖:鎖定一個數據塊(數據頁面)。開銷和加鎖時間介於表鎖行鎖之間,會出現死鎖;鎖定粒度介於表鎖和行鎖之間,並發度一般。

行鎖:鎖定一個行。開銷大,加鎖慢;會出現死鎖;鎖定粒度介於表鎖和行鎖之間,並發度一般。

一般來說,表鎖適合以查詢為主,只有少量按索引條件愛你更新數據的應用,如web應用。

行級鎖適合大量按索引條件並發更新少量不同數據,同時又有並發查詢能力的應用,如一些在線事務系統(OLTP)

1. 鎖語句

手動加鎖:lock tables 表名 [read|write]

 

給t9表上只讀鎖

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)

給t9表上寫鎖

mysql>lock table t9 write;

Query OK, 0 rows affected (0.00 sec)

解鎖表

mysql>unlock tables;

Query OK, 0 rows affected (0.00 sec)

2. MyISAM表鎖

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存儲引擎的讀寫鎖是互斥的,如果一個進程請求某個MyISAM表的讀鎖,同時另一個進程請求同一個表的寫鎖,那麼MySQL會讓寫進程先獲得鎖。不僅如此,即使讀請求先到鎖等待隊列,寫請求後到,寫鎖也會插入到讀鎖請求之前。這是因為MySQL認為寫請求比一般的讀請求重要。因此,大量的更新操作會造成查詢操作很難獲得讀鎖,從而可能永遠阻塞。這種情況非常糟糕!

幸好我們可以通過一些設置來調節MyISAM的調度行為。

通過指定啟動參數low-priority-updates,使MyISAM引擎默認給予讀請求,以有線的權利。

通過執行命令SETLOW_PRIORITY_UPDATES=1,使該鏈接發出的更新請求優先級降低。

通過指定 insert、update、delete語句的LOW_PRIORITY屬性,降低該語句的優先級。

並且也可以通過給系統參數max_write_lock_count設置一個合適的值,當一個表的讀鎖到達這個值後,MySQL就暫時將寫請求的優先級降低,給讀進程一個獲得鎖的機會。

這裡強調一點:一個需要長時間的運行的查詢操作,也會使寫進程“餓死”!因此要盡量避免長時間查詢操作。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved