mysql的鎖機制

鎖概述

mysql鎖機制的特點： 不同存儲引擎支持不同的鎖機制。

MyISAM和MEMORY存儲引擎支持表級鎖；

BDB存儲引擎采用頁面鎖；

InnoDB存儲引擎支持行級鎖。

• 表級鎖：

  開銷小，加鎖快，不會出現死鎖，鎖定粒度大，加鎖沖突概率最高，并發度最低；

  適用于以查詢為主，只有少量按索引條件更新數據的應用，如Web應用；

• 行級鎖

  開銷大，加鎖慢，會出現死鎖，鎖定粒度小，發生鎖沖突的概率最低，并發度最高；

  適合于有大量按索引條件并發更新少量不同數據，同時又有并發查詢的應用，如一些在線事務處理（OLTP）系統。

• 頁面鎖

  開銷和加鎖時間介于表鎖和行鎖之間，會出現死鎖，鎖定粒度介于表鎖和行鎖之間，并發度一般；

MyISAM表鎖

查詢表級鎖的爭用情況

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 19 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)

table_locks_waited的值越大，表級鎖的爭用情況越嚴重

表級鎖的鎖模式

• 表共享讀鎖

• 表共享寫鎖

MyISAM表讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
MyISAM表的寫操作，會阻塞其他用戶對同一表的讀請求和寫請求。
MyISAM表的讀操作和寫操作之間，以及寫操作之間是串行的

如何加表鎖

MyISAM表在執行SELECT語句之前，會自動給涉及到的表加讀鎖，在執行UPDATE、DELETE、INSERT之前，自動給涉及到的表加寫鎖。

當然也可以顯示手動加鎖，用來模擬事務操作。

mysql> lock table tbl_name1 read/write；
mysql> lock tables tbl_name1 read [local], tbl_name2 write [local];

mysql> unlock tables;

注意：對表加鎖之后，使用該表的別名也是不允許的，需要對別名也進行鎖定，如下：

mysql> lock table tbl_name1 as tb1 read;
mysql> select a.id from tbl_name tb1;

MyISAM的并發插入

MyISAM的讀寫操作是串行的，但一定程度上，也是支持查詢和插入的并發進行，但不可以刪除和更新。

在MyISAM引擎中有一個系統變量 concurrent_insert ，專門用來控制并發插入的行為，有三種取值：

0-> 不允許并發插入；
1-> 在MyISAM表沒有孔洞的前提下，允許在讀的同時，另一個進程從尾部插入記錄（默認）
2-> 不論有無空洞，都可以在表尾并發插入

整理空間碎片：

mysql> optimize table tbl_name;

MyISAM的鎖調度

已經知道，MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的，但是，及時讀請求先到達等待隊列，寫請求后到達等待隊列，寫鎖也會插入到讀鎖之前，因為MySQL認為寫操作比讀操作重要。

同時，這也是MyISAM表不適合有大量更新和查詢操作的原因，因為大量的更新和查詢操作會占據鎖等待隊列，讀鎖會被長時間等待。

為了解決這個問題，我們有一些參數設置來調節MyISAM的調度行為。

• 啟動low-priority-updates,使得MyISAM 默認基于讀請求以優先的權利；

• 降低更新請求的的優先級

mysql> set LOW_PRIORITY_UPDATES=1

• 指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級

• 一種折中的方案：給系統參數max_write_locl_count設置一個合適的值，當表的讀操作達到該值之后，MySQL就暫時將寫請求的優先級降低。

InnoDB鎖

InnoDB與MyISAM的最大不同支出在于：1、支持事務；2、采用了行級鎖。

查詢InnoDB行鎖的爭用情況

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.00 sec)

Innodb_row_lock_waits和Innodb_row_lock_time_avg的值越大，表級鎖的爭用情況越嚴重

InnoDB行鎖模式

• 共享鎖（S）

  允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖

• 排他鎖（X）

  允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖

為了行鎖表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖，這兩種意向鎖都是表鎖

• 意向共享鎖（IS）

  事務在給一個數據行加共享鎖之前，必須先取得該表的IS鎖

• 意向排他鎖（IX）

  事務在給一個數據行加排他鎖之前，必須先取得該表的IX鎖

意向鎖是InnoDB自動添加的，不需要用戶干預

對于UPDATE、DELETE、INSERT語句，InnoDB自動給涉及數據集加排他鎖；對于SELECT語句，InnoDB不會加任何鎖。