912
[數據庫] 如何加表鎖
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程並不需要用戶幹預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，並非必須如此。
給MyISAM表顯示加鎖，一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。例如，有一個訂單表orders，其中記錄有各訂單的總金額total，同時還有一個訂單明細表order_detail，其中記錄有各訂單每一產品的金額小計subtotal，假設我們需要檢查這兩個表的金額合計是否相符，可能就需要執行如下兩條SQL：
Select sum(total) from orders; Select sum(subtotal) from order_detail; |
這時，如果不先給兩個表加鎖，就可能產生錯誤的結果，因為第一條語句執行過程中，order_detail表可能已經發生了改變。因此，正確的方法應該是：
Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables; |
要特別說明以下兩點內容。
·上麵的例子在LOCK TABLES時加了“local”選項，其作用就是在滿足MyISAM表並發插入條件的情況下，允許其他用戶在表尾並發插入記錄，有關MyISAM表的並發插入問題，在後麵的章節中還會進一步介紹。
·在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，並且MySQL不支持鎖升級。也就是說，在執行LOCK TABLES後，隻能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那麼隻能執行查詢操作，而不能執行更新操作。其實，在自動加鎖的情況下也基本如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。
在如表20-3所示的例子中，一個session使用LOCK TABLE命令給表film_text加了讀鎖，這個session可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；同時，另外一個session可以查詢表中的記錄，但更新就會出現鎖等待。
表20-3 MyISAM存儲引擎的讀阻塞寫例子
|
session_1 |
session_2 |
|
獲得表film_text的READ鎖定 mysql> lock table film_text read; Query OK, 0 rows affected (0.00 sec) |
|
|
當前session可以查詢該表記錄 mysql> select film_id,title from film_text where film_id = 1001; +---------+------------------+ | film_id | title | +---------+------------------+ | 1001 | ACADEMY DINOSAUR | +---------+------------------+ 1 row in set (0.00 sec) |
其他session也可以查詢該表的記錄 mysql> select film_id,title from film_text where film_id = 1001; +---------+------------------+ | film_id | title | +---------+------------------+ | 1001 | ACADEMY DINOSAUR | +---------+------------------+ 1 row in set (0.00 sec) |
|
當前session不能查詢沒有鎖定的表 mysql> select film_id,title from film where film_id = 1001; ERROR 1100 (HY000): Table 'film' was not locked with LOCK TABLES |
其他session可以查詢或者更新未鎖定的表 mysql> select film_id,title from film where film_id = 1001; +---------+---------------+ | film_id | title | +---------+---------------+ | 1001 | update record | +---------+---------------+ 1 row in set (0.00 sec) mysql> update film set title = 'Test' where film_id = 1001; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
當前session中插入或者更新鎖定的表都會提示錯誤： mysql> insert into film_text (film_id,title) values(1002,'Test'); ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated mysql> update film_text set title = 'Test' where film_id = 1001; ERROR 1099 (HY000): Table 'film_text' was locked with a READ lock and can't be updated |
其他session更新鎖定表會等待獲得鎖： mysql> update film_text set title = 'Test' where film_id = 1001; 等待 |
|
釋放鎖 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) |
等待 |
|
|
Session獲得鎖，更新操作完成： mysql> update film_text set title = 'Test' where film_id = 1001; Query OK, 1 row affected (1 min 0.71 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
當使用LOCK TABLES時，不僅需要一次鎖定用到的所有表，而且，同一個表在SQL語句中出現多少次，就要通過與SQL語句中相同的別名鎖定多少次，否則也會出錯！舉例說明如下。
（1）對actor表獲得讀鎖：
mysql> lock table actor read; Query OK, 0 rows affected (0.00 sec) |
（2）但是通過別名訪問會提示錯誤：
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name; ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES |
（3）需要對別名分別鎖定：
mysql> lock table actor as a read,actor as b read; Query OK, 0 rows affected (0.00 sec) |
（4）按照別名的查詢可以正確執行：
mysql> select a.first_name,a.last_name,b.first_name, b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name; +------------+-----------+------------+-----------+ | first_name | last_name | first_name | last_name | +------------+-----------+------------+-----------+ | Lisa | Tom | LISA | MONROE | +------------+-----------+------------+-----------+ 1 row in set (0.00 sec) |
最後更新：2017-04-03 05:39:54