Mysql鎖表解鎖

-- 查詢是否鎖表

我們提供的服務有：網站設計、成都網站建設、微信公眾號開發、網站優化、網站認證、臨朐ssl等。為上千余家企事業單位解決了網站和推廣的問題。提供周到的售前咨詢和貼心的售后服務，是有科學管理、有技術的臨朐網站制作公司

show OPEN TABLES ;

-- 查詢進程

show processlist ;

-- 查詢到相對應的進程，然后殺死進程

kill id; -- 一般到這一步就解鎖了

-- 查看正在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- 查看等待鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 解鎖表

UNLOCK TABLES;

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

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

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

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

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

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

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

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

2、釋放鎖 unlock tables 或者關閉客戶端連接

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

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

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

查看元數據鎖:

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

意向鎖，為了避免DML在執行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數據是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖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;

行級鎖，每次操作鎖住對應的行數據，鎖定粒度最小，發生鎖沖突的概率最高，并發讀最高，應用在innodb存儲引擎中。

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

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

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

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

innodb實現了以下兩種類型的行鎖

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

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

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

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

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

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

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

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

mysql 的鎖以及間隙鎖

mysql 為并發事務同時對一條記錄進行讀寫時，提出了兩種解決方案：

1）使用 mvcc 的方法，實現多事務的并發讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數據，還有一種是“當前讀”，一般加鎖實現“當前讀”，或者 insert、update、delete 也是當前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）

快照讀：就是select

當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，處理的都是當前的數據，需要加鎖。

mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機制，保證讀到低于后面事務的數據。但是 select for update 不會觸發 mvcc，它是當前讀。如果后面事務插入數據并提交，那么在 RR 級別就會讀到插入的數據。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當前讀的時候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務的插入操作，以此防止幻讀的發生。

Innodb自動使用間隙鎖的條件：

如何對MySQL數據庫表進行鎖定

服務器由兩種表的鎖定方法：

1.內部鎖定

內部鎖定可以避免客戶機的請求相互干擾——例如，避免客戶機的SELECT查詢被另一個客戶機的UPDATE查詢所干擾。也可以利用內部鎖定機制防止服務器在利用myisamchk或isamchk檢查或修復表時對表的訪問。

語法：

鎖定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCK TABLES

LOCK TABLES為當前線程鎖定表。UNLOCK TABLES釋放被當前線程持有的任何鎖。當線程發出另外一個LOCK TABLES時，或當服務器的連接被關閉時，當前線程鎖定的所有表自動被解鎖。

如果一個線程獲得在一個表上的一個READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖，那么只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個線程等待(沒有超時)直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優先級，以確保更改盡快被處理。這意味著，如果一個線程獲得READ鎖，并且然后另外一個線程請求一個WRITE鎖, 隨后的READ鎖請求將等待直到WRITE線程得到了鎖并且釋放了它。

顯然對于檢查，你只需要獲得讀鎖。再者鐘情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機讀取表。對于修復，你必須獲得些所以防止任何客戶機在你對表進行操作時修改它。

2.外部鎖定

服務器還可以使用外部鎖定（文件級鎖）來防止其它程序在服務器使用表時修改文件。通常，在表的檢查操作中服務器將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統中是禁用的，因為他不能可靠的進行工作。對運行myisamchk或isamchk所選擇的過程取決于服務器是否能使用外部鎖定。如果不使用，則必修使用內部鎖定協議。

如果服務器用--skip-locking選項運行，則外部鎖定禁用。該選項在某些系統中是缺省的，如Linux。可以通過運行mysqladmin variables命令確定服務器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續并運行人和一個實用程序來檢查表。服務器和實用程序將合作對表進行訪問。但是，運行任何一個實用程序之前，應該使用mysqladmin

flush-tables。為了修復表，應該使用表的修復鎖定協議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復表示服務器并不知道，最好關閉服務器。如果堅持是服務器保持開啟狀態，月確保在您使用此表示沒有客戶機來訪問它。必須使用卡黨的鎖定協議告訴服務器是該表不被其他客戶機訪問。

檢查表的鎖定協議

本節只介紹如果使用表的內部鎖定。對于檢查表的鎖定協議，此過程只針對表的檢查，不針對表的修復。

1.調用mysql發布下列語句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name READ;mysqlFLUSH TABLES;

該鎖防止其它客戶機在檢查時寫入該表和修改該表。FLUSH語句導致服務器關閉表的文件，它將刷新仍在告訴緩存中的任何為寫入的改變。

2.執行檢查過程

$myisamchk tbl_name$ isamchk tbl_name

3.釋放表鎖

mysqlUNLOCK TABLES;

如果myisamchk或isamchk指出發現該表的問題，將需要執行表的修復。

修復表的鎖定協議

這里只介紹如果使用表的內部鎖定。修復表的鎖定過程類似于檢查表的鎖定過程，但有兩個區別。第一，你必須得到寫鎖而非讀鎖。由于你需要修改表，因此根本不允許客戶機對其進行訪問。第二，必須在執行修復之后發布FLUSH

TABLE語句，因為myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速緩存，否則服務器不會注意到這個改變。本例同樣適合優化表的過程。

1.調用mysql發布下列語句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name WRITE;mysqlFLUSH TABLES;

2.做數據表的拷貝，然后運行myisamchk和isamchk：

$cp tbl_name.* /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name

--recover選項只是針對安裝而設置的。這些特殊選項的選擇將取決與你執行修復的類型。

3.再次刷新高速緩存，并釋放表鎖：

mysqlFLUSH TABLES;mysqlUNLOCK TABLES;

解決一次mysql死鎖問題

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

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數據

邏輯刪除原有數據

插入新的數據

根據現有數據情況，update的時候沒有數據被更新

報了非常多一樣的錯

發現居然有死鎖。

根據常識考慮，我每個線程（事務）更新的數據都不沖突，為什么會產生死鎖？

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

show engine innodb status

顯示如下

發現事務1在等待一個鎖

事務2也在等待一個鎖

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

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

通過搜索引擎，

鎖的持有兼容程度如下表

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

事務1正在獲取插入意向鎖

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

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

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

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

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

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

那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發現自己相關的知識儲備還不夠。那就開始調查。

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

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

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務1的執行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

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

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

再看事務2的執行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

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

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

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

重新梳理一下：

所以從結果來看，一堆事務被回滾，只有10007數據被更新成功

gap lock 導致了并發處理的死鎖

在mysql默認的事務隔離級別（repeatable read）下，無法避免這種情況。只能把并發處理改成同步處理。或者從業務層面做處理。

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

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

show engine innodb status

分享標題：mysql怎么調試鎖,mysql鎖表操作
當前地址：http://vcdvsql.cn/article12/hshedc.html

成都網站建設公司_創新互聯，為您提供網頁設計公司、建站公司、品牌網站設計、微信小程序、手機網站建設、自適應網站

廣告

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