MySQL中有哪些鎖與事務隔離級別

本篇文章為大家展示了MySQL中有哪些鎖與事務隔離級別，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

　　1、概述

　　(1)鎖的定義

　　鎖是計算機協調多個進程或線程并發訪問某一資源的機制。

　　在數據庫中，除了傳統的計算資源(如CPU、RAM、IO等)的爭用以外，數據也是一種供需要用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜。

　　(2)鎖的分類

　　從性能上分為樂觀鎖和悲觀鎖

　　樂觀鎖：每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據。

　　例子：① 在數據庫的表中加一個version字段，用來記錄每次修改數據的版本號，防止并發修改數據出錯;② CAS原子類。

　　悲觀鎖：每次去拿數據的時候都認為別人會修改，所以每次在拿數據的時候都會上鎖，這樣別人想拿這個數據就會阻塞直到它拿到鎖。

　　例子：synchronized關鍵字。

　　從對數據庫操作的類型分為讀鎖和寫鎖(都屬于悲觀鎖)

　　讀鎖(共享鎖)：針對同一份數據，多個讀操作可以同時進行而不會互相影響。

　　寫鎖(排它鎖)：當前寫操作沒有完成前，它會阻斷其他session的寫鎖和讀鎖。(session：數據庫連接)

　　從對數據操作的粒度分為表鎖和行鎖。

　　2、表鎖與行鎖

　　(1)表鎖(偏讀)

　　表鎖偏向MyISAM存儲引擎，開銷小，加鎖快，無死鎖，鎖定粒度大，發生鎖沖突的概率最高，并發度最低。

　　1)基本操作

　　建表SQL

