您好,登錄后才能下訂單哦!
小編給大家分享一下MySQL事務(wù)隔離級別以及臟讀、幻讀、不可重復(fù)讀的示例,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
推薦(免費):mysql視頻教程
事務(wù)的隔離性
MySQL是一個客戶端/服務(wù)器架構(gòu)的軟件,對于同一個服務(wù)器來說,可以有若干個客戶端與之連接,每個客戶端與服務(wù)器連接上之后,就可以稱之為一個會話(Session)。每個客戶端都可以在自己的會話中向服務(wù)器發(fā)出請求語句,一個請求語句可能是某個事務(wù)的一部分,也就是對于服務(wù)器來說可能同時處理多個事務(wù)。當數(shù)據(jù)庫上有多個事務(wù)同時執(zhí)行的時候,就可能出現(xiàn)臟讀(Dirty Read)、不可重復(fù)讀(Non-Repeatable Read)、幻讀(Phantom Read)的問題,為了解決這些問題,就有了 “隔離級別” 的概念。
理論上在某個事務(wù)對某個數(shù)據(jù)進行訪問時,其他事務(wù)應(yīng)該進行排隊,當該事務(wù)提交之后,其他事務(wù)才可以繼續(xù)訪問這個數(shù)據(jù)。但一般情況下隔離得越嚴實,效率就會越低。因此很多時候,我們都要在隔離性和效率二者之間尋找一個平衡點。
事務(wù)并發(fā)執(zhí)行遇到的問題
臟讀(Dirty Read): 臟讀是指一個事務(wù)讀到了另一個未提交事務(wù)修改過的數(shù)據(jù)。
如小王的賬戶中有100的余額,接下來有兩個事務(wù)對小王的賬戶進行訪問。
會話A | 會話B |
---|---|
begin; | |
update xxx set balance = balance+50 where client_no = ‘小王客戶號’ ; | begin; |
select balance from xxx where client_no = ‘小王客戶號’ ; (如果讀到150,則意味著發(fā)生了臟讀) | |
rollback; | commit; |
如上,會話A和會話B各開啟了一個事務(wù),會話A先給小王賬戶余額加了50,此時賬戶B查詢小王賬戶余額為150,接下來會話A進行了回滾,那會話B查詢到的150就成一個不正確的臟數(shù)據(jù)。
不可重復(fù)讀(Non-Repeatable Read): 不可重復(fù)讀是指在同一個事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合,但查到的結(jié)果卻不相同。發(fā)生不可重復(fù)讀的原因是在多次搜索期間查詢的數(shù)據(jù)被其它事務(wù)修改了。
看如下的兩個會話請求。
會話A | 會話B |
---|---|
begin; | |
select balance from xxx where client_no = ‘小王客戶號’ ; (讀到余額為100) | begin; |
update xxx set balance = balance+50 where client_no = ‘小王客戶號’ ; | |
commit; | |
select balance from xxx where client_no = ‘小王客戶號’ ; (如果讀到150,則意味著發(fā)生了不可重復(fù)讀) | |
commit; |
在會話A的同一個事務(wù)中,兩次相同查詢的結(jié)果不同,意味著發(fā)生了不可重復(fù)讀。
幻讀(Phantom Read): 所謂幻讀,指的是當某個事務(wù)在讀取某個范圍內(nèi)的記錄時,另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄,當之前的事務(wù)再次讀取該范圍的記錄時,會讀取到之前沒有讀到的數(shù)據(jù)。
假如賬戶表中目前只有小王的余額為100,再看下如下的兩個會話請求。
會話A | 會話B |
---|---|
begin; | |
select name from xxx where balance = 100 ; (讀到name為‘小王’) | begin; |
insert into xxx(client_no,name,balance) values(‘小張客戶號’,‘小張’,100); | |
commit; | |
select name from xxx where balance = 100 ; (如果讀到了‘小王’和‘小張’,則意味著發(fā)生了幻讀) | |
commit; |
會話A事務(wù)中的第二次查詢,查到了第一次查詢沒有查到的 name ‘小張’,這就意味著出現(xiàn)了幻讀。
SQL標準制定的四種隔離級別
ISO 和 ANIS SQL 標準制定了四種事務(wù)隔離級別的標準,分別為:讀未提交(read uncommitted)、讀提交(read committed)、可重復(fù)讀(repeatable read)和串行化(serializable )。
我們先來看下這四種隔離級別的意思。
讀未提交: 一個事務(wù)還沒提交時,它做的變更就能被別的事務(wù)看到。
讀提交: 一個事務(wù)提交之后,它做的變更才會被其他事務(wù)看到。
可重復(fù)讀: 一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù),總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的。當然在可重復(fù)讀隔離級別下,未提交的變更對其他事務(wù)也是不可見的。
串行化: 顧名思義是對于同一行記錄,“寫”會加“寫鎖”,“讀”會加“讀鎖”。當出現(xiàn)讀寫鎖沖突的時候,后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成,才能繼續(xù)執(zhí)行。
SQL 標準中規(guī)定,針對不同的隔離級別,并發(fā)事務(wù)可以發(fā)生不同嚴重程度的問題,具體情況如下:( √ 表示可以發(fā)生;× 表示不可以發(fā)生)
隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
---|---|---|---|
讀未提交(read uncommitted) | √ | √ | √ |
讀提交(read committed) | × | √ | √ |
可重復(fù)讀(repeatable read) | × | × | √ |
串行化(serializable ) | × | × | × |
MySQL對四種隔離級別的支持情況
雖然 ISO 和 ANIS SQL 標準制定了四種事務(wù)隔離級別的標準,但不是所有數(shù)據(jù)庫廠商都遵循這些標準,比如 Oracle 數(shù)據(jù)庫就不支持讀未提交(read uncommitted)和可重復(fù)讀(repeatable read)的事務(wù)隔離級別。
MySQL InnoDB 存儲引擎支持4種隔離級別,但與 SQL 標準中定義的不同的是,InnoDB 存儲引擎在默認的可重復(fù)讀(repeatable read)事務(wù)隔離級別下,使用 Next-Key Lock 鎖的算法,避免了幻讀的產(chǎn)生
。也就是說 InnoDB 存儲引擎在可重復(fù)讀(repeatable read)的事務(wù)隔離級別下,已經(jīng)可以完全保證事務(wù)的隔離性要求,即達到了 SQL 標準中的串行化(serializable )隔離級別的要求。
如何設(shè)置事務(wù)的隔離級別
在 InnoDB 存儲引擎中,可以使用以下命令來設(shè)置全局或者當前會話的事務(wù)隔離級別:
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
如想設(shè)置當前會話的隔離級別為讀提交,可以使用如下語句:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
如果想在 MySQL 數(shù)據(jù)庫啟動時就設(shè)置事務(wù)的默認隔離級別,那就需要修改配置文件中 transaction-isolation 的值,比方說,我們在啟動前指定了 transaction-isolation = READ COMMITTED
,那么事務(wù)的默認隔離級別就從原來的 REPEATABLE READ 變成了READ COMMITTED。
查看當前會話的事務(wù)隔離級別,可以用如下語句:
SELECT @@transaction_isolation;
查看全局的事務(wù)隔離級別,可以使用如下語句:
SELECT @@global.transaction_isolation;
注意:transaction_isolation 是在 MySQL 5.7.20 的版本中引入來替換tx_isolation的,如果你使用的是之前版本的 MySQL,請將上述用到的 transaction_isolation 的地方替換為 tx_isolation 。
事務(wù)的隔離性
MySQL是一個客戶端/服務(wù)器架構(gòu)的軟件,對于同一個服務(wù)器來說,可以有若干個客戶端與之連接,每個客戶端與服務(wù)器連接上之后,就可以稱之為一個會話(Session)。每個客戶端都可以在自己的會話中向服務(wù)器發(fā)出請求語句,一個請求語句可能是某個事務(wù)的一部分,也就是對于服務(wù)器來說可能同時處理多個事務(wù)。當數(shù)據(jù)庫上有多個事務(wù)同時執(zhí)行的時候,就可能出現(xiàn)臟讀(Dirty Read)、不可重復(fù)讀(Non-Repeatable Read)、幻讀(Phantom Read)的問題,為了解決這些問題,就有了 “隔離級別” 的概念。
理論上在某個事務(wù)對某個數(shù)據(jù)進行訪問時,其他事務(wù)應(yīng)該進行排隊,當該事務(wù)提交之后,其他事務(wù)才可以繼續(xù)訪問這個數(shù)據(jù)。但一般情況下隔離得越嚴實,效率就會越低。因此很多時候,我們都要在隔離性和效率二者之間尋找一個平衡點。
事務(wù)并發(fā)執(zhí)行遇到的問題
臟讀(Dirty Read): 臟讀是指一個事務(wù)讀到了另一個未提交事務(wù)修改過的數(shù)據(jù)。
如小王的賬戶中有100的余額,接下來有兩個事務(wù)對小王的賬戶進行訪問。
會話A | 會話B |
---|---|
begin; | |
update xxx set balance = balance+50 where client_no = ‘小王客戶號’ ; | begin; |
select balance from xxx where client_no = ‘小王客戶號’ ; (如果讀到150,則意味著發(fā)生了臟讀) | |
rollback; | commit; |
如上,會話A和會話B各開啟了一個事務(wù),會話A先給小王賬戶余額加了50,此時賬戶B查詢小王賬戶余額為150,接下來會話A進行了回滾,那會話B查詢到的150就成一個不正確的臟數(shù)據(jù)。
不可重復(fù)讀(Non-Repeatable Read): 不可重復(fù)讀是指在同一個事務(wù)內(nèi)多次讀取同一數(shù)據(jù)集合,但查到的結(jié)果卻不相同。發(fā)生不可重復(fù)讀的原因是在多次搜索期間查詢的數(shù)據(jù)被其它事務(wù)修改了。
看如下的兩個會話請求。
會話A | 會話B |
---|---|
begin; | |
select balance from xxx where client_no = ‘小王客戶號’ ; (讀到余額為100) | begin; |
update xxx set balance = balance+50 where client_no = ‘小王客戶號’ ; | |
commit; | |
select balance from xxx where client_no = ‘小王客戶號’ ; (如果讀到150,則意味著發(fā)生了不可重復(fù)讀) | |
commit; |
在會話A的同一個事務(wù)中,兩次相同查詢的結(jié)果不同,意味著發(fā)生了不可重復(fù)讀。
幻讀(Phantom Read): 所謂幻讀,指的是當某個事務(wù)在讀取某個范圍內(nèi)的記錄時,另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄,當之前的事務(wù)再次讀取該范圍的記錄時,會讀取到之前沒有讀到的數(shù)據(jù)。
假如賬戶表中目前只有小王的余額為100,再看下如下的兩個會話請求。
會話A | 會話B |
---|---|
begin; | |
select name from xxx where balance = 100 ; (讀到name為‘小王’) | begin; |
insert into xxx(client_no,name,balance) values(‘小張客戶號’,‘小張’,100); | |
commit; | |
select name from xxx where balance = 100 ; (如果讀到了‘小王’和‘小張’,則意味著發(fā)生了幻讀) | |
commit; |
會話A事務(wù)中的第二次查詢,查到了第一次查詢沒有查到的 name ‘小張’,這就意味著出現(xiàn)了幻讀。
SQL標準制定的四種隔離級別
ISO 和 ANIS SQL 標準制定了四種事務(wù)隔離級別的標準,分別為:讀未提交(read uncommitted)、讀提交(read committed)、可重復(fù)讀(repeatable read)和串行化(serializable )。
我們先來看下這四種隔離級別的意思。
讀未提交: 一個事務(wù)還沒提交時,它做的變更就能被別的事務(wù)看到。
讀提交: 一個事務(wù)提交之后,它做的變更才會被其他事務(wù)看到。
可重復(fù)讀: 一個事務(wù)執(zhí)行過程中看到的數(shù)據(jù),總是跟這個事務(wù)在啟動時看到的數(shù)據(jù)是一致的。當然在可重復(fù)讀隔離級別下,未提交的變更對其他事務(wù)也是不可見的。
串行化: 顧名思義是對于同一行記錄,“寫”會加“寫鎖”,“讀”會加“讀鎖”。當出現(xiàn)讀寫鎖沖突的時候,后訪問的事務(wù)必須等前一個事務(wù)執(zhí)行完成,才能繼續(xù)執(zhí)行。
SQL 標準中規(guī)定,針對不同的隔離級別,并發(fā)事務(wù)可以發(fā)生不同嚴重程度的問題,具體情況如下:( √ 表示可以發(fā)生;× 表示不可以發(fā)生)
隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
---|---|---|---|
讀未提交(read uncommitted) | √ | √ | √ |
讀提交(read committed) | × | √ | √ |
可重復(fù)讀(repeatable read) | × | × | √ |
串行化(serializable ) | × | × | × |
MySQL對四種隔離級別的支持情況
雖然 ISO 和 ANIS SQL 標準制定了四種事務(wù)隔離級別的標準,但不是所有數(shù)據(jù)庫廠商都遵循這些標準,比如 Oracle 數(shù)據(jù)庫就不支持讀未提交(read uncommitted)和可重復(fù)讀(repeatable read)的事務(wù)隔離級別。
MySQL InnoDB 存儲引擎支持4種隔離級別,但與 SQL 標準中定義的不同的是,InnoDB 存儲引擎在默認的可重復(fù)讀(repeatable read)事務(wù)隔離級別下,使用 Next-Key Lock 鎖的算法,避免了幻讀的產(chǎn)生
。也就是說 InnoDB 存儲引擎在可重復(fù)讀(repeatable read)的事務(wù)隔離級別下,已經(jīng)可以完全保證事務(wù)的隔離性要求,即達到了 SQL 標準中的串行化(serializable )隔離級別的要求。
如何設(shè)置事務(wù)的隔離級別
在 InnoDB 存儲引擎中,可以使用以下命令來設(shè)置全局或者當前會話的事務(wù)隔離級別:
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
如想設(shè)置當前會話的隔離級別為讀提交,可以使用如下語句:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
如果想在 MySQL 數(shù)據(jù)庫啟動時就設(shè)置事務(wù)的默認隔離級別,那就需要修改配置文件中 transaction-isolation 的值,比方說,我們在啟動前指定了 transaction-isolation = READ COMMITTED
,那么事務(wù)的默認隔離級別就從原來的 REPEATABLE READ 變成了READ COMMITTED。
查看當前會話的事務(wù)隔離級別,可以用如下語句:
SELECT @@transaction_isolation;
查看全局的事務(wù)隔離級別,可以使用如下語句:
SELECT @@global.transaction_isolation;
注意:transaction_isolation 是在 MySQL 5.7.20 的版本中引入來替換tx_isolation的,如果你使用的是之前版本的 MySQL,請將上述用到的 transaction_isolation 的地方替換為 tx_isolation 。
以上是“MySQL事務(wù)隔離級別以及臟讀、幻讀、不可重復(fù)讀的示例”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學(xué)習(xí)更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責聲明:本站發(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)容。