677
MySQL 鎖
MySQL 支持對 MyISAM 和 MEMORY 表進行表級鎖定，對 InnoDB 表進行行級鎖定。
在許多情況下，可以根據猜測應用程序使用哪類鎖定類型最好，但一般很難說出某個給出的鎖類型就比另一個好。一切取決於應用程序，應用程序的不同部分可能需要不同的鎖類型。
為了確定是否想要使用行級鎖定的存儲引擎，應看看應用程序做什麼並且混合使用什麼樣的選擇和更新語句。例如，大多數Web應用程序執行許多選擇，而很少進行刪除，隻對關鍵字的值進行更新，並且隻插入少量具體的表。基本MySQL MyISAM 設置已經調節得很好。
在 MySQL 中對於使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定並且總是以同樣的順序鎖定表來管理。
對 WRITE，MySQL使用的表鎖定方法原理如下：
如果在表上沒有鎖，在它上麵放一個寫鎖。
否則，把鎖定請求放在寫鎖定隊列中。
對 READ，MySQL使用的鎖定方法原理如下：
如果在表上沒有寫鎖定，把一個讀鎖定放在它上麵。
否則，把鎖請求放在讀鎖定隊列中。
當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然後是讀鎖定隊列中的線程。
這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到沒有更多的更新。
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態變量來分析係統上的表鎖定爭奪：
mysql> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 36 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
Table_locks_immediate 發生表鎖定操作, 但表鎖定後馬上釋放
Table_locks_waited 發生表鎖定, 並因此具有鎖等待
對於 SQL 執行過程中, 需要對 SQL 進行硬解析, 計算 SQL 的運行成本後得到執行計劃, 在執行 SQL 語句的時候需要查詢當前資源是否具有鎖機製, 如果當前資源被鎖定中, 必須等待資源釋放後才能夠繼續執行 SQL.
利用 SCHEMA sbtest.new 作為測試對象. 參考下麵表結構。
mysql> desc sbtest.new;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
下麵兩個例子舉例說明鎖定的發生。
例子 1 讀鎖定
當前具有兩個 session 登錄到 MySQL 服務器中, 簡稱 sessionA 與 sessionB。
sessionA 中執行下麵操作。
sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 36 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
解釋：查詢當前 MySQL 中表鎖定信息。
sessionA> lock table sbtest.new write;
Query OK, 0 rows affected (0.00 sec)
解釋：對測試表 sbtest.new 鎖定，該操作隻會影響其他會話對 sbtest.new 表執行 DDL 及 DML 操作。
sessionA> show global status like "table_locks%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 37 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
解釋：當執行 lock table 操作之後，係統會對 sbtest.new 表執行一次鎖定操作，當完成在表中數據庫頭部標記鎖定資源操作後，釋放鎖。
在當前 sessionA 執行鎖定操作狀態下，不影響 sessionA 對表 sbtest.new 進行增刪改操作，參考例子。
sessionA> insert into sbtest.new values (4),(5),(6);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
sessionA> select * from sbtest.new;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
6 rows in set (0.00 sec)
sessionA> delete from sbtest.new where id > 3;
Query OK, 3 rows affected (0.06 sec)
完成上述操作後， 切換到 sessionB 會話中， 執行下麵操作。
sessionB> select * from sbtest.new;
解釋：當 sessionB 進行表查詢時，由於 sessionA 執行鎖定操作，導致查詢等待，直到鎖定結束為止。
利用管理員創建 sessionC 登錄到 MySQL, 利用 show processlist 命令顯示當前登錄到 MySQL 終端的所有狀態狀態信息。
sessionC> show processlist;
+----+------+---------+------+---------------------------------+--------------------------+
| Id | db | Command | Time | State | Info |
+----+------+---------+------+---------------------------------+--------------------------+
| 1 | NULL | Sleep | 120 | | NULL |
| 2 | NULL | Query | 0 | NULL | show processlist |
| 3 | NULL | Query | 112 | Waiting for table metadata lock | select * from sbtest.new |
+----+------+---------+------+---------------------------------+--------------------------+
3 rows in set (0.00 sec)
要使用 show processlist 必須具有 PROCESS 權限，由於排版關係，返回信息進行部分折斷，從 State 狀態欄中可以清楚看到， ID 3 的會話當前正在處於查詢等待狀態， Waiting for table metadata lock 顯示當前等待狀態信息。
隻要 sessionA 對表執行解鎖操作，sessionB 就能夠重新獲得資源，繼續之前 SQL 操作。
sessionA> unlock tables;
Query OK, 0 rows affected (0.00 sec)
sessionB> select * from sbtest.new;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (29 min 52.91 sec)
例子 2：死鎖
MySQL 在使用 InnoDB 引擎時，默認使用自動提交機製，該機製能夠自動幫我們完成事務，自動提交機製並不能夠為我們提高事務性能，我們稍後進行描述。
認識一下死鎖的發生。我們需要兩個會話，分別是 sessionA 與 sessionB。分別創建兩個表，t_lock.a 與 t_lock.b, t_lock.a 與 t_lock.b 結構一致，如下描述。
mysql> desc t_lock.a;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc t_lock.b;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
測試過程中，sessionA 與 sessionB 需要關閉自動提交功能。
1. sessionA 登錄到數據庫，分別在兩個表中插入測試數據。
sessionA> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
sessionA> insert into t_lock.a values (1,'from sessionA');
Query OK, 1 row affected, 1 warning (0.00 sec)
sessionA> insert into t_lock.b values (1,'from sessionA');
Query OK, 1 row affected, 1 warning (0.00 sec)
sessionA> commit;
Query OK, 0 rows affected (0.04 sec)
2. sessionA 更新 A 表中數據，不提交事務。
sessionA> update t_lock.a set name='new data' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
sessionA> select * from t_lock.a;
+----+----------+
| id | name |
+----+----------+
| 1 | new data |
+----+----------+
1 row in set (0.00 sec)
3. sessionB 更新 B 表數據， 再更新 A 表數據，由於當前 A 表數據被 sessionA 修改中，數據處於保護狀態，導致 sessionB 嚐試修改 A 表數據時候出現鎖定等待。鎖定過程中，係統顯示進程為更新中。
sessionB> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update t_lock.b set name='update data' where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
sessionB> select * from t_lock.b;
+----+------------+
| id | name |
+----+------------+
| 1 | update dat |
+----+------------+
1 row in set (0.00 sec)
sessionB> update t_lock.a set name='update data' where id=1;
sessionA> show full processlist;
+----+------+---------+------+----------+---------------------------------------------------+
| Id | db | Command | Time | State | Info |
+----+------+---------+------+----------+---------------------------------------------------+
| 2 | NULL | Query | 8 | Updating | update t_lock.a set name='update data' where id=1 |
| 3 | NULL | Query | 0 | NULL | show full processlist |
+----+------+---------+------+----------+---------------------------------------------------+
2 rows in set (0.00 sec)
4. 這個時候當 sessionA 嚐試修改 B 表數據，因為 sessionB 當前為鎖定狀態，而且 sessionB 對 B 表中數據具有鎖定狀態中，則出現死鎖。sessionB 會自動終止嚐試修改 A 表數據事務， 並返回下麵錯誤信息。而且下麵兩個事務操作都被終止。
sessionA> update t_lock.b set name='new data' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
sessionB> update t_lock.a set name='update data' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
sessionA> select * from t_lock.a;
+----+----------+
| id | name |
+----+----------+
| 1 | new data |
+----+----------+
1 row in set (0.00 sec)
sessionB> select * from t_lock.b;
+----+------------+
| id | name |
+----+------------+
| 1 | update dat |
+----+------------+
1 row in set (0.00 sec)
最後更新：2017-04-02 16:47:42