理解mysql鎖（2）表級鎖定
MyISAM 存儲引擎使用的鎖定機制完全是由 MySQL 提供的表級鎖定實現。
mysql的表級鎖定主要有兩種：寫鎖和讀鎖
對write寫鎖，MySQL使用的表鎖定方法原理如下：
* 如果在表上沒有鎖，在它上面放一個寫鎖。
* 否則，把鎖定請求放在寫鎖定隊列中。
對read讀鎖，MySQL使用的表鎖定方法原理如下：
* 如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。
* 否則，把鎖請求放在讀鎖定隊列中。 www.2cto.com
當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然後是讀鎖定隊列中的線程。這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到沒有更多的更新。
可以通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪：
[sql]
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 18 |
| Table_locks_waited | 0 |
+-----------------------+-------+
在 MySQL 中,主要通過四個隊列來維護這兩種鎖定:兩個存放當前正在鎖定中的讀和寫鎖定信息,另外兩個存放等待中的讀寫鎖定信息,如下:
• Current read-lock queue (lock->read)
• Pending read-lock queue (lock->read_wait)
• Current write-lock queue (lock->write)
• Pending write-lock queue (lock->write_wait)
當客戶端請求寫鎖時，mysql首先檢查在Current write-lock queue是否已經有鎖定相同資源到信息存在，如果Current write-lock queue沒有，則再檢查Pending write-lock queue ，如果在Pending write-lock queue 中找到了，則自己也需要進入該等待隊列；反之，如果在Pending write-lock queue 找不到，則再檢測Current read-lock queue，如果有鎖定存在，則同樣需要進入Pending write-lock queue。如果一開始就檢測到Current write-lock queue中有鎖定相同資源的寫鎖存在，那麼就直接進入Pending write-lock queue。
讀請求和寫等待隊列中的寫鎖請求的優先級規則主要為以下規則決定:
1. 除了 READ_HIGH_PRIORITY 的讀鎖定之外,Pending write-lock queue 中的 WRITE 寫鎖定能夠阻塞所有其他的讀鎖定;
2. READ_HIGH_PRIORITY 讀鎖定的請求能夠阻塞所有 Pending write-lock queue 中的寫鎖定;
3. 除了 WRITE 寫鎖定之外,Pending write-lock queue 中的其他任何寫鎖定都比讀鎖定的優先級低。
表級鎖在下列幾種情況下比行級鎖更優越:
1. 很多操作都是讀表。
2. 在嚴格條件的索引上讀取和更新,當更新或者刪除可以用單獨的索引來讀取得到時:
3. UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
4. DELETE FROM tbl_name WHERE unique_key_col=key_value;
5. SELECT 和 INSERT 語句並發的執行,但是只有很少的 UPDATE 和 DELETE 語句。
6. 很多的掃描表和對全表的 GROUP BY 操作,但是沒有任何寫表。
www.2cto.com
測試：
[sql]
session A
顯示地給t1表加讀鎖定
mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)
自己的讀操作未被阻塞：
mysql> select * from t1;
+------+
| i |
+------+
| 1 |
| 2 |
| 5 |
+------+
www.2cto.com
3 rows in set (0.00 se）
session B
其他進程的讀操作也未被阻塞：
mysql> select * from t1;
+------+
| i |
+------+
| 1 |
| 2 |
| 5 |
+------+
3 rows in set (0.00 sec)
session A
mysql> update t1 set i=3 limit 1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
session B
mysql> update t1 set i=3 limit 1;
直接被阻塞了
session A
解除讀鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
www.2cto.com
session B
在session A釋放鎖定資源後，session B獲得了資源，更新成功
mysql> update t1 set i=3 limit 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session A
獲取讀鎖的時候增加local選項
mysql> lock table t1 read local;
Query OK, 0 rows affected (0.00 sec)
session B
其他session的insert 未被阻塞
mysql> insert into t1 values(6);
Query OK, 1 row affected (0.00 sec)
www.2cto.com
然而，其他session的update被阻塞了
mysql> update t1 set i=3 limit 1;
直接被阻塞鳥
session A
這次加寫鎖
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)
自己的session可以繼續讀：
mysql> select * from t1;
+------+
| i |
+------+
| 3 |
| 2 |
| 5 |
| 6 |
+------+
www.2cto.com
4 rows in set (0.00 sec)
session B：
其他session的讀被阻塞了
mysql> select * from t1;
直接被阻塞鳥
session A
釋放鎖定資源
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
session B
其他session可以獲得資源了
mysql> select * from t1;
+------+
| i |
+------+
| 3 |
| 2 |
| 5 |
| 6 |
+------+
www.2cto.com
4 rows in set (0.00 sec)
session A
通過DDL獲取write_allow_read類型的寫鎖定
mysql> alter table t1 add constraint t1_pk primary key(i);
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
session B
其他session的讀未被阻塞
mysql> select * from t1;
+---+
| i |
+---+
| 2 |
| 3 |
| 5 |
| 6 |
+---+
4 rows in set (0.00 sec)
