mysql中的樂觀鎖和悲觀鎖怎么用

關(guān)于mysql中的樂觀鎖和悲觀鎖面試的時候被問到的概率還是比較大的。

成都創(chuàng)新互聯(lián)服務(wù)項目包括孟連網(wǎng)站建設(shè)、孟連網(wǎng)站制作、孟連網(wǎng)頁制作以及孟連網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，孟連網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到孟連省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

mysql的悲觀鎖：

其實理解起來非常簡單，當(dāng)數(shù)據(jù)被外界修改持保守態(tài)度，包括自身系統(tǒng)當(dāng)前的其他事務(wù)，以及來自外部系統(tǒng)的事務(wù)處理，因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機制，但是也只有數(shù)據(jù)庫層提供的鎖機制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在自身系統(tǒng)中實現(xiàn)了加鎖機制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)。

來點實際的，當(dāng)我們使用悲觀鎖的時候我們首先必須關(guān)閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當(dāng)你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進行提交。

關(guān)閉命令為：set autocommit=0;

悲觀鎖可以使用select…for update實現(xiàn)，在執(zhí)行的時候會鎖定數(shù)據(jù)，雖然會鎖定數(shù)據(jù)，但是不影響其他事務(wù)的普通查詢使用。此處說普通查詢就是平時我們用的：select * from table 語句。在我們使用悲觀鎖的時候事務(wù)中的語句例如：

//開始事務(wù)

begin;/begin work;/start transaction; (三選一)

//查詢信息

select * from order where id=1 for update;

//修改信息

update order set name='names';

//提交事務(wù)

commit;/commit work;(二選一)

此處的查詢語句for update關(guān)鍵字，在事務(wù)中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一條數(shù)據(jù)時會等待其它事務(wù)結(jié)束后才執(zhí)行，一般的SELECT查詢則不受影響。

執(zhí)行事務(wù)時關(guān)鍵字select…for update會鎖定數(shù)據(jù)，防止其他事務(wù)更改數(shù)據(jù)。但是鎖定數(shù)據(jù)也是有規(guī)則的。

查詢條件與鎖定范圍：

1、具體的主鍵值為查詢條件

比如查詢條件為主鍵ID=1等等，如果此條數(shù)據(jù)存在，則鎖定當(dāng)前行數(shù)據(jù)，如果不存在，則不鎖定。

2、不具體的主鍵值為查詢條件

比如查詢條件為主鍵ID1等等，此時會鎖定整張數(shù)據(jù)表。

3、查詢條件中無主鍵

會鎖定整張數(shù)據(jù)表。

4、如果查詢條件中使用了索引為查詢條件

明確指定索引并且查到，則鎖定整條數(shù)據(jù)。如果找不到指定索引數(shù)據(jù)，則不加鎖。

悲觀鎖的確保了數(shù)據(jù)的安全性，在數(shù)據(jù)被操作的時候鎖定數(shù)據(jù)不被訪問，但是這樣會帶來很大的性能問題。因此悲觀鎖在實際開發(fā)中使用是相對比較少的。

mysql的樂觀鎖：

相對悲觀鎖而言，樂觀鎖假設(shè)數(shù)據(jù)一般情況下不會造成沖突，所以在數(shù)據(jù)進行提交更新的時候，才會對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。

一般來說，實現(xiàn)樂觀鎖的方法是在數(shù)據(jù)表中增加一個version字段，每當(dāng)數(shù)據(jù)更新的時候這個字段執(zhí)行加1操作。這樣當(dāng)數(shù)據(jù)更改的時候，另外一個事務(wù)訪問此條數(shù)據(jù)進行更改的話就會操作失敗，從而避免了并發(fā)操作錯誤。當(dāng)然，還可以將version字段改為時間戳，不過原理都是一樣的。

例如有表student，字段：

id,name,version

1 a 1

當(dāng)事務(wù)一進行更新操作：update student set name='ygz' where id = #{id} and version = #{version};

