MYSQL行級(jí)鎖使用方法

當(dāng) web 日志中出現(xiàn)行鎖超時(shí)錯(cuò)誤后，很多開發(fā)都會(huì)找我來排查問題，這里說下問題定位的難點(diǎn)！

創(chuàng)新互聯(lián)建站主要企業(yè)基礎(chǔ)官網(wǎng)建設(shè)，電商平臺(tái)建設(shè)，移動(dòng)手機(jī)平臺(tái)，小程序開發(fā)等一系列專為中小企業(yè)按需定制開發(fā)產(chǎn)品體系；應(yīng)對(duì)中小企業(yè)在互聯(lián)網(wǎng)運(yùn)營(yíng)的各種問題，為中小企業(yè)在互聯(lián)網(wǎng)的運(yùn)營(yíng)中保駕護(hù)航。

1. MySQL 本身不會(huì)主動(dòng)記錄行鎖等待的相關(guān)信息，所以無法有效的進(jìn)行事后分析。

2. 鎖爭(zhēng)用原因有多種，很難在事后判斷到底是哪一類問題場(chǎng)景，尤其是事后無法復(fù)現(xiàn)問題的時(shí)候。

3. 找到問題 SQL 后，開發(fā)無法有效從代碼中挖掘出完整的事務(wù)，這也和公司框架-產(chǎn)品-項(xiàng)目的架構(gòu)有關(guān)，需要靠 DBA 事后采集完整的事務(wù) SQL 才可以進(jìn)行分析。

深入理解MySQL數(shù)據(jù)庫各種鎖（總結(jié)）

MyISAM和InnoDB存儲(chǔ)引擎使用的鎖：

封鎖粒度小：

由于InnoDB存儲(chǔ)引擎支持的是行級(jí)別的鎖，因此意向鎖（因?yàn)橐庀蜴i是表鎖）其實(shí)不會(huì)阻塞除全表掃以外的任何請(qǐng)求。故表級(jí)意向鎖與行級(jí)鎖的兼容性如下所示

參考

參考

行鎖的三種算法：

這條語句阻止其他事務(wù)插入10和20之間的數(shù)字，無論這個(gè)數(shù)字是否存在。 間隙可以跨越0個(gè)，單個(gè)或多個(gè)索引值。

共享鎖：

排他鎖：

樂觀鎖：總是假設(shè)最好的情況，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改（天真）， 操作數(shù)據(jù)時(shí)不會(huì)上鎖 ，但是 更新時(shí)會(huì)判斷在此期間有沒有別的事務(wù)更新這個(gè)數(shù)據(jù)，若被更新過，則失敗重試 ；適用于讀多寫少的場(chǎng)景。

樂觀鎖的實(shí)現(xiàn)方式 有：

關(guān)閉自動(dòng)提交后，我們需要手動(dòng)開啟事務(wù)。

上述就實(shí)現(xiàn)了悲觀鎖，悲觀鎖就是悲觀主義者，它會(huì)認(rèn)為我們?cè)谑聞?wù)A中操作數(shù)據(jù)1的時(shí)候，一定會(huì)有事務(wù)B來修改數(shù)據(jù)1,所以，在第2步我們將數(shù)據(jù)查詢出來后直接加上排它鎖（X）鎖，防止別的事務(wù)來修改事務(wù)1，直到我們commit后，才釋放了排它鎖。

MySQL - for update 行鎖 表鎖

for update 的作用是在查詢的時(shí)候?yàn)樾屑由吓潘i，當(dāng)一個(gè)事務(wù)的操作未完成時(shí)候，其他事務(wù)可以讀取但是不能寫入或更新。

它的典型使用場(chǎng)景是 高并發(fā)并且對(duì)于數(shù)據(jù)的準(zhǔn)確性有很高要求 ，比如金錢、庫存等，一般這種操作都是很長(zhǎng)一串并且開啟事務(wù)的，假如現(xiàn)在要對(duì)庫存進(jìn)行操作，在剛開始讀的時(shí)候是1，然后馬上另外一個(gè)進(jìn)程將庫存更新為0了，但事務(wù)還沒結(jié)束，會(huì)一直用1進(jìn)行后續(xù)的邏輯，就會(huì)有問題，所以需要用for upate 加鎖防止出錯(cuò)。

