mysql資料庫中鎖機制的詳細介紹

本篇文章給大家帶來的內容是關於mysql資料庫中鎖機制的詳細介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有所協助。

悲觀鎖與樂觀鎖：
悲觀鎖：顧名思義，就是很悲觀，每次去拿資料的時候都認為別人會修改，所以每次在拿資料的時候都會上鎖，這樣別人想拿這個資料就會block直到它拿到鎖。傳統的關係型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

樂觀鎖：顧名思義，就是很樂觀，每次去拿資料的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個資料，可以使用版本號碼等機制。樂觀鎖適用於多讀的應用類型，這樣可以提高輸送量，像資料庫如果提供類似於write_condition機制的其實都是提供的樂觀鎖。

表級：引擎 MyISAM，直接鎖定整張表，在你鎖定期間，其它進程無法對該表進行寫操作。如果你是寫鎖，則其它進程則讀也不允許

頁級：引擎 BDB，表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄

行級：引擎 INNODB， 僅對指定的記錄進行加鎖，這樣其它進程還是可以對同一個表中的其它記錄進行操作。

上述三種鎖的特性可大致歸納如下：
1） 表級鎖：開銷小，加鎖快；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高，並發度最低。
2） 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死結；鎖定粒度界於表鎖和行鎖之間，並發度一般。
3） 行級鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低，並發度也最高。

三種鎖各有各的特點，若僅從鎖的角度來說，表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如WEB應用；行級鎖更適合於有大量按索引條件並發更新少量不同資料，同時又有並發查詢的應用，如一些線上交易處理（OLTP）系統。

MySQL表級鎖有兩種模式：
1、表共用讀鎖（Table Read Lock）。對MyISAM表進行讀操作時，它不會阻塞其他使用者對同一表的讀請求，但會阻塞 對同一表的寫操作；
2、表獨佔寫鎖（Table Write Lock）。對MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作。

MyISAM表的讀和寫是串列的，即在進行讀操作時不能進行寫操作，反之也是一樣。但在一定條件下MyISAM表也支援查詢和插入的操作的並發進行，其機制是通過控制一個系統變數（concurrent_insert）來進行的，當其值設定為0時，不允許並發插入；當其值設定為1時，如果MyISAM表中沒有空洞（即表中沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄；當其值設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。

MyISAM鎖調度是如何?的呢，這也是一個很關鍵的問題。例如，當一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，此時mysql將會如優先處理進程呢？通過研究表明，寫進程將先獲得鎖（即使讀請求先到鎖等待隊列）。但這也造成一個很大的缺陷，即大量的寫操作會造成查詢操作很難獲得讀鎖，從而可能造成永遠阻塞。所幸我們可以通過一些設定來調節MyISAM的調度行為。我們可通過指定參數low-priority-updates，使MyISAM預設引擎給予讀請求以優先的權利，設定其值為1（set low_priority_updates=1),使優先順序降低。

InnoDB鎖與MyISAM鎖的最大不同在於：
1、是支援事務（TRANCSACTION）。
2、是採用了行級鎖。

我們知道事務是由一組SQL語句組成的邏輯處理單元，其有四個屬性（簡稱ACID屬性），分別為：
原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全部執行，要麼全都不執行；
一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態；
隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並行作業影響的“獨立”環境執行；
持久性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

並發交易處理帶來的問題
相對於串列處理來說，並發交易處理能大大增加資料庫資源的利用率，提高資料庫系統的事務輸送量，從而可以支援更多的使用者。但並發交易處理也會帶來一些問題，主要包括以下幾種情況。
1、更新丟失（Lost Update）：當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題－－最後的更新覆蓋了由其他事務所做的更新。例如，兩個編輯人員製作了同一文檔的電子副本。每個編輯人員獨立地更改其副本，然後儲存更改後的副本，這樣就覆蓋了原始文檔。最後儲存其更改副本的編輯人員覆蓋另一個編輯人員所做的更改。如果在一個編輯人員完成並提交事務之前，另一個編輯人員不能訪問同一檔案，則可避免此問題。
2、髒讀（Dirty Reads）：一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“髒”資料，並據此做進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象地叫做”髒讀”。
3、不可重複讀取（Non-Repeatable Reads）：一個事務在讀取某些資料後的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重複讀取”。
4、幻讀（Phantom Reads）：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”。