此時操作完后數(shù)據(jù)會變?yōu)閕d = 1,name = ygz,version = 2，當(dāng)另外一個事務(wù)二同樣執(zhí)行更新操作的時候，卻發(fā)現(xiàn)version != 1，此時事務(wù)二就會操作失敗，從而保證了數(shù)據(jù)的正確性。

悲觀鎖和樂觀鎖都是要根據(jù)具體業(yè)務(wù)來選擇使用，本文僅作簡單介紹。

一文詳解-MySQL 事務(wù)和鎖

當(dāng)多個用戶訪問同一份數(shù)據(jù)時，一個用戶在更改數(shù)據(jù)的過程中，可能有其他用戶同時發(fā)起更改請求，為保證數(shù)據(jù)庫記錄的更新從一個一致性狀態(tài)變?yōu)榱硗庖粋€一致性狀態(tài)，使用事務(wù)處理是非常必要的，事務(wù)具有以下四個特性：

MySQL 提供了多種事務(wù)型存儲引擎，如 InnoDB 和 BDB 等，而 MyISAM 不支持事務(wù)。為了支持事務(wù)，InnoDB 存儲引擎引入了與事務(wù)處理相關(guān)的 REDO 日志和 UNDO 日志，同時事務(wù)依賴于 MySQL 提供的鎖機制

事務(wù)執(zhí)行時需要將執(zhí)行的事務(wù)日志寫入日志文件，對應(yīng)的文件為 REDO 日志。當(dāng)每條 SQL 進行數(shù)據(jù)更新操作時，首先將 REDO 日志寫進日志緩沖區(qū)。當(dāng)客戶端執(zhí)行 COMMIT 命令提交時，日志緩沖區(qū)的內(nèi)容將被刷新到磁盤，日志緩沖區(qū)的刷新方式或者時間間隔可以通過參數(shù) innodb_flush_log_at_trx_commit 控制

REDO 日志對應(yīng)磁盤上的 ib_logifleN 文件，該文件默認為 5MB，建議設(shè)置為 512MB，以便容納較大的事務(wù)。MySQL 崩潰恢復(fù)時會重新執(zhí)行 REDO 日志的記錄，恢復(fù)最新數(shù)據(jù)，保證已提交事務(wù)的持久性

與 REDO 日志相反，UNDO 日志主要用于事務(wù)異常時的數(shù)據(jù)回滾，具體內(nèi)容就是記錄數(shù)據(jù)被修改前的信息到 UNDO 緩沖區(qū)，然后在合適的時間將內(nèi)容刷新到磁盤

假如由于系統(tǒng)錯誤或者 rollback 操作而導(dǎo)致事務(wù)回滾，可以根據(jù) undo 日志回滾到?jīng)]修改前的狀態(tài)，保證未提交事務(wù)的原子性

與 REDO 日志不同的是，磁盤上不存在單獨的 UNDO 日志文件，所有的 UNDO 日志均存在表空間對應(yīng)的 .ibd 數(shù)據(jù)文件中，即使 MySQL 服務(wù)啟動了獨立表空間

在 MySQL 中，可以使用 BEGIN 開始事務(wù)，使用 COMMIT 結(jié)束事務(wù)，中間可以使用 ROLLBACK 回滾事務(wù)。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務(wù)

MySQL 定義了四種隔離級別，指定事務(wù)中哪些數(shù)據(jù)改變其他事務(wù)可見、哪些數(shù)據(jù)該表其他事務(wù)不可見。低級別的隔離級別可以支持更高的并發(fā)處理，同時占用的系統(tǒng)資源更少

InnoDB 系統(tǒng)級事務(wù)隔離級別可以使用以下語句設(shè)置：

查看系統(tǒng)級事務(wù)隔離級別：

InnoDB 會話級事務(wù)隔離級別可以使用以下語句設(shè)置：

查看會話級事務(wù)隔離級別：

在該隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。讀取未提交的數(shù)據(jù)稱為臟讀（Dirty Read），即是：首先開啟 A 和 B 兩個事務(wù)，在 B 事務(wù)更新但未提交之前，A 事務(wù)讀取到了更新后的數(shù)據(jù)，但由于 B 事務(wù)回滾，導(dǎo)致 A 事務(wù)出現(xiàn)了臟讀現(xiàn)象

