天道不一定酬所有勤
但是,天道只酬勤

MySQL中的行級鎖,表級鎖,頁級鎖

開發十年,就只剩下這套架構體系了??!

在計算機科學中,鎖是在執行多線程時用于強行限制資源訪問的同步機制,即用于在并發控制中保證對互斥要求的滿足。

數據庫的鎖機制中介紹過,在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、對于非常容易產生死鎖的業務部分,可以嘗試使用升級鎖定顆粒度,通過表級鎖定來減少死鎖產生的概率;

參考資料

20.3.4 InnoDB行鎖實現方式

(全文完) 歡迎關注『Java之道』微信公眾號
贊(9)
如未加特殊說明,此網站文章均為原創,轉載必須注明出處。HollisChuang's Blog » MySQL中的行級鎖,表級鎖,頁級鎖
分享到: 更多 (0)

評論 3

  • 昵稱 (必填)
  • 郵箱 (必填)
  • 網址
  1. #1

    頁級鎖 那段是不是有錯別字:
    “表級鎖”是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖.表級鎖速度快,但沖突多,行級沖突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄。BDB支持頁級鎖

    淩亂4年前 (2016-03-25)回復
  2. #2

    當兩個事務同時執行,一個鎖住了“逐漸”索引在等待其他相關索引,一個鎖定了非主鍵索引,在等待主鍵索引。這樣就會發生死鎖
    這里好像也有錯別字。

    淩亂4年前 (2016-03-25)回復
    • 感謝提醒,已經全部修改?。?![/給力]

      Hollis_Chuang4年前 (2016-03-28)回復

HollisChuang's Blog

聯系我關于我
大乐透复式返奖 内蒙古快三玩法介绍 盛鑫配资 安徽25选5走势图 龙王捕鱼2技巧 月薪2000元理财 今晚开什么特马资料26 11选5定一胆百分之98准 516棋牌游戏手机充值 重庆幸运农场官方平台 黑龙江22选5开奖结果查询表