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）   事務級否    是
可序列化（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資料庫中鎖機制的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！