輕松掌握MySQL數(shù)據(jù)庫鎖機制的相關原理[1]

在一個update和insert操作頻繁的表中 少量數(shù)據(jù)測試的時候運行良好 在實際運營中 因數(shù)據(jù)量比較大( 萬條記錄) 會出現(xiàn)死鎖現(xiàn)象 用show processlist查看 可以看到一個update語句狀態(tài)是Locked 一個delete語句狀態(tài)是Sending data 查看了一下參考手冊 把鎖定相關的資料整理下來 以便自己記錄和追蹤該問題的解決情況

創(chuàng)新互聯(lián)公司成立十年來，這條路我們正越走越好，積累了技術與客戶資源，形成了良好的口碑。為客戶提供網(wǎng)站設計制作、成都做網(wǎng)站、網(wǎng)站策劃、網(wǎng)頁設計、域名注冊、網(wǎng)絡營銷、VI設計、網(wǎng)站改版、漏洞修補等服務。網(wǎng)站是否美觀、功能強大、用戶體驗好、性價比高、打開快等等，這些對于網(wǎng)站建設都非常重要，創(chuàng)新互聯(lián)公司通過對建站技術性的掌握、對創(chuàng)意設計的研究為客戶提供一站式互聯(lián)網(wǎng)解決方案，攜手廣大客戶，共同發(fā)展進步。

MySQL 支持對MyISAM和MEMORY表進行表級鎖定 對BDB表進行頁級鎖定 對InnoDB 表進行行級鎖定 在許多情況下 可以根據(jù)培訓猜測應用程序使用哪類鎖定類型最好 但一般很難說出某個給出的鎖類型就比另一個好 一切取決于應用程序 應用程序的不同部分可能需要不同的鎖類型 為了確定是否想要使用行級鎖定的存儲引擎 應看看應用程序做什么并且混合使用什么樣的選擇和更新語句 例如 大多數(shù)Web應用程序執(zhí)行許多選擇 而很少進行刪除 只對關鍵字的值進行更新 并且只插入少量具體的表 基本MySQL MyISAM設置已經(jīng)調節(jié)得很好

在MySQL中對于使用表級鎖定的存儲引擎 表鎖定時不會死鎖的 這通過總是在一個查詢開始時立即請求所有必要的鎖定并且總是以同樣的順序鎖定表來管理

對WRITE MySQL使用的表鎖定方法原理如下

◆ 如果在表上沒有鎖 在它上面放一個寫鎖

◆否則 把鎖定請求放在寫鎖定隊列中

對READ MySQL使用的鎖定方法原理如下

◆如果在表上沒有寫鎖定 把一個讀鎖定放在它上面

◆否則 把鎖請求放在讀鎖定隊列中

當一個鎖定被釋放時 鎖定可被寫鎖定隊列中的線程得到 然后是讀鎖定隊列中的線程

這意味著 如果你在一個表上有許多更新 SELECT語句將等待直到?jīng)]有更多的更新

如果INSERT 語句不沖突 可以自由為MyISAM 表混合并行的INSERT 和SELECT 語句而不需要鎖定

InnoDB 使用行鎖定 BDB 使用頁鎖定 對于這兩種存儲引擎 都可能存在死鎖 這是因為 在SQL語句處理期間 InnoDB 自動獲得行鎖定 BDB 獲得頁鎖定 而不是在事務啟動時獲得

行級鎖定的優(yōu)點

· 當在許多線程中訪問不同的行時只存在少量鎖定沖突

· 回滾時只有少量的更改

· 可以長時間鎖定單一的行

行級鎖定的缺點

· 比頁級或表級鎖定占用更多的內存

· 當在表的大部分中使用時 比頁級或表級鎖定速度慢 因為你必須獲取更多的鎖

· 如果你在大部分數(shù)據(jù)上經(jīng)常進行 GROUP BY 操作或者必須經(jīng)常掃描整個表 比其它鎖定明顯慢很多

· 用高級別鎖定 通過支持不同的類型鎖定 你也可以很容易地調節(jié)應用程序 因為其鎖成本小于行級鎖定

在以下情況下 表鎖定優(yōu)先于頁級或行級鎖定

· 表的大部分語句用于讀取

· 對嚴格的關鍵字進行讀取和更新 你可以更新或刪除可以用單一的讀取的關鍵字來提取的一行

# ; UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;

# ; DELETE FROM tbl_name WHERE unique_key_col = key_value ;

· SELECT 結合并行的INSERT 語句 并且只有很少的UPDATE或 DELETE 語句

· 在整個表上有許多掃描或 GROUP BY 操作 沒有任何寫操作

lishixinzhi/Article/program/MySQL/201311/29594

mysql 的鎖以及間隙鎖

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

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

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

快照讀：就是select

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

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

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

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

用 MySQL 實現(xiàn)分布式鎖，你聽過嗎？

以前參加過一個庫存系統(tǒng)，由于其業(yè)務復雜性，搞了很多個應用來支撐。這樣的話一份庫存數(shù)據(jù)就有可能同時有多個應用來修改庫存數(shù)據(jù)。