所有事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變，此級別可以解決臟讀，但也會導(dǎo)致不可重復(fù)讀（Nonrepeatable Read）：首先開啟 A 和 B 兩個事務(wù)，A事務(wù)讀取了 B 事務(wù)的數(shù)據(jù)，在 B 事務(wù)更新并提交后，A 事務(wù)又讀取到了更新后的數(shù)據(jù)，此時就出現(xiàn)了同一 A 事務(wù)中的查詢出現(xiàn)了不同的查詢結(jié)果

MySQL 默認的事務(wù)隔離級別，能確保同一事務(wù)的多個實例在并發(fā)讀取數(shù)據(jù)時看到同樣的數(shù)據(jù)行，理論上會導(dǎo)致一個問題，幻讀（Phontom Read）。例如，第一個事務(wù)對一個表中的數(shù)據(jù)做了修改，這種修改會涉及表中的全部數(shù)據(jù)行，同時第二個事務(wù)也修改這個表中的數(shù)據(jù)，這次的修改是向表中插入一行新數(shù)據(jù)，此時就會發(fā)生操作第一個事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行

InnoDB 通過多版本并發(fā)控制機制（MVCC）解決了該問題：InnoDB 通過為每個數(shù)據(jù)行增加兩個隱含值的方式來實現(xiàn)，這兩個隱含值記錄了行的創(chuàng)建時間、過期時間以及每一行存儲時間發(fā)生時的系統(tǒng)版本號，每個查詢根據(jù)事務(wù)的版本號來查詢結(jié)果

通過強制事務(wù)排序，使其不可能相互沖突，從而解決幻讀問題。簡而言之，就是在每個讀的數(shù)據(jù)行上加上共享鎖實現(xiàn)，這個級別會導(dǎo)致大量的超時現(xiàn)象和鎖競爭，一般不推薦使用

為了解決數(shù)據(jù)庫并發(fā)控制問題，如走到同一時刻客戶端對同一張表做更新或者查詢操作，需要對并發(fā)操作進行控制，因此產(chǎn)生了鎖

共享鎖的粒度是行或者元組（多個行），一個事務(wù)獲取了共享鎖以后，可以對鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行讀操作

排他鎖的粒度與共享鎖相同，一個事務(wù)獲取排他鎖以后，可以對鎖定范圍內(nèi)的數(shù)據(jù)執(zhí)行寫操作

有兩個事務(wù) A 和 B，如果事務(wù) A 獲取了一個元組的共享鎖，事務(wù) B 還可以立即獲取這個元組的共享鎖，但不能獲取這個元組的排他鎖，必須等到事務(wù) A 釋放共享鎖之后。如果事務(wù) A 獲取了一個元組的排他鎖，事務(wù) B 不能立即獲取這個元組的共享鎖，也不能立即獲取這個元組的排他鎖，必須等到 A 釋放排他鎖之后

意向鎖是一種表鎖，鎖定的粒度是整張表，分為意向共享鎖和意向排他鎖。意向共享鎖表示一個事務(wù)有意對數(shù)據(jù)上共享鎖或者排他鎖。有意表示事務(wù)想執(zhí)行操作但還沒真正執(zhí)行

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小，同時允許的并發(fā)量也是最小。MyISAM 存儲引擎使用該鎖機制。當(dāng)要寫入數(shù)據(jù)時，整個表記錄被鎖，此時其他讀/寫動作一律等待。一些特定的動作，如 ALTER TABLE 執(zhí)行時使用的也是表鎖

行鎖可以支持最大的并發(fā)，InnoDB 存儲引擎使用該鎖機制。如果要支持并發(fā)讀/寫，建議采用 InnoDB 存儲引擎

面試你應(yīng)該知道的 MySQL 的鎖

背景

數(shù)據(jù)庫的鎖是在多線程高并發(fā)的情況下用來保證數(shù)據(jù)穩(wěn)定性和一致性的一種機制。MySQL 根據(jù)底層存儲引擎的不同，鎖的支持粒度和實現(xiàn)機制也不同。MyISAM 只支持表鎖，InnoDB 支持行鎖和表鎖。目前 MySQL 默認的存儲引擎是 InnoDB，這里主要介紹 InnoDB 的鎖。

