一文學(xué)習(xí)MySQL?意向共享鎖、意向排他鎖、死鎖
一、InnoDB表級(jí)鎖
我們知道，InnoDB是支持行鎖，但不是每次都獲取行鎖，如果不使用索引的，那還是獲取的表鎖。而且有的時(shí)候，我們希望直接去使用表鎖
在絕大部分情況下都應(yīng)該使用行鎖，因?yàn)槭聞?wù)的并發(fā)效率比表鎖更高，但個(gè)別情況下也使用表級(jí)鎖：
- 事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，給大部分行都加鎖（此時(shí)不如直接加表鎖），不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長(zhǎng)時(shí)間等待和鎖沖突
- 事務(wù)涉及多個(gè)表，比較復(fù)雜，如果都用行鎖，很可能引起死鎖，造成大量事務(wù)回滾
當(dāng)我們希望獲取表鎖時(shí)，可以使用以下命令：
LOCK TABLE user READ -- 獲取這張表的讀鎖 LOCK TABLE user WRITE -- 獲取這張表的寫鎖 事務(wù)執(zhí)行… COMMIT/ROLLBACK; -- 事務(wù)提交或者回滾 UNLOCK TABLES; -- 本身自帶提交事務(wù)，釋放線程占用的所有表鎖
在使用表鎖的時(shí)候，涉及到效率的問題：
如果我們要獲取一張表的排它鎖X，最起碼得確定，這張表沒有被其他事務(wù)獲取過S鎖或X鎖，以及這張表沒有任何行被其他事務(wù)獲取過行S或X鎖
假如這張表有1000萬個(gè)數(shù)據(jù)，那我怎么知道這1000萬行哪些有行鎖哪些沒有行鎖呢？
除了挨個(gè)檢查，沒有更好的辦法，這就導(dǎo)致效率低下的問題
我們這里學(xué)習(xí)的意向共享鎖和意向排他鎖就是用來解決，由于需要加表鎖而去挨個(gè)遍歷數(shù)據(jù)，確定是否有某些數(shù)據(jù)被加了行鎖，而導(dǎo)致的效率低下問題
二、意向共享鎖和意向排他鎖
為了可以更快速的獲取表鎖
意向共享鎖（IS鎖）：事務(wù)計(jì)劃給記錄加行共享鎖，事務(wù)在給一行記錄加共享鎖前，必須先取得該表的IS鎖
意向排他鎖（IX鎖）：事務(wù)計(jì)劃給記錄加行排他鎖，事務(wù)在給一行記錄加排他鎖前，必須先取得該表的IX鎖
- 在加行鎖之前，由InnoDB存儲(chǔ)引擎加上表的IS或IX鎖
- 意向鎖之間都兼容，不會(huì)產(chǎn)生沖突
- 意向鎖存在的意義是為了更高效的獲取表鎖（表格中的X、S、IX、IS指的是表鎖，不是行鎖）
- 意向鎖是表級(jí)鎖，協(xié)調(diào)表鎖和行鎖的共存關(guān)系，主要目的是顯示事務(wù)正在鎖定某行或者試圖鎖定某行。
分析事務(wù)1獲取行X鎖和事務(wù)2獲取表S鎖：
首先事務(wù)1需要給表的第10行數(shù)據(jù)加X鎖，于是InnoDB存儲(chǔ)引擎自動(dòng)給整張表加上了IX鎖。當(dāng)事務(wù)2再想獲取整張表的S鎖時(shí)，看到這張表已經(jīng)有別的事務(wù)獲取了IX鎖了，就說明這張表肯定有某些數(shù)據(jù)被加上了X鎖，這就導(dǎo)致事務(wù)2不能給整張表加S鎖了。此時(shí)事務(wù)2只能等待，無法成功獲取表S鎖
三、死鎖
1. 數(shù)據(jù)庫(kù)中的死鎖
MyISAM 表鎖是 deadlock free 的， 這是因?yàn)?MyISAM 不支持事務(wù)，只支持表鎖，而且總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。如果是處理多張表，還是可能出現(xiàn)死鎖問題的
在 InnoDB 中，除單個(gè) SQL 組成的事務(wù)外，鎖是逐步獲得的，即鎖的粒度比較?。ㄐ墟i），這就決定了在 InnoDB 中發(fā)生死鎖是可能的
死鎖問題一般都是我們自己的應(yīng)用造成的，和多線程編程的死鎖情況相似，大部分都是由于我們多個(gè)線程在獲取多個(gè)鎖資源的時(shí)候，獲取的順序不同而導(dǎo)致的死鎖問題。因此我們應(yīng)用在對(duì)數(shù)據(jù)庫(kù)的多個(gè)表做更新的時(shí)候，不同的代碼段，應(yīng)對(duì)這些表按相同的順序進(jìn)行更新操作，以防止鎖沖突導(dǎo)致死鎖問題
2. 死鎖場(chǎng)景以及解決辦法
死鎖出現(xiàn)的場(chǎng)景如下：
事務(wù)1成功獲取行鎖1
事務(wù)2成功獲取行鎖2
…
事務(wù)1無法獲取行鎖2，被阻塞的同時(shí)也無法釋放行鎖1
事務(wù)2無法獲取行鎖1，被阻塞的同時(shí)也無法釋放行鎖2
此時(shí)所有的事務(wù)都阻塞住了，相當(dāng)于進(jìn)程內(nèi)的所有線程都阻塞住了，發(fā)生了死鎖問題
解決死鎖辦法：多個(gè)事務(wù)/線程獲取多個(gè)相同資源鎖的時(shí)候應(yīng)該按照同樣的順序獲取鎖。與此同時(shí)，由于mysqld（MySQL Server守護(hù)進(jìn)程）設(shè)置了事務(wù)阻塞的超時(shí)時(shí)間，事務(wù)不會(huì)阻塞很長(zhǎng)時(shí)間，超時(shí)后事務(wù)處理失敗，自動(dòng)釋放當(dāng)前占有的鎖
3. 操作
設(shè)置自動(dòng)提交 以及 可重復(fù)讀隔離級(jí)別，開啟事務(wù)
查詢一下表數(shù)據(jù)，在可重復(fù)讀隔離級(jí)別使用的是MVCC提供的快照讀，并沒有加鎖
事務(wù)1獲取id=7的排他鎖，事務(wù)2獲取id=8的排他鎖
事務(wù)1再次獲取id=8的排他鎖，發(fā)生阻塞
事務(wù)2再次獲取id=7的排他鎖
此時(shí)由于MySQL Server檢測(cè)到發(fā)生了死鎖，于是解除事務(wù)1的阻塞，進(jìn)行事務(wù)1的rollback，釋放其占有的行鎖，于是事務(wù)2成功獲取id=7的排他鎖
四、鎖的優(yōu)化建議
- 在能正確完成業(yè)務(wù)的前提下，為確保效率，盡量使用較低的隔離級(jí)別（必須避免臟讀）
- 設(shè)計(jì)合理的索引并盡量使用索引訪問數(shù)據(jù)，使加鎖更準(zhǔn)確，減少鎖沖突的機(jī)會(huì)，提高并發(fā)能力
- 選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的概率小（事務(wù)越大，包含的SQL越多，可能包含更多的表資源和行資源的鎖，增大了鎖沖突的概率）
- 不同的程序訪問一組表時(shí)，應(yīng)盡量約定以相同的順序訪問各表，對(duì)一個(gè)表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會(huì)
- 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響（其實(shí)等值查詢也會(huì)加間隙鎖）
- 不要申請(qǐng)超過實(shí)際需要的鎖級(jí)別
- 除非必須，查詢時(shí)不要顯示加鎖（在已提交讀和可重復(fù)讀隔離級(jí)別，MVCC提供了讀取機(jī)制，不需要手動(dòng)加鎖）
到此這篇關(guān)于一文學(xué)習(xí)MySQL 意向共享鎖、意向排他鎖、死鎖的文章就介紹到這了,更多相關(guān)mysql意向共享鎖、意向排他鎖、死鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL8.x登陸root用戶突然提示mysql_native_password的實(shí)現(xiàn)
本文主要介紹了MySQL 8.x登陸root用戶突然提示mysql_native_password,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-08-08
有時(shí)候正式庫(kù)和測(cè)試庫(kù)同一個(gè)表有字段有差異,會(huì)造成各種錯(cuò)誤,下面這篇文章主要給大家介紹了關(guān)于SQL多表多字段比對(duì)方法的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-05-05
這篇文章主要介紹了Centos7 下Mysql5.7.19安裝教程詳解,小編認(rèn)為非常不錯(cuò)，特此分享到腳本之家平臺(tái)，需要的朋友參考下吧2017-09-09
Mysql實(shí)現(xiàn)全文檢索、關(guān)鍵詞跑分的方法實(shí)例
這篇文章主要給大家介紹了關(guān)于Mysql實(shí)現(xiàn)全文檢索、關(guān)鍵詞跑分的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-09-09
MySQL 百萬級(jí)分頁優(yōu)化(Mysql千萬級(jí)快速分頁)
MySql 性能到底能有多高？用了php半年多，真正如此深入的去思考這個(gè)問題還是從前天開始。有過痛苦有過絕望，到現(xiàn)在充滿信心2012-11-11
本文提供了詳細(xì)的MySQL安裝步驟,包括下載、安裝、配置環(huán)境變量、啟動(dòng)服務(wù)和初始化數(shù)據(jù)庫(kù),首先,從MySQL官網(wǎng)下載壓縮包并解壓,其次,將bin目錄添加到系統(tǒng)PATH中,接著,創(chuàng)建my.ini文件進(jìn)行配置,并正確放置于指定目錄,通過CMD以管理員身份執(zhí)行安裝和初始化命令2024-10-10
mysql查詢時(shí)offset過大影響性能的原因和優(yōu)化詳解
這篇文章主要給大家介紹了關(guān)于mysql查詢時(shí)offset過大影響性能的原因和優(yōu)化的相關(guān)資料，并在文末跟大家分享了MYSQL中l(wèi)imit,offset的區(qū)別，需要的朋友可以參考借鑒，下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-06-06
這篇文章主要介紹了centos7通過yum安裝mysql的方法，非常不錯(cuò)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2018-11-11
這篇文章主要介紹了mysql誤刪root用戶恢復(fù)方法,本文操作是在mysql5.5版本下完成,其它版本僅作參考,需要的朋友可以參考下2015-02-02
mysql千萬級(jí)數(shù)據(jù)分頁查詢性能優(yōu)化
本文給大家分享的是作者在使用mysql進(jìn)行千萬級(jí)數(shù)據(jù)量分頁查詢的時(shí)候進(jìn)行性能優(yōu)化的方法，非常不錯(cuò)的一篇文章，對(duì)我們學(xué)習(xí)mysql性能優(yōu)化非常有幫助2017-11-11