　　CREATE TABLE `mylock` (

　　`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',

　　`name` varchar(20) DEFAULT NULL COMMENT '名稱',

　　PRIMARY KEY (`id`)

　　) ENGINE=MyISAM DEFAULT CHARSET=utf8

　　插入數據

　　insert into `mylock` (`id`, `name`) values('1','a');

　　insert into `mylock` (`id`, `name`) values('2','b');

　　insert into `mylock` (`id`, `name`) values('3','c');

　　insert into `mylock` (`id`, `name`) values('4','d');

　　手動增加表鎖

　　lock table 表名稱 read(write), 表名稱2 read(write)

　　查看表上過的鎖

　　show open tables

　　刪除表鎖

　　unlock tables

　　2)案例分析(加讀鎖)

　　當前session和其他session都可以讀該表。

　　當前session中插入或者更新鎖定的表都會報錯，其他session插入或更新則會等待。

　　3)案例分析(加寫鎖)

　　當前session對該表的增刪改查都沒問題，其他session對該表的索引操作被阻塞。

　　4)案例結論

　　MyISAM在執行查詢語句前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。

　　① 對MyISAM表的讀操作(加讀鎖)，不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執行其他進程的寫操作。

　　② 對MyISAM表的寫操作(加寫鎖)，會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其他進程的讀寫操作。

　　總結：讀鎖會阻塞寫，但不會阻塞讀。而寫鎖會阻塞讀和寫。

　　(2)行鎖(偏寫)

　　行鎖偏向InnoDB存儲引擎，開銷大，加鎖慢，會出現死鎖，鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。InnoDB和MYISAM的最大不同有兩點：① 支持事務;② 采用了行級鎖。

　　1)行鎖支持事務

　　事務(Transaction)及其ACID屬性

　　事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。

　　原子性(Atomicity)：事務是一個原子操作單元，其對數據的修改，要么全部執都執行，要么全都不執行。

　　一致性(Consistent)：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用于事務的修改，以保持數據的完整性;事務結束時，所有的內部數據結構(如B樹索引或雙向鏈表)也都必須是正確的。

　　隔離性(Isolation)：數據庫系統提供一定的隔離機制，保證事務在不受外部并發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。

　　持久性(Durable)：事務完成之后，它對于數據的修改是永久性的，即使出現系統故障也能夠保持。

　　并發事務處理帶來的問題

　　(溫馨提示：以下部分理論解釋看不懂沒關系，接著往下看，有例子)

　　更新丟失(Lost Update)：當兩個或多個事務選擇同一行，然后基于最初選定的值更新該行時，由于每個事務都不知道其他事務的存在，就會發生丟失更新問題-最后的更新覆蓋了由其他事務所做的更新。(可以用類似樂觀鎖的方案解決)

　　臟讀(Dirty Reads)：一個事務正在對一條記錄做修改，在這個事務完成并提交前，這條記錄的數據就處于不一致的狀態;這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“臟”數據，并據此做進一步的處理，就會產生未提交的數據依賴關系。這種現象被形象的叫做“臟讀”。

　　一句話：事務A讀取到了事務B已經修改但尚未提交的數據，還在這個數據基礎上做了操作。此時，如果B事務回滾，A讀取的數據無效，不符合一致性的要求。

　　不可重復讀(Non-Repeatable Reads)：一個事務在讀取某些數據后的某個時間，再次讀取以前讀過的數據，卻發現其讀出的數據已經發生了改變、或某些記錄已經被刪除了!這種現象就叫做“不可重復讀”。

　　一句話：事務A讀取到了事務B已經提交的修改數據，不符合隔離性。

　　幻讀(Phantom Reads)：一個事務按相同的查詢條件重新讀取以前檢索過的數據，卻發現其他事務插入了滿足其查詢條件的新數據，這種現象就稱為“幻讀”。

　　一句話：事務A讀取到了事務B已經提交的新增數據，不符合隔離性。

　　事務的隔離級別

　　“臟讀”、“不可重復讀”、“幻讀”，其實都是數據庫讀一致性問題，必須由數據庫提供一定的事務隔離機制來解決。

　　數據庫的事務隔離級別越嚴格，并發副作用越小，但付出的性能代價也就越大，因為事務隔離實質上就是使事務在一定程度上“串行化”進行，這顯然與“并發”是矛盾的。

　　查看當前數據的事務隔離級別：show variables like 'tx_isolation'

　　設置事務隔離級別：set tx_isolation='REPEATABLE-READ'

　　MySQL默認的隔離級別是可重復讀。

　　2)行鎖案例分析

　　用下面的表演示，需要開啟事務，session_1更新某一行，session_2同時更新某一行將被阻塞，但是更新其他行正常

　　3)隔離級別案例分析

　　CREATE TABLE `account` (

　　`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵id',

　　`name` varchar(255) DEFAULT NULL COMMENT '名稱',

　　`balance` int(11) DEFAULT NULL COMMENT '金額',

　　PRIMARY KEY (`id`)

　　) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

　　insert into `account` (`id`, `name`, `balance`) values('1','lilei','450');

　　insert into `account` (`id`, `name`, `balance`) values('2','hanmei','16000');

　　insert into `account` (`id`, `name`, `balance`) values('3','lucy','2400');

　　① 讀未提交：

　　a. 打開一個客戶端A，并設置當前事務隔離級別為read uncommitted(讀未提交)，set tx_isolation='read-uncommitted'，查詢account表的初始值。

　　b. 在客戶端A的事務提交之前，打開另一個客戶端B，并設置當前事務隔離級別為read uncommitted，更新account表。

　　c. 這時，雖然客戶端B的事務還沒提交，但是客戶端A就可以查詢到B已經更新的數據。

　　d. 一旦客戶端B的事務因為某種原因回滾，所有的操作都將會被撤銷，那客戶端A查詢到的數據其實就是臟數據。

　　e. 臟數據展現：在客戶端A執行更新語句update account set balance=balance-50 where id=1，lilei的balance沒有變成350，而是400，數據??一致，因為在這過程中，并不知道客戶端B會話回滾了，行數據實際上是450，450-50=400，可以采用讀已提交的隔離級別。

　　② 讀已提交：

　　a. 打開一個客戶端A，并設置當前事務隔離級別為read committed(讀已提交)，set tx_isolation='read-committed'，查詢account表的初始值。

