mysql鎖了怎么解決 mysql鎖是怎么實現(xiàn)的

解決一次mysql死鎖問題

多線程開啟事務(wù)處理。每個事務(wù)有多個update操作和一個insert操作（都在同一張表）。

10年積累的成都網(wǎng)站設(shè)計、成都網(wǎng)站制作經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你，你也不認識我。但先做網(wǎng)站后付款的網(wǎng)站建設(shè)流程，更有曾都免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數(shù)據(jù)

邏輯刪除原有數(shù)據(jù)

插入新的數(shù)據(jù)

根據(jù)現(xiàn)有數(shù)據(jù)情況，update的時候沒有數(shù)據(jù)被更新

報了非常多一樣的錯

發(fā)現(xiàn)居然有死鎖。

根據(jù)常識考慮，我每個線程（事務(wù)）更新的數(shù)據(jù)都不沖突，為什么會產(chǎn)生死鎖？

帶著這個問題，打印mysql最近一次的死鎖信息

show engine innodb status

顯示如下

發(fā)現(xiàn)事務(wù)1在等待一個鎖

事務(wù)2也在等待一個鎖

而且事物2持有了事物1需要的鎖

關(guān)于鎖的描述，出現(xiàn)了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說明了什么？說明我關(guān)于mysql的鎖相關(guān)的知識儲備還不夠。那就開始調(diào)查mysql的鎖相關(guān)知識。

通過搜索引擎，

鎖的持有兼容程度如下表

那么再回到死鎖日志，可以知道 :

事務(wù)1正在獲取插入意向鎖

事務(wù)2正在獲取插入意向鎖，持有排他gap鎖

再看我們上面的鎖兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那么就可以推斷出： 事務(wù)1持有g(shù)ap lock，等待事務(wù)2的insert intention lock釋放；事務(wù)2持有g(shù)ap lock，等待事務(wù)1的insert intention lock釋放，從而導致死鎖。

那么新的問題就來了，事務(wù)1的intention lock 為什么會和事務(wù)2的gap lock 有交集，或者說，事務(wù)1要插入的數(shù)據(jù)的位置為什么會被事務(wù)2給鎖住？

讓我回顧一下gap lock的定義：

間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當前讀，出現(xiàn)幻讀的情況

那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發(fā)現(xiàn)自己相關(guān)的知識儲備還不夠。那就開始調(diào)查。

調(diào)查后發(fā)現(xiàn)，當當前索引是一個 普通索引 的時候，會加一個gap lock來防止幻讀， 此gap lock 會鎖住一個左開右閉的區(qū)間。 假設(shè)索引為xx_idx（xx_id），數(shù)據(jù)分布為1,4,6,8,12，當更新xx_id=9的時候，這個時候gap lock的鎖定記錄區(qū)間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數(shù)據(jù)，當有其他事務(wù)要插入xxid in (9,10,11,12)的數(shù)據(jù)時，就會處于等待獲取鎖的狀態(tài)。

ps：當前索引不是普通索引，而且是唯一索引等其他情況，請參考下面資料

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務(wù)1的執(zhí)行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關(guān)系 這段sql會獲取一個gap lock，范圍(2,11111]

這段sql會獲取一個insert intention lock （waiting）

再看事務(wù)2的執(zhí)行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關(guān)系 這段sql也會獲取一個gap lock，范圍也是(2,11111]（根據(jù)前面的知識，gap lock之間會互相兼容，可以一起持有鎖的）

這段sql也會獲取一個insert intention lock （waiting）

看到這里，基本也就破案了。因為普通索引的關(guān)系，事務(wù)1和事務(wù)2的gap lock的覆蓋范圍太廣，導致其他事務(wù)無法插入數(shù)據(jù)。

重新梳理一下：

所以從結(jié)果來看，一堆事務(wù)被回滾，只有10007數(shù)據(jù)被更新成功

gap lock 導致了并發(fā)處理的死鎖

在mysql默認的事務(wù)隔離級別（repeatable read）下，無法避免這種情況。只能把并發(fā)處理改成同步處理?；蛘邚臉I(yè)務(wù)層面做處理。

共享鎖、排他鎖、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status

mysql經(jīng)常鎖死怎么辦

1）可以使用show processlist查看當前用戶連接

如果是root帳號，你能看到所有用戶的當前連接。如果是其它普通帳號，只能看到自己占用的連接。show processlist;只列出前100條，如果想全列出請使用show full processlist;

2）在構(gòu)造鎖的會話中，使用unlock tables;也可以解鎖

詳解MySQL（InnoDB）如何處理死鎖

鎖是需要事務(wù)結(jié)束后才釋放的。

一個是 MVCC，一個是兩階段鎖協(xié)議。

為什么要并發(fā)控制呢？是因為多個用戶同時操作 MySQL 的時候，為了提高并發(fā)性能并且要求如同多個用戶的請求過來之后如同串行執(zhí)行的一樣（為了解決臟讀、不可重復讀、幻讀）

官方定義：

兩階段鎖協(xié)議是指所有事務(wù)必須分兩個階段對數(shù)據(jù)加鎖和解鎖，在對任何數(shù)據(jù)進行讀、寫操作之前，事務(wù)首先要獲得對該數(shù)據(jù)的封鎖；在釋放一個封鎖之后，事務(wù)不再申請和獲得任何其他封鎖。

對應(yīng)到 MySQL 上分為兩個階段：

但是兩階段鎖協(xié)議不要求事務(wù)必須一次將所有需要使用的數(shù)據(jù)加鎖（innodb在需要的索引列數(shù)據(jù)才鎖行），并且在加鎖階段沒有順序要求，所以這種并發(fā)控制方式會形成死鎖。

MySQL有兩種死鎖處理方式：

死鎖檢測 （默認開啟）

死鎖檢測的原理是構(gòu)建一個以事務(wù)為頂點、鎖為邊的有向圖，判斷有向圖是否存在環(huán)，存在即有死鎖。

回滾

檢測到死鎖之后，選擇插入更新或者刪除的行數(shù)最少的事務(wù)回滾，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。

收集死鎖信息：

減少死鎖：

死鎖解決：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

意向鎖，為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在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;

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

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

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

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

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

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

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

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

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

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

select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動在select之后添加for update

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

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

mysql鎖表問題分析以及解決方法-簡書

項目坑有千千萬，我們靜下心來還是可以找到解決辦法的

最近接了一個由供應(yīng)商留下來的項目，正是周末休息時間突然一個電話說功能用不了，翻看日志發(fā)現(xiàn)是業(yè)務(wù)功能的表被鎖了，我就奇了怪了，天天沒事，突然周末來使兒。一番了解才發(fā)現(xiàn)那個鎖表情況是天天都有的，但是一直沒找到原因，所以DBA運維同事天天充當定時刪除機器，每天早上清除鎖表進程，丟失的數(shù)據(jù)也手動補錄，厲害了，這就是供應(yīng)商做的項目嗎？爛到這個層度（吐槽一番）

回到正題，我們來聊一聊我的解決步驟

mysql表被鎖了怎么解鎖

重啟mysql服務(wù)

執(zhí)行show processlist，找到state，State狀態(tài)為Locked即被其他查詢鎖住。KILL?? 10866。

網(wǎng)站標題：mysql鎖了怎么解決 mysql鎖是怎么實現(xiàn)的
標題URL：http://www.muchs.cn/article28/hggcjp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、App開發(fā)、搜索引擎優(yōu)化、品牌網(wǎng)站制作、ChatGPT、App設(shè)計

廣告

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