使用 InnoDB 的兩大優(yōu)點：一是支持事務(wù)；二是支持行鎖。

在高并發(fā)的情況下事務(wù)的并發(fā)處理會帶來幾個問題

由于高并發(fā)事務(wù)帶來這幾個問題，所以就產(chǎn)生了事務(wù)的隔離級別

舉個例子

按照上面 1，2，3，4 的順序執(zhí)行會發(fā)現(xiàn)第 4 步被阻塞了，必須執(zhí)行完第 5 步后才能插入成功。這里我們會很奇怪明明鎖住的是uid=6 的這一行，為什么不能插入 5 呢？原因就是這里采用了 next-key 的算法，鎖住的是（3,10）整個區(qū)間。感興趣的可以試一下。

今天給大家分享了一下 MySQL 的 InnoDB 的事務(wù)以及鎖的一些知識，通過自己的實際上手實踐對這塊更加熟悉了，希望大家在看的時候也可以動手試試，這樣更能體會，理解的更深刻。

MySQL的鎖分類以及使用場景

InnoDB默認是行級別的鎖，當(dāng)有明確指定的主鍵時候，是行級鎖。否則是表級別。

例子: 假設(shè)表foods ，存在有id跟name、status三個字段，id是主鍵，status有索引。

例1: (明確指定主鍵，并且有此記錄，行級鎖)

例2: (明確指定主鍵/索引，若查無此記錄，無鎖)

例3: (無主鍵/索引，表級鎖)

例4: (主鍵/索引不明確，表級鎖)

for update的注意點

for update的疑問點

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ù)庫而言是尤其重要，也更加復(fù)雜。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í)行主庫同步過來的二進制日志，會導(dǎo)致主從延遲。

解決辦法是在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，當(dāng)對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當(dāng)對表結(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白菜教程（Level 10 - 意向鎖&記錄鎖&間隙鎖）

意向鎖（Intention Locks; table-level lock）

意向鎖是一種特殊的表級鎖，意向鎖是為了讓 InnoDB 多粒度的鎖能共存而設(shè)計的。取得行的共享鎖和排他鎖之前需要先取得表的意向共享鎖（IS）和意向排他鎖（IX），意向共享鎖和意向排他鎖都是系統(tǒng)自動添加和自動釋放的，整個過程無需人工干預(yù)

意向鎖就是指未來的某一個時刻事務(wù)可能要加共享鎖或者排它鎖，提前聲明一個意向，分為兩種：

意向共享鎖（Intention Shared Lock） IS

事務(wù)有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖）

意向排它鎖（Intention Exclusive Lock）IX

事務(wù)有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖）

記錄鎖（Record Locks）

官方原文

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 這一行則是使用了記錄鎖，不允許其他事務(wù)進行增，刪，改

但是 SELECT c1 FROM t WHERE c1 = 10; 是沒有鎖的，走的是快照讀，上文已經(jīng)闡明過了

記錄鎖本身不是鎖定記錄數(shù)據(jù)本身而是鎖定索引記錄，如果要鎖的列沒有索引，則會進行全表記錄加鎖

間隙鎖（Gap Locks）

官方原文

比如 SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE ;

插入 c1 為 15 的記錄會被鎖定不可執(zhí)行

這種默認存在于可重復(fù)讀的事務(wù)隔離級別中的鎖，鎖定被圈定的范圍不允許 insert，防止不可重復(fù)讀，上文說了我們的事務(wù)隔離級別都是讀已提交，默認會產(chǎn)生不可重復(fù)讀的問題

本文標題：mysql鎖怎么使用,mysql鎖怎么實現(xiàn)
轉(zhuǎn)載來于：http://chinadenli.net/article36/dsgohsg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站制作、商城網(wǎng)站、網(wǎng)站排名、營銷型網(wǎng)站建設(shè)、域名注冊、動態(tài)網(wǎng)站

廣告

聲明：本網(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)