交易隔離等級
在上面講到的並發交易處理帶來的問題中，“更新丟失”通常是應該完全避免的。但防止更新丟失，並不能單靠資料庫事務控制器來解決，需要應用程式對要更新的資料加必要的鎖來解決，因此，防止更新丟失應該是應用的責任。
“髒讀”、“不可重複讀取”和“幻讀”，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。資料庫實現事務隔離的方式，基本上可分為以下兩種。
1、一種是在讀取資料前，對其加鎖，阻止其他事務對資料進行修改。
2、另一種是不用加任何鎖，通過一定機制產生一個資料請求時間點的一致性資料快照（Snapshot)，並用這個快照來提供一定層級（語句級或事務級）的一致性讀取。從使用者的角度來看，好像是資料庫可以提供同一資料的多個版本，因此，這種技術叫做資料多版本並發控制（MultiVersion Concurrency Control，簡稱MVCC或MCC），也經常稱為多版本資料庫。

資料庫的事務隔離越嚴格，並發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上 “序列化”進行，這顯然與“並發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重複讀取”和“幻讀”並不敏感，可能更關心資料並發訪問的能力。
為瞭解決“隔離”與“並發”的矛盾，ISO/ANSI SQL92定義了4個交易隔離等級，每個層級的隔離程度不同，允許出現的副作用也不同，應用可以根據自己的商務邏輯要求，通過選擇不同的隔離等級來平衡 “隔離”與“並發”的矛盾。表20-5很好地概括了這4個隔離等級的特性。

讀資料一致性及允許的並發副作用
隔離等級 讀資料一致性 髒讀 不可重複讀取 幻讀
未提交讀（Read uncommitted） 最低層級，只能保證不讀取物理上損壞的資料 是 是 是
已提交度（Read committed） 語句級 否 是 是
可重複讀（Repeatable read） 事務級 否 否 是
可序列化（Serializable） 最進階別，事務級 否 否 否

最後要說明的是：各具體資料庫並不一定完全實現了上述4個隔離等級，例如，Oracle只提供Read committed和Serializable兩個標準隔離等級，另外還提供自己定義的Read only隔離等級；SQL Server除支援上述ISO/ANSI SQL92定義的4個隔離等級外，還支援一個叫做“快照”的隔離等級，但嚴格來說它是一個用MVCC實現的Serializable隔離等級。MySQL支援全部4個隔離等級，但在具體實現時，有一些特點，比如在一些隔離等級下是採用MVCC一致性讀，但某些情況下又不是
InnoDB有兩種模式的行鎖：
1）共用鎖定（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的獨佔鎖定。
( Select * from table_name where ……lock in share mode)
2）獨佔鎖定（X）：允許獲得獨佔鎖定的事務更新資料，阻止其他事務取得相同資料集的共用讀鎖和排他寫鎖。(select * from table_name where…..for update)
為了允許行鎖和表鎖共存，實現多粒度鎖機制；同時還有兩種內部使用的意圖鎖定（都是表鎖），分別為意圖共用鎖和意向獨佔鎖定。
1）意圖共用鎖（IS）：事務打算給資料行加行共用鎖定，事務在給一個資料行加共用鎖定前必須先取得該表的IS鎖。
2）意向獨佔鎖定（IX）：事務打算給資料行加行獨佔鎖定，事務在給一個資料行加獨佔鎖定前必須先取得該表的IX鎖。
InnoDB行鎖模式相容性列表
請求鎖模式
是否相容
當前鎖模式 X IX S IS
X 衝突 衝突 衝突 衝突
IX 衝突 相容 衝突 相容
S 衝突 衝突 相容 相容
IS 衝突 相容 相容 相容
如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。
意圖鎖定是InnoDB自動加的，不需使用者幹預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加獨佔鎖定（X）；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共用鎖定或獨佔鎖定。
1、共用鎖定（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。
2、獨佔鎖定（X)：SELECT * FROM table_name WHERE … FOR UPDATE。
InnoDB行鎖是通過給索引上的索引項目加鎖來實現的，這一點MySQL與oracle不同，後者是通過在資料區塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發效能。

