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支持.根據實際情況調大,一般默認夠用了。

以上是“mysql數據庫中鎖機制的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！