行鎖的具體實(shí)現(xiàn)算法有三種：record lock、gap lock以及next-key lock。

只在可重復(fù)讀或以上隔離級(jí)別下的特定操作才會(huì)取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 時(shí)，除了基于唯一索引的查詢之外，其它索引查詢時(shí)都會(huì)獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。主鍵索引也屬于唯一索引，所以主鍵索引是不會(huì)使用 gap lock 或 next-key lock

for update 僅適用于InnoDB，并且必須開啟事務(wù)，在begin與commit之間才生效。

select 語句默認(rèn)不獲取任何鎖，所以是可以讀被其它事務(wù)持有排它鎖的數(shù)據(jù)的！

InnoDB 既實(shí)現(xiàn)了行鎖，也實(shí)現(xiàn)了表鎖。

當(dāng)有明確指定的主鍵/索引時(shí)候，是行級(jí)鎖，否則是表級(jí)鎖

假設(shè)表 user，存在有id跟name字段，id是主鍵，有5條數(shù)據(jù)。

明確指定主鍵，并且有此記錄，行級(jí)鎖

無主鍵/索引，表級(jí)鎖

主鍵/索引不明確，表級(jí)鎖

明確指定主鍵/索引，若查無此記錄，無鎖

參考博文：

MySQL從入門到精通(九) MySQL鎖，各種鎖

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制，在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）爭(zhēng)用外，數(shù)據(jù)也是一種供許多用戶共享的資源，如何保證數(shù)據(jù)并發(fā)訪問的一致性，有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素，從這個(gè)角度來說，鎖對(duì)數(shù)據(jù)庫而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫中的所有表。2、表級(jí)鎖，每次操作鎖住整張表。3、行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖，加鎖后整個(gè)實(shí)例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將阻塞。其典型的使用場(chǎng)景就是做全庫的邏輯備份，對(duì)所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對(duì)數(shù)據(jù)庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會(huì)受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進(jìn)制日志，會(huì)導(dǎo)致主從延遲。

解決辦法是在innodb引擎中，備份時(shí)加上--single-transaction參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級(jí)鎖,每次操作會(huì)鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲(chǔ)引擎中。表級(jí)鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨(dú)占寫鎖write lock

語法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接

注意: 讀鎖不會(huì)阻塞其它客戶端的讀，但是會(huì)阻塞其它客戶端的寫，寫鎖既會(huì)阻塞其它客戶端的讀，又會(huì)阻塞其它客戶端的寫。大家可以拿一張表來測(cè)試看看。

元數(shù)據(jù)鎖,在加鎖過程中是系統(tǒng)自動(dòng)控制的，無需顯示使用，在訪問一張表的時(shí)候會(huì)自動(dòng)加上，MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫入操作。為了避免DML和DDL沖突，保證讀寫的正確性。

在MySQL5.5中引入了MDL，當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候，加MDL讀鎖(共享)；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作時(shí)，加MDL寫鎖(排他).

查看元數(shù)據(jù)鎖:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲(chǔ)引擎中。

innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過對(duì)索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，而不是對(duì)記錄加的鎖，對(duì)于行級(jí)鎖，主要分為以下三類:

1、行鎖或者叫record lock記錄鎖，鎖定單個(gè)行記錄的鎖，防止其他事物對(duì)次行進(jìn)行update和delete操作，在RC,RR隔離級(jí)別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個(gè)間隙進(jìn)行insert操作，產(chǎn)生幻讀，在RR隔離級(jí)別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級(jí)別下支持。

innodb實(shí)現(xiàn)了以下兩種類型的行鎖

1、共享鎖 S: 允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。

insert 語句 排他鎖 自動(dòng)添加的

update語句 排他鎖 自動(dòng)添加

delete 語句 排他鎖 自動(dòng)添加

select 正常查詢語句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update

默認(rèn)情況下，innodb在repeatable read事務(wù)隔離級(jí)別運(yùn)行，innodb使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。

間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用的間隙鎖。

本文題目：mysql行級(jí)鎖怎么實(shí)現(xiàn),mysql行級(jí)鎖與表級(jí)鎖
網(wǎng)站URL：http://chinadenli.net/article45/dsehdhi.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、網(wǎng)站收錄、域名注冊(cè)、靜態(tài)網(wǎng)站、自適應(yīng)網(wǎng)站、定制開發(fā)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)