Mysql中的行級鎖 表級鎖 頁級鎖

2022-09-23 09:36:53 字數 2224 閱讀 5964

在dbms中,可以按照鎖的粒度把資料庫鎖分為行級鎖(innodb引擎)、表級鎖(myisam引擎)和頁級鎖(bdb引擎 )。

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

開銷大,加鎖慢;會出現死鎖;鎖定粒度最小,發生鎖衝突的概率最低,併發度也最高。

共享鎖(s鎖):共享 (s) 用於不更改或不更新資料的操作(只讀操作),如 select 語句。

如果事務t對資料a加上共享鎖,則其他事務只能對a再加共享鎖,不能加排它鎖。只有事務t可以對a進行讀取和修改,其他事務只能讀取資料而不能修改資料。

共享鎖(s):select * from table_name where ... lock in share mode
排他鎖(x鎖):用於資料修改操作,例如 insert、update 或 delete。確保不會同時同一資源進行多重更新。

如果事務t對資料a加上排它鎖,只有事務t可以對a進行讀取和修改,其他任何事務都不能對a進行讀取和修改。

排他鎖(x):select * from table_name where ... for update
表級鎖是mysql中鎖定粒度最大的一種鎖,表示對當前操作的整張表加鎖,它實現簡單,資源消耗較少,被大部分mysql引擎支援。最常使用的myisam與innodb都支援表級鎖定。表級鎖定分為表共享讀鎖(共享鎖)表獨佔寫鎖(排他鎖)

開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發出鎖衝突的概率最高,併發度最低。

頁級鎖是mysql中鎖定粒度介於行級鎖和表級鎖中間的一種鎖.表級鎖速度快,但衝突多,行級衝突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄。bdb支援頁級鎖

開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間,併發度一般

myisam和memory採用表級鎖(table-level locking)

bdb採用頁面鎖(page-level locking)或表級鎖,預設為頁面鎖

innodb支援行級鎖(row-level locking)和表級鎖,預設為行級鎖

前面提到過,在innodb引擎中既支援行鎖也支援表鎖,那麼什麼時候會鎖住整張表,什麼時候或只鎖住一行呢?

innodb行鎖是通過給索引上的索引項加鎖來實現的,這一點mysql與oracle不同,後者是通過在資料塊中對相應資料行加鎖來實現的。innodb這種行鎖實現特點意味著:只有通過索引條件檢索資料,innodb才使用行級鎖,否則,innodb將使用表鎖!

在實際應用中,要特別注意innodb行鎖的這一特性,不然的話,可能導致大量的鎖衝突,從而影響併發效能。

行級鎖都是基於索引的,如果一條sql語句用不到索引是不會使用行級鎖的,會使用表級鎖。行級鎖的缺點是:由於需要請求大量的鎖資源,所以速度慢,記憶體消耗大。

myisam中是不會產生死鎖的,因為myisam總是一次性獲得所需的全部鎖,要麼全部滿足,要麼全部等待。而在innodb中,鎖是逐步獲得的,就造成了死鎖的可能。

在mysql中,行級鎖並不是直接鎖記錄,而是鎖索引。索引分為主鍵索引和非主鍵索引兩種,如果一條sql語句操作了主鍵索引,mysql就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引,mysql會先鎖定該非主鍵索引,再鎖定相關的主鍵索引。 在update、delete操作時,mysql不僅鎖定where條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking。

當兩個事務同時執行,一個鎖住了逐漸索引在等待其他相關索引,一個鎖定了非主鍵索引,在等待主鍵索引。這樣就會發生死鎖。

發生死鎖後,innodb一般都可以檢測到,並使一個事務釋放鎖回退,另一個獲取鎖完成事務。

有多種方法可以避免死鎖,這裡只介紹常見的三種

1、如果不同程式會併發存取多個表,儘量約定以相同的順序訪問表,可以大大降低死鎖機會。

2、在同一個事務中,儘可能做到一次鎖定所需要的所有資源,減少死鎖產生概率;

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