首頁 > 文章 > 資料庫 > MYSQL中的鎖介紹

MYSQL中的鎖介紹

小云云原創: 2018-03-22 13:28:091329瀏覽

在資料庫的鎖定機制中介紹過，在DBMS中，可以依照鎖定的粒度把資料庫鎖定分成行級鎖定(INNODB引擎)、表格層級鎖定(MYISAM引擎)和頁級鎖定(BDB引擎 )。

行級鎖定

行級鎖定是Mysql中鎖定粒度最細的一種鎖，表示只針對目前操作的行進行加鎖。行級鎖能大幅減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖和排他鎖。

特點

開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖定衝突的機率最低，並發度也最高。

表格層級鎖定

表格層級鎖定是MySQL中鎖定粒度最大的一種鎖，表示對目前操作的整個表格加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支援。最常使用的MYISAM與INNODB都支援表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）與表獨佔寫鎖（排他鎖）。

特點

開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖定衝突的機率最高，並發度最低。

頁級鎖定

頁級鎖定是MySQL中鎖定粒度介於行級鎖定和表格級鎖定中間的一種鎖定。表級鎖定速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定鄰近的一組記錄。 BDB支援頁級鎖定

特點

開銷與加鎖時間界於表鎖與行鎖之間；會出現死鎖；鎖定粒度界於表鎖與行鎖之間，並發度一般

MySQL常用儲存引擎的鎖定機制

MyISAM和MEMORY採用表級鎖定(table-level locking)

BDB採用頁面鎖定(page -level locking)或表格級鎖定，預設為頁面鎖定

InnoDB支援行級鎖定(row-level locking)和表格層級鎖定,預設為行級鎖定

#Innodb中的行鎖與表鎖
前面提到過，在Innodb引擎中既支援行鎖也支援表鎖，那麼什麼時候會鎖住整張表，什麼時候或只鎖住一行呢？
InnoDB行鎖是透過為索引上的索引項目加鎖來實現的，這一點MySQL與Oracle不同，後者是透過在資料區塊中對對應資料行加鎖來實現的。 InnoDB這種行鎖實現特點意味著：只有透過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
在實際應用中，要特別注意InnoDB行鎖的這項特性，不然的話，可能導致大量的鎖定衝突，進而影響並發效能。

在不透過索引條件查詢的時候,InnoDB 確實使用的是表格鎖定,而不是行鎖定。

由於MySQL 的行鎖是針對索引加的鎖,不是針對記錄加的鎖,所以雖然是訪問不同行的記錄,但是如果是使用相同的索引鍵,是會出現鎖衝突的。應用設計的時候要注意這一點。

當表有多個索引的時候,不同的事務可以使用不同的索引鎖定不同的行,另外,不論是使用主鍵索引、唯一索引或普通索引,InnoDB 都會使用行鎖來對資料加鎖。

即便在條件中使用了索引欄位,但是否使用索引來檢索資料是由MySQL 透過判斷不同執行計劃的代價來決定的,如果MySQL 認為全表掃效率更高,例如對一些很小的表,它就不會使用索引,這種情況下InnoDB 將使用表鎖,而不是行鎖。因此,在分析鎖定衝突時, 別忘了檢查 SQL 的執行計劃,以確認是否真正使用了索引。

###行級鎖定與死鎖######MyISAM中是不會產生死鎖的，因為MyISAM總是一次獲得所需的全部鎖，要嘛全部滿足，要嘛全部等待。而在InnoDB中，鎖是逐步取得的，就造成了死鎖的可能。 ######在MySQL中，行級鎖定並不是直接鎖定記錄，而是鎖定索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這個主鍵索引；如果一語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，還會鎖定相鄰的鍵值，也就是所謂的next-key locking。 ######當兩個交易同時執行，一個鎖定了主鍵索引，在等待其他相關索引。另一個鎖定了非主鍵索引，在等待主鍵索引。這樣就會發生死鎖。 ######發生死鎖後，InnoDB一般都可以偵測到，並使一個交易釋放鎖定，另一個取得鎖定完成交易。 #########有多種方法可以避免死鎖，這裡只介紹常見的三種######1、如果不同程式會並發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會。 ######2、在同一個交易中，盡可能做到一次鎖定所需的所有資源，減少死鎖產生機率；###

3、對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，透過表級鎖定來減少死鎖產生的機率

行級鎖是Mysql中鎖定粒度最細的一種鎖，行級鎖能大幅減少資料庫操作的衝突。行級鎖分為共享鎖及排他鎖兩種，本文將詳細介紹共享鎖及排他鎖的概念、使用方式及注意事項等。

共享鎖定(Share Lock)

共享鎖定又稱為讀鎖，是讀取操作所建立的鎖定。其他使用者可以並發讀取數據，但任何事務都不能對數據進行修改（獲取數據上的排他鎖），直到已釋放所有共享鎖。

如果交易T對資料A加上共享鎖定後，則其他交易只能對A再加共享鎖，不能加排他鎖。獲準共享鎖的事務只能讀數據，不能修改數據。

用法

SELECT ... LOCK IN SHARE MODE;

#在查詢語句後面增加LOCK IN SHARE MODE，Mysql會對查詢結果中的每行都加共享鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他執行緒也可以讀取使用了共享鎖的表，而這些執行緒讀取的是同一個版本的資料。

排他鎖（eXclusive Lock）

排他鎖又稱寫鎖，如果事務T對資料A加上排他鎖後，則其他事務不能再對A加任任何類型的封鎖。獲準排他鎖的事務既能讀取數據，又能修改數據。

用法

SELECT ... FOR UPDATE;

在查詢語句後面增加FOR UPDATE，Mysql就會對查詢結果中的每行都加排他鎖，當沒有其他執行緒對查詢結果集中的任何一行使用排他鎖時，可以成功申請排他鎖，否則會被阻塞。

意向鎖

意向鎖是表級鎖，其設計目的主要是為了在一個事務中揭示下一行將要被請求鎖的類型。 InnoDB中的兩個表鎖定：

意向共享鎖定（IS）：表示交易準備在資料行中加入共享鎖定，也就是說一個資料行加上共享鎖定前必須先取得該表的IS鎖定

意向排他鎖（IX）：類似上面，表示交易準備給資料行加入排他鎖，說明交易在一個資料行加排他鎖前必須先取得該表的IX鎖。

意向鎖定是InnoDB自動加的，不需要使用者乾預。

對於insert、update、delete，InnoDB會自動為涉及的資料加排他鎖（X）；對於一般的Select語句，InnoDB不會加任何鎖，交易可以透過以下語句為顯示加上共享鎖或排他鎖。

共享鎖定：SELECT ... LOCK IN SHARE MODE;

排他鎖：SELECT ... FOR UPDATE;