MySQ數(shù)據(jù)庫鎖定機(jī)制是什么

本篇內(nèi)容主要講解“MySQ數(shù)據(jù)庫鎖定機(jī)制是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQ數(shù)據(jù)庫鎖定機(jī)制是什么”吧!

各存儲引擎使用三種類型鎖定機(jī)制

• 行級鎖定（row-level）

• 表級鎖定（table-level）

• 頁級鎖定（page-leve） ：頁級鎖定介于行級鎖定與表級鎖定之間。

Mysql數(shù)據(jù)庫中表級鎖定主要是 MyISAM、Memory、CSV 等一些非事務(wù)性存儲引擎，使用行級鎖定主要是 InnoDB 存儲引擎和 NDB Cluster 存儲引擎，頁級鎖定主要是 BerkeleyDB 存儲引擎。

• 讀鎖定，一個新客戶端在申請獲取讀鎖定資源的時候，需要滿足兩個條件：

• 請求鎖定的資源當(dāng)前沒有被寫鎖定

• 寫鎖定等待隊列 （Pending write-lock queue）中沒有更高優(yōu)先級的寫鎖定在等待

• （只影響寫操作）

• 寫鎖定

• （影響讀操作，同時也影響寫操作）

Mysql種主要分 4 中隊列來維護(hù)這兩種鎖定：兩個存放當(dāng)前正在鎖定的讀和寫鎖定信息，另外兩個存放等待中的讀寫鎖定西信息，如下：

• Current read-lock queue (lock->read)

• Pending read-lock queue (lock->read_wait)

• Current write-lock queue (lock->write)

• Pending write-lock queue (lock->write_wait)

• 共享鎖 （有叫做：讀鎖）

• 允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)的排它鎖。

• 排他鎖 （有叫做：寫鎖）

• 允許獲得排它鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)

• 意向共享鎖

• 意向排他鎖

InnoDB 的鎖定是通過在指向數(shù)據(jù)記錄的第一個索引鍵之前和最后一個索引鍵之后的空域空間標(biāo)記鎖定信息實現(xiàn)的。這種鎖定方式被稱為 “NEXT-KEY locking”（間隙鎖）
間隙鎖弱點：鎖定一個范圍之后，即使某些不存在的鍵值也會被無辜鎖定，造成鎖定的時候無法插入鍵值鎖定內(nèi)的任何數(shù)據(jù)。
通過索引實現(xiàn)鎖定的方式存在其他幾個較大的性能隱患：

1. 當(dāng) Query 無法利用索引的時候，InnoDB 會放棄使用 行級鎖定 而改用 表級鎖定 ，造成并發(fā)性能降低；

• 當(dāng) Query 使用的索引并不包含所有過濾條件時，數(shù)據(jù)檢索使用到的索引鍵中的數(shù)據(jù)可能有部分不屬于 Query 的結(jié)果集行列，但是也會被鎖定，因為間隙鎖鎖定的是一個范圍，而不是具體的索引鍵。

• 當(dāng) Query 在使用索引定位數(shù)據(jù)的時候，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同 （索引只是過濾條件的一部分）， 他們一樣會被鎖定。

• 縮短鎖定時間

• 盡量減少大的復(fù)雜 Query，將復(fù)雜 Query 拆分成幾個小的 Query 執(zhí)行。

• 盡可能地建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速。

• 盡量讓 MyISAM 存儲引擎的表只存放必要的信息，控制字段類型。

• 利用合適的機(jī)會優(yōu)化 MyISAM 表數(shù)據(jù)文件。

• 分離能并行的操作

• concurrent_insert = 2，無論 MyISAM 存儲引擎的表數(shù)據(jù)文件的中間部分是否存在因為刪除數(shù)據(jù)而留下的空閑空間，都允許在數(shù)據(jù)文件尾部進(jìn)行 Concurrent Insert。

• concurrent_insert = 1，MyISAM 存儲引擎表數(shù)據(jù)文件中間不存在空閑空間的時候，可以從文件尾部進(jìn)行 Concurrent Insert。

• concurrent_insert = 0， 無論 MyISAM 存儲引擎的表數(shù)據(jù)文件的中間部分是否存在因為刪除數(shù)據(jù)而留下的空閑空間，都不允許 Concurrent Insert。（讀鎖時，不允許插入）

• MyISAM 并非只能完全的串行化，MyISAM 存儲引擎還有一個特性 Concurrent Insert（并發(fā)插入）的特性。

• MyISAM 存儲引擎有一個控制是否打開 Concurrent insert 功能的參數(shù)選項：concurrent_insert 可以設(shè)置為 0/1/2：具體如下：

• 合理利用讀寫優(yōu)先級

• 表級鎖定 默認(rèn)情況下寫優(yōu)先級大于讀，如果讀操作多的時候，可以設(shè)置讀優(yōu)先級高，可設(shè)置參數(shù) low_priority_updates = 1。

• 盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成，從而避免 InnoDB 因為無法通過索引鍵加鎖而升級為表級鎖定

• 合理設(shè)計索引，讓 InnoDB 在索引鍵上加鎖的時候盡可能準(zhǔn)確，盡可能地縮小鎖定范圍，避免造成不必要的鎖定而影響其他 Query 的執(zhí)行。

• 盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件，避免因間隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄。

• 盡量控制事務(wù)大小，減少鎖定的資源量和鎖定的時間長度。

• 在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級別的事務(wù)隔離，減少 MySQL 因為實現(xiàn)事務(wù)隔離級別所帶來的附加成本。

MySQL 內(nèi)部有兩組專用的狀態(tài)變量記錄系統(tǒng)內(nèi)部資源爭用情況。

•  表級鎖定的爭用狀態(tài)變量

mysql> show status like ‘table%’;

• 
  

• Table_locks_immediate：產(chǎn)生表級鎖定的次數(shù)；

• Table_locks_waited：出現(xiàn)表級鎖定爭用而發(fā)生等待的次數(shù)

Table_locks_immediate 值大于 Table_locks_waited 5000 是比較合適的，在大就需要分析問題所在。
兩個狀態(tài)值都是從系統(tǒng)啟動后開始記錄，每出現(xiàn)一次加 1，如果這里 Table_locks_waited 狀態(tài)值比較高，說明表級鎖定爭用嚴(yán)重，需進(jìn)一步分析。

• InnoDB 行級鎖定狀態(tài)變量記錄

sql> show status like ‘innodb_row_lock%’;

• 
  

• Innodb_row_lock_current_waites：當(dāng)前正在等待鎖定的數(shù)量；

• Innodb_row_lock_time：從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度；

• Innodb_row_lock_time_avg：每次等待所花平均時間；

• Innodb_row_lock_time_max：從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間；

• Innodb_row_lock_waits：從系統(tǒng)啟動到現(xiàn)在總等待次數(shù)。

5 個狀態(tài)，比較重要的是 Innodb_row_lock_time_avg（等待平均時長），Innodb_row_lock_waits（等待總次數(shù)）及 Innodb_row_lock_time（等待總時長）

InnoDB除了提供以上 5 個系統(tǒng)狀態(tài)變量外，還提供了更為豐富的即時狀態(tài)信息，實現(xiàn)方法如下：

• 創(chuàng)建 InnoDB Monitor 表來打開 InnoDB 的 monitor 功能

• mysql > create table innodb_monitor(a int) engine=innodb;

• 然后執(zhí)行 ”show innodb status” 查看詳細(xì)信息

到此，相信大家對“MySQ數(shù)據(jù)庫鎖定機(jī)制是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！