mysql資料庫鎖定機制的介紹

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

並發控制

• 資料庫管理系統中的並發控制的任務是確保在多個交易同時訪問資料庫中同一資料時不破壞事務的隔離性和統一性以及資料庫的統一性。

• 封鎖，時間戳，樂觀並發控制和悲觀並發控制是並發控制主要採用的技術手段

鎖定

當並發事務同時存取一個資源時，有可能導致資料不一致，因此需要一種機制來將資料存取順序化，以確保資料庫資料的一致性。鎖定就是其中的一種機制（推薦教學：MySQL教學）

鎖定的分類

• 依照運算劃分，可分為DML鎖，DDL鎖定

• 依鎖定粒度劃分，可分為表格層級鎖定，行級鎖，頁級鎖定（mysql）

• 依鎖定層級劃分，可分為共享鎖，排他鎖

• 以加鎖方式劃分，可分為自動鎖定，顯示鎖定

• 依使用方式劃分，可分為樂觀鎖定，悲觀鎖定

DML鎖定用於保護資料的完整性，其中包括行級鎖（TX鎖），表級鎖定（TM鎖定）。 DDL鎖定用於保護資料庫物件的結構，如表，索引等的結構定義，其中包含排他DDL鎖，共用DDL鎖，可中斷解析鎖定

行級鎖定

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

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

表級鎖定

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

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

頁級鎖定

• ##頁級鎖定是Mysql中鎖定粒度介於行級鎖定和表級鎖定中間的一種鎖。表級鎖定速度快，但是衝突多，行級衝突少，但是速度慢。頁級鎖定中，以此鎖定相鄰的一組紀錄。 BDB支援頁級鎖定

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

MyISAM和MEMORY採用表級鎖定

• #BDB採用頁級鎖定或表級鎖定，預設為頁級鎖定

• #InnoDB支援行級鎖定和表格級鎖定，預設為行級鎖定

• 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一般都可以偵測到，並使一個交易釋放鎖定回退，另一個取得鎖定完成交易

避免死鎖

• 如果不同程式會並發存取多個表，盡量約定以相同的順序存取表，可以大幅降低死鎖機會

• #在同一筆交易中，盡可能做到一次鎖定所需的所有資源，減少死鎖產生機率

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

共享鎖定和排他鎖定

• 行級鎖定是MySQL中鎖定粒度最細的一種鎖，行級鎖能夠大幅減少資料庫操作的衝突。行級鎖定分為共享鎖定和排他鎖定

#1. 共享鎖定

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

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

• 如果事務T對數據A加上共享鎖定後，又對數據進行修改，那麼其他事務將不能獲取共享鎖；同樣的，若多個交易對相同資料取得共享鎖，則任何交易不能對該資料進行修改

• 用法： SSELECT ... LOCK IN SHARE MODE

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

#2. 排他鎖

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

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

3. 意向鎖定

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

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

• 意向排他鎖（IX）：表示交易準備給資料行加入排他鎖，說明交易在一個資料行加排他鎖前必須先去的該表的IX鎖定

• 意向鎖定是InnoDB自動加的，不需要用戶幹預

##總結

對於insert，update，delete，InnoDB會自動為涉及的資料加排他鎖；對於一般的Select語句，InnoDB不會加任何鎖，事務可以透過以下語句將明確加上共用鎖或排他鎖

• 共用鎖定：select ... LOCK IN SHARE MODE

• 排他鎖：SELECT ... FOR UPDATE

#

以上是mysql資料庫鎖定機制的介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！