查詢表級鎖爭用情況
表鎖定爭奪：
可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：

mysql> show status like ‘table%’; +———————–+——-+ | Variable_name         | Value | +———————–+——-+ | Table_locks_immediate | 2979  | | Table_locks_waited    | 0     | +———————–+——-+ 2 rows in set (0.00 sec))

如果Table_locks_waited的值比較高，則說明存在著較嚴重的表級鎖爭用情況。
InnoDB行鎖爭奪：
可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：

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.01 sec)

MyISAM寫鎖實驗：
對MyISAM表的讀操作，不會阻塞其他使用者對同一表的讀請求，但會阻塞對同一表的寫請求；對MyISAM表的寫操作，則會阻塞其他使用者對同一表的讀和寫操作；MyISAM表的讀操作與寫操作之間，以及寫操作之間是串列的！根據如表20-2所示的例子可以知道，當一個線程獲得對一個表的寫鎖後，只有持有鎖的線程可以對錶進行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。
USER1：

mysql> lock table film_text write;

當前session對鎖定表的查詢、更新、插入操作都可以執行：

mysql> select film_id,title from film_text where film_id = 1001;

USER2：

mysql> select film_id,title from film_text where film_id = 1001;

等待
USER1：
釋放鎖：

mysql> unlock tables;

USER2：
獲得鎖，查詢返回：
InnoDB儲存引擎的共用鎖定實驗

USER1： mysql> set autocommit = 0; USER2： mysql> set autocommit = 0;

USER1：
當前session對actor_id=178的記錄加share mode 的共用鎖定：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER2：
其他session仍然可以查詢記錄，並也可以對該記錄加share mode的共用鎖定：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178lock in share mode;

USER1：
當前session對鎖定的記錄進行更新操作，等待鎖：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

等待
USER2：
其他session也對該記錄進行更新操作，則會導致死結退出：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
USER1：
獲得鎖後，可以成功更新：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1  Changed: 1  Warnings: 0

InnoDB儲存引擎的獨佔鎖定例子

USER1： mysql> set autocommit = 0; USER2： mysql> set autocommit = 0;

USER1：
當前session對actor_id=178的記錄加for update的排它鎖：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

USER2：
其他session可以查詢該記錄，但是不能對該記錄加共用鎖定，會等待獲得鎖：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178;

USER1：
當前session可以對鎖定的記錄進行更新操作，更新後釋放鎖：

mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178;

USER2：
其他session獲得鎖，得到其他session提交的記錄：

mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update;

更新效能最佳化的幾個重要參數
bulk_insert_buffer_size
批量插入緩衝大小,這個參數是針對MyISAM儲存引擎來說的.適用於在一次性插入100-1000+條記錄時,提高效率.預設值是8M.可以針對資料量的大小,翻倍增加.
concurrent_insert
並發插入,當表沒有空洞(刪除過記錄),在某進程擷取讀鎖的情況下,其他進程可以在表尾部進行插入.
值可以設0不允許並發插入, 1當表沒有空洞時,執行並發插入, 2不管是否有空洞都執行並發插入.
預設是1針對錶的刪除頻率來設定.
delay_key_write
針對MyISAM儲存引擎,延遲更新索引.意思是說,update記錄時,先將資料up到磁碟,但不up索引,將索引存在記憶體裡,當表關閉時,將記憶體索引,寫到磁碟.值為 0不開啟, 1開啟.預設開啟.
delayed_insert_limit, delayed_insert_timeout, delayed_queue_size
延遲插入,將資料先交給記憶體隊列,然後慢慢地插入.但是這些配置,不是所有的儲存引擎都支援,目前來看,常用的InnoDB不支援, MyISAM支援.根據實際情況調大,一般預設夠用了。