　　b. 在客戶端A的事務提交之前，打開另一個客戶端B，并設置當前事務隔離級別為read committed，更新account表。

　　c. 這時，客戶端B的事務還沒提交，客戶端A不能查詢到B已經更新的數據，解決了臟讀的問題。

　　d. 客戶端B的事務提交。

　　e. 客戶端A執行與上一步相同的查詢，結果與上一步不一致，即產生了不可重復讀的問題。

　　③ 可重復讀

　　a. 打開一個客戶端A，并設置當前事務隔離級別為repeatable read(可重復讀)，set tx_isolation='repeatable-read'，查詢account表的初始值：

　　b. 在客戶端A的事務提交之前，打開另一個客戶端B，并設置當前事務隔離級別為repeatable read，更新account表：

　　c. 在客戶端A查詢account表的所有記錄，與步驟a的查詢結果一致，沒有出現不可重復讀的問題。

　　d. 在客戶端A執行update account set balance=balance-50 where id=1，balance沒有變成350-50=300，lilei的balance值用的是步驟b中的300來算的，所以是250，數據的一致性倒是沒有被破壞。可重復讀的隔離級別下使用了MVCC機制，select操作不會更新版本號，是快照讀(歷史版本);insert、update和delete會更新版本號，是當前讀(當前版本)。

　　e. 重新打開客戶端B，插入一條新數據后提交：

　　f. 在客戶端A查詢account表的所有記錄，沒有查出新增數據，所以沒有出現幻讀：

　　g. 驗證幻讀：在客戶端A執行update account set balance=666 where id=4;能更新成功，再次查詢能查到客戶端B新增的數據：

　　④ 串行化

　　a. 打開一個客戶端A，并設置當前事務隔離級別為serializable(串行化)，set tx_isolation='serializable'，查詢account表的初始值：

　　b. 打開一個客戶端B，并設置當前事務隔離級別為serializable，插入一條記錄報錯，表被鎖了插入失敗，MySQL中事務隔離級別為serializable時會鎖表，因此不會出現幻讀的情況，但這種隔離級別并發性能極低，開發中很少會用到。

　　提問：MySQL默認級別是repeatable-read，有辦法解決幻讀問題嗎?

　　間隙鎖在某些情況下可以解決幻讀問題。要避免幻讀可以用間隙鎖在session_1下面執行update account set name='Zeki' where id>3 and id<=20，則其他session沒法插入這個范圍內的數據。 　　InnoDB的行鎖是針對索引加的鎖，不是針對記錄加的鎖。并且該索引不能失效，否則都會從行鎖升級為表鎖。 　　無索引行鎖升級為表鎖：varchar如果不加 ' '，將導致系統自動轉換類型，行鎖變表鎖，例如：update table set name=Zeki where id=1，這個語句會導致行鎖變表鎖，其他session無法對這個表做操作，5.7之后的版本這樣寫SQL會報錯。 　　鎖定某一行還可以用lock in share mode(共享鎖)和for update(排他鎖)，例如：select * from table where id=1 for update，這樣其他session只能讀這行數據，修改則會被阻塞，直到鎖定行的session提交。 　　4)案例結論

　　InnoDB存儲引擎由于實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一下，但是在整體并發處理能力方面要遠遠優于MyISAM的表級鎖定的。當系統并發量高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢了。但是，InnoDB的行級鎖同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不能比MyISAM高，甚至可能會更差。

　　5)行鎖分析

　　通過檢查InnoDB_row_lock狀態變量來分析系統上的行鎖的爭奪情況：show status like 'innodb_row_lock%'。

　　6)死鎖

　　set tx_isolation='repeatable-read';

　　session_1執行：select * from account where id=1 for update;

　　session_2執行：select * from account where id=2 for update;

　　session_1執行：select * from account where id=2 for update;

　　session_2執行：select * from account where id=1 for update;

　　查看近期死鎖日志信息：show engine innodb status\G。

上述內容就是MySQL中有哪些鎖與事務隔離級別，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。