您好,登錄后才能下訂單哦!
小編給大家分享一下MySQL鎖與事物隔離級別是什么,希望大家閱讀完這篇文章后大所收獲,下面讓我們一起去探討吧!
前面我們講了MySQL數(shù)據(jù)庫底層的數(shù)據(jù)結(jié)構(gòu)與算法、MySQL性能優(yōu)化篇一些內(nèi)容。我們再來聊聊MySQL的鎖與事務(wù)隔離級別,分上下兩篇,本篇重點講MySQL的行鎖與事務(wù)隔離級別。
鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制。
在數(shù)據(jù)庫中,除了傳統(tǒng)的計算資源(如CPU、RAM、I/O等)的爭用以外,數(shù)據(jù)也是一種供需要用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題,鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。
對于鎖深入的理解,可以查看《關(guān)于Java中鎖的理解》。
行鎖(Record Locks)
間隙鎖(Gap Locks)
臨鍵鎖(Next-key Locks)
共享鎖/排他鎖(Shared and Exclusive Locks)
意向共享鎖/意向排他鎖(Intention Shared and Exclusive Locks)
插入意向鎖(Insert Intention Locks)
自增鎖(Auto-inc Locks)
預(yù)測鎖,這種鎖主要用于存儲了空間數(shù)據(jù)的空間索引。
每次操作鎖住整張表。
示例表,如下:
# 建表SQLCREATE TABLE mylock ( id INT(11) NOT NULL AUTO_INCREMENT, NAME VARCHAR(20) DEFAULT NULL, PRIMARY KEY(id) ) ENGINE = MyISAM DEFAULT CHARSET = utf8; # 插入數(shù)據(jù)INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('1','a'); INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('2','b'); INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('3','c'); INSERT INTO`test`.`mylock`(`id`,`NAME`) VALUES ('4','d');復(fù)制代碼
lock table 表名稱 read(write), 表名稱2 read(write);復(fù)制代碼
show open tables;復(fù)制代碼
unlock tables;復(fù)制代碼
LOCK TABLE mylock read;復(fù)制代碼
當(dāng)前 session 和其他 seesion 都可以讀該表;
當(dāng)前 session 中插入或者更新鎖定表都會報錯,其他 session 插入或者更新則會等待。
LOCK TABLE mylock WRITE;復(fù)制代碼
當(dāng)前 session 對該表的增刪改查都沒有問題,其他 session 對該表的所有操作都會被阻塞 。
MyISAM 在執(zhí)行查詢語句(SELECT)前,會自動給涉及的所有表加讀鎖;在執(zhí)行增刪改查操作前,會自動給涉及的表加寫鎖。
總結(jié):讀鎖會阻塞寫,但不會阻塞讀;而寫鎖則會把讀和寫都阻塞。
每次操作鎖住一行數(shù)據(jù)。
InnoDB 和 MyISAM 的最大不同點:
事務(wù)是由一組 SQL 語句組成的邏輯處理單元,事務(wù)具有以下四個屬性,通常簡稱為事務(wù)的 ACID屬性。
當(dāng)兩個或多個事務(wù)選擇同一行,然后基于最初選定的值更新該行值,由于每個事務(wù)都不知道其他事務(wù)的存在,就會發(fā)生丟失更新問題,最后的更新覆蓋來其他事務(wù)所做的更新。
一個事務(wù)正在對一條記錄做修改,在這個事務(wù)完成并提交前,這個條記錄的數(shù)據(jù)就處于不一致的狀態(tài);這時另外一個事務(wù)也來讀取同一條記錄,如果不加控制,第二個事務(wù)讀取來這些“臟”數(shù)據(jù),并據(jù)此做進一步的處理,就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象的叫做“臟讀”。
總結(jié):事務(wù)A讀取到來事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù),還在這個數(shù)據(jù)基礎(chǔ)上做來操作。此時,如果事務(wù)B回滾,事務(wù)A讀取的數(shù)據(jù)無效,不符合一致性要求。
一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間,再次讀取以前讀過的數(shù)據(jù),卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生來改變、或某些記錄已經(jīng)被刪除了,這種現(xiàn)象就叫做“不可重復(fù)讀”。
總結(jié):事務(wù)A讀取到了事務(wù)B已經(jīng)提交的修改數(shù)據(jù),不符合隔離性。
一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù),卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù),這種現(xiàn)象就稱為“幻讀”。
總結(jié):事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù),不符合隔離性。
“臟讀”、“不可重復(fù)讀”、“幻讀”,其實都是數(shù)據(jù)庫讀一致性問題,必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機制來解決。
數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格,并發(fā)副作用越小,但付出的代價也就越大,因為事務(wù)隔離實質(zhì)上就是使事務(wù)在一定程度上“串行化”進行,這顯然與“并發(fā)”是矛盾的。
同時,不同應(yīng)用對讀一致性和事務(wù)隔離程度的要求也是不同的,比如許多應(yīng)用對“不可重復(fù)讀”和“幻讀” 并不敏感,可能更關(guān)系數(shù)據(jù)并發(fā)訪問的能力。
查看當(dāng)前數(shù)據(jù)庫的事務(wù)隔離級別
show variables like 'tx_isolation';復(fù)制代碼
設(shè)置事務(wù)隔離級別
set tx_isolation='REPEATABLE-READ';復(fù)制代碼
數(shù)據(jù)庫版本是5.7,隔離級別是Repeatable-Read(可重復(fù)讀),不同的數(shù)據(jù)庫版本和隔離級別對語句的執(zhí)行結(jié)果影響很大。所以需要說明版本和隔離級別
BEGIN
或 START TRANSACTION
;顯式地開啟一個事務(wù);COMMIT
;也可以使用 COMMIT WORK
,不過二者是等價的。COMMIT會提交事務(wù),并使已對數(shù)據(jù)庫進行的所有修改稱為永久性的;ROLLBACK
;有可以使用 ROLLBACK WORK
,不過二者是等價的。回滾會結(jié)束用戶的事務(wù),并撤銷正在進行的所有未提交的修改;SAVEPOINT identifier
;SAVEPOINT允許在事務(wù)中創(chuàng)建一個保存點,一個事務(wù)中可以有多個SAVEPOINT;RELEASE SAVEPOINT identifier
;刪除一個事務(wù)的保存點,當(dāng)沒有指定的保存點時,執(zhí)行該語句會拋出一個異常;ROLLBACK TO identifier
;把事務(wù)回滾到標(biāo)記點;SET TRANSACTION
;用來設(shè)置事務(wù)的隔離級別。InnoDB存儲引擎提供事務(wù)的隔離級別有READ UNCOMMITTED
、READ COMMITTED
、REPEATABLE READ
和SERIALIZABLE
。MYSQL 事務(wù)處理主要有兩種方法:
BEGIN
, ROLLBACK
, COMMIT
來實現(xiàn)SET AUTOCOMMIT=0
禁止自動提交SET AUTOCOMMIT=1``
開啟自動提交示例表,如下:
CREATE TABLE `user` ( `id` INT (11) NOT NULL AUTO_INCREMENT, `name` VARCHAR (255) DEFAULT NULL, `balance` INT (11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('zhangsan','450');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('lisi', '16000');INSERT INTO `test`.`user` (`name`,`balance`) VALUES ('wangwu','2400');復(fù)制代碼
一個 session 開啟事務(wù)更新不提交,另一個 seesion 更新同一條記錄會阻塞,更新不同記錄u會阻塞。
(1)打開一個客戶端A,并設(shè)置當(dāng)前事務(wù)模式為 read uncommitted
(讀未提交),查詢表 user 的初始化值
set tx_isolation='read-uncommitted';復(fù)制代碼
(2)在客戶端A的事務(wù)提交之前,打開另一個客戶端B,更新表 user
(3)這時,雖然客戶端B的事務(wù)還沒提交,但是在客戶端A就可以查詢到B已經(jīng)更新的數(shù)據(jù)
(4)一旦客戶端B的事務(wù)因為某種原因回滾,所有的操作都將會被撤銷,那么客戶端A查詢到的數(shù)據(jù)其實就是臟數(shù)據(jù)。
(5)在客戶端A執(zhí)行更新語句 update user set balance = balance - 50 where id = 1;
zhangsan 的 balance沒有變成350,居然是400,是不是很奇怪,數(shù)據(jù)不一致啊。如果你這么想就太天真了,在應(yīng)用程序中,我們會用400-50=350,并不知道其他會話回滾了,要想解決這個問題可以采用讀已提交的隔離級別。
(1)打開一個客戶端A,并設(shè)置當(dāng)前事務(wù)模式為 read committed
(讀已提交),查詢表 user 的所有記錄
set tx_isolation='read-committed';復(fù)制代碼
(2)在客戶端A的事務(wù)提交之前,打開另一個客戶端B,更新表 user
(3)這時,客戶端B的事務(wù)還沒提交,客戶端A不能查詢到B已經(jīng)更新的數(shù)據(jù),解決了臟讀問題。
(4)客戶端B的事務(wù)提交
(5)客戶端A執(zhí)行與上一步相同的查詢,結(jié)果與上一步不一致,即產(chǎn)生了不可重復(fù)讀的問題。
(1)打開一個客戶端A,并設(shè)置當(dāng)前的事務(wù)模式為 repeatable read
,查詢表 user 的所有記錄。
set tx_isolation='repeatable-read';復(fù)制代碼
(2)在客戶端A的事務(wù)提交之前,打開另一個客戶端B,更新表 user 并提交。
(3)在客戶端A查詢表 user 的所有記錄,與步驟(1)查詢結(jié)果一直,沒有出現(xiàn)不可重復(fù)讀的問題。
(4)在客戶端A,接著執(zhí)行 update user set balance = balance - 50 where id = 1
, balance 沒有變成 400 - 50 = 350, zhangsan 的 balance 的值用的是步驟(2) 中的 350 來計算的,所以是300,數(shù)據(jù)的一致性倒是沒有被破壞??芍貜?fù)讀的隔離級別下使用了 MVCC(multi-version concurrency control)機制,select 操作不會更新版本號,是快照讀(歷史版本);insert、update、delete 會更新版本號,是當(dāng)前讀(當(dāng)前版本)。
我們下篇來講 MVCC。
(5)重新打開客戶端B,插入一條新數(shù)據(jù)后提交。
(6)在客戶端A查詢表user 的所有記錄,沒有查出新增數(shù)據(jù),所以沒有出現(xiàn)幻讀。
(7)驗證幻讀
在客戶端A執(zhí)行 update user set balance = 8888 where id = 4;
,能更新成功,再次查詢到客戶端B新增的數(shù)據(jù)。
(1)打開一個客戶端A,并設(shè)置當(dāng)前事務(wù)模式為 serializable
,查詢表 user 的初始值
set tx_isolation='serializable';復(fù)制代碼
(2)打開一個客戶端B,并設(shè)置當(dāng)前事務(wù)模式為 serializable
,插入一條記錄報錯,表被鎖了插入失敗,MySQL 中事務(wù)隔離級別為 serializable
時會鎖表,因此不會出現(xiàn)幻讀的情況,這種隔離級別并發(fā)性極低,開發(fā)中很少會用到。
InnoDB 存儲引擎由于實現(xiàn)了行級鎖定,雖然在鎖定機制的實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會更高一下,但是在整體并發(fā)處理能力方面要遠遠優(yōu)于 MyISAM 的表級鎖定的。當(dāng)系統(tǒng)并發(fā)量最高的時候,InnoDB 的整體性能和 MyISAM 相比就會有比較明顯的優(yōu)勢。
但是,InnoDB 的行級鎖定同樣也有其脆弱的一面,當(dāng)我們使用不當(dāng)?shù)臅r候,可能會讓 InnoDB 的整體性能表現(xiàn)不僅不能比 MyISAM 高,甚至可能會更差。
通過檢查 innodb_row_lock
狀態(tài)變量來分析系統(tǒng)上的行鎖的競爭情況:
show status like 'innodb_row_lock%';復(fù)制代碼
對各個狀態(tài)量的說明如下:
對于這5個狀態(tài)變量,比較重要的主要是:
尤其是當(dāng)?shù)却螖?shù)很高,而且每次等待時長也不小的時候,我們就需要分析系統(tǒng) 中為什么會有如此多的等待,然后根據(jù)分析結(jié)果著手制定優(yōu)化計劃。
set tx_isolation='repeatable-read';復(fù)制代碼
Session_1執(zhí)行:select * from user where id=1 for update; Session_2執(zhí)行:select * from user where id=2 for update; Session_1執(zhí)行:select * from user where id=2 for update; Session_2執(zhí)行:select * from user where id=1 for update;復(fù)制代碼
查看近期死鎖日志信息:
show engine innodb status\G;復(fù)制代碼
大多數(shù)情況mysql可以自動檢測死鎖并回滾產(chǎn)生死鎖的那個事務(wù),但是有些情況 mysql沒法自動檢測死鎖
repeatable-read
,有什么辦法可以解決幻讀媽?間隙鎖(Gap Lock)在某些情況下可以解決幻讀問題,它是 Innodb 在 可重復(fù)讀 提交下為解決幻讀問題時引入的鎖機制。要避免幻讀可以用間隙鎖在Session_1 下面執(zhí)行 update user set name = 'hjh' where id > 10 and id <= 20;
,則其他 Session 沒法在這個范圍鎖包含的間隙里插入或修改任何數(shù)據(jù)。
如:user 表有3條數(shù)據(jù), id > 2 and id <=3
會把第三條記錄鎖住,其他會話對則無法對第三條記錄做操作。
客戶端A執(zhí)行: update user set balance = 800 where name = 'zhangsan';
客戶端B對該表任一行執(zhí)行修改、刪除操作都會阻塞
InnoDB 的行鎖是針對索引加的鎖,不是針對記錄加的鎖。并且該索引不能失效,否則都會從行鎖升級為表鎖。
local in share mode(共享鎖)
和 for update(排它鎖)
,例如: select * from test_innodb_lock where a = 2 for update;
這樣其他 session 只能讀這行數(shù)據(jù),修改則會被阻塞,直到鎖定行的 session 提交。看完了這篇文章,相信你對MySQL鎖與事物隔離級別是什么有了一定的了解,想了解更多相關(guān)知識,歡迎關(guān)注億速云行業(yè)資訊頻道,感謝各位的閱讀!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。