比如說，有定時任務域xx.cron，和SystemA域和SystemB域這幾個JAVA應用，可能同時修改同一份庫存數(shù)據(jù)。如果不做協(xié)調的話，就會有臟數(shù)據(jù)出現(xiàn)。

對于跨JAVA進程的線程協(xié)調，可以借助外部環(huán)境，例如DB或者Redis。下文介紹一下如何使用DB來實現(xiàn)分布式鎖。

本文設計的分布式鎖的交互方式如下：

在使用synchronized關鍵字的時候，必須指定一個鎖對象。

進程內的線程可以基于obj來實現(xiàn)同步。obj在這里可以理解為一個鎖對象。如果線程要進入synchronized代碼塊里，必須先持有obj對象上的鎖。這種鎖是JAVA里面的內置鎖，創(chuàng)建的過程是線程安全的。那么借助DB，如何保證創(chuàng)建鎖的過程是線程安全的呢？

可以利用DB中的UNIQUE KEY特性，一旦出現(xiàn)了重復的key，由于UNIQUE KEY的唯一性，會拋出異常的。在JAVA里面，是 SQLIntegrityConstraintViolationException 異常。

transaction_id是事務Id，比如說，可以用

來組裝一個transaction_id，表示某倉庫某銷售模式下的某個條碼資源。不同條碼，當然就有不同的transaction_id。如果有兩個應用，拿著相同的transaction_id來創(chuàng)建鎖資源的時候，只能有一個應用創(chuàng)建成功。

在寫操作頻繁的業(yè)務系統(tǒng)中，通常會進行分庫，以降低單數(shù)據(jù)庫寫入的壓力，并提高寫操作的吞吐量。如果使用了分庫，那么業(yè)務數(shù)據(jù)自然也都分配到各個數(shù)據(jù)庫上了。

在這種水平切分的多數(shù)據(jù)庫上使用DB分布式鎖，可以自定義一個DataSouce列表。并暴露一個 getConnection(String transactionId) 方法，按照transactionId找到對應的Connection。

實現(xiàn)代碼如下：

首先編寫一個initDataSourceList方法，并利用Spring的PostConstruct注解初始化一個DataSource 列表。相關的DB配置從db.properties讀取。

DataSource使用阿里的DruidDataSource。

接著最重要的一個實現(xiàn)getConnection(String transactionId)方法。實現(xiàn)原理很簡單，獲取transactionId的hashcode，并對DataSource的長度取模即可。

連接池列表設計好后，就可以實現(xiàn)往distributed_lock表插入數(shù)據(jù)了。

接下來利用DB的 select for update 特性來鎖住線程。當多個線程根據(jù)相同的transactionId并發(fā)同時操作 select for update 的時候，只有一個線程能成功，其他線程都block住，直到 select for update 成功的線程使用commit操作后，block住的所有線程的其中一個線程才能開始干活。

我們在上面的DistributedLock類中創(chuàng)建一個lock方法。

當線程執(zhí)行完任務后，必須手動的執(zhí)行解鎖操作，之前被鎖住的線程才能繼續(xù)干活。在我們上面的實現(xiàn)中，其實就是獲取到當時 select for update 成功的線程對應的Connection，并實行commit操作即可。

那么如何獲取到呢？我們可以利用ThreadLocal。首先在DistributedLock類中定義

每次調用lock方法的時候，把Connection放置到ThreadLocal里面。我們修改lock方法。

這樣子，當獲取到Connection后，將其設置到ThreadLocal中，如果lock方法出現(xiàn)異常，則將其從ThreadLocal中移除掉。

有了這幾步后，我們可以來實現(xiàn)解鎖操作了。我們在DistributedLock添加一個unlock方法。

畢竟是利用DB來實現(xiàn)分布式鎖，對DB還是造成一定的壓力。當時考慮使用DB做分布式的一個重要原因是，我們的應用是后端應用，平時流量不大的，反而關鍵的是要保證庫存數(shù)據(jù)的正確性。對于像前端庫存系統(tǒng)，比如添加購物車占用庫存等操作，最好別使用DB來實現(xiàn)分布式鎖了。

如果想鎖住多份數(shù)據(jù)該怎么實現(xiàn)？比如說，某個庫存操作，既要修改物理庫存，又要修改虛擬庫存，想鎖住物理庫存的同時，又鎖住虛擬庫存。其實也不是很難，參考lock方法，寫一個multiLock方法，提供多個transactionId的入?yún)?，for循環(huán)處理就可以了。這個后續(xù)有時間再補上。

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

鎖是計算機協(xié)調多個進程或線程并發(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、行級鎖，每次操作鎖住對應的行數(shù)據(jù)。

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

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

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

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

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

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

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

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

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

在MySQL5.5中引入了MDL，當對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當對表結構進行變更操作時，加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;

行級鎖，每次操作鎖住對應的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應用在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: 允許一個事務去讀一行，阻止其他事務獲得相同數(shù)據(jù)集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務更新數(shù)據(jù)，阻止其他事務獲得相同數(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事務隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。

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