MySQL事務隔離級別以及髒讀、幻讀、不可重複讀示例
事務的隔離性
MySQL是一個客戶端/伺服器架構的軟體,對於同一個伺服器來說,可以有若干個客戶端與之連線,每個客戶端與伺服器連線上之後,就可以稱之為一個會話(Session)。每個客戶端都可以在自己的會話中向伺服器發出請求語句,一個請求語句可能是某個事務的一部分,也就是對於伺服器來說可能同時處理多個事務。當資料庫上有多個事務同時執行的時候,就可能出現髒讀(Dirty Read)、不可重複讀(Non-Repeatable Read)、幻讀(Phantom Read)的問題,為了解決這些問題,就有了 “隔離級別” 的概念。
理論上在某個事務對某個資料進行訪問時,其他事務應該進行排隊,當該事務提交之後,其他事務才可以繼續訪問這個資料。但一般情況下隔離得越嚴實,效率就會越低。因此很多時候,我們都要在隔離性和效率二者之間尋找一個平衡點。
事務併發執行遇到的問題
髒讀(Dirty Read): 髒讀是指一個事務讀到了另一個未提交事務修改過的資料。
如小王的賬戶中有100的餘額,接下來有兩個事務對小王的賬戶進行訪問。
會話A | 會話B |
---|---|
begin; | |
update xxx set balance = balance+50 where client_no = ‘小王客戶號’ ; | begin; |
select balance from xxx where client_no = ‘小王客戶號’ ; (如果讀到150,則意味著發生了髒讀) |
|
rollback; | commit; |
如上,會話A和會話B各開啟了一個事務,會話A先給小王賬戶餘額加了50,此時賬戶B查詢小王賬戶餘額為150,接下來會話A進行了回滾,那會話B查詢到的150就成一個不正確的髒資料。
不可重複讀(Non-Repeatable Read): 不可重複讀是指在同一個事務內多次讀取同一資料集合,但查到的結果卻不相同。發生不可重複讀的原因是在多次搜尋期間查詢的資料被其它事務修改了。
看如下的兩個會話請求。
會話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,則意味著發生了不可重複讀) |
|
commit; |
在會話A的同一個事務中,兩次相同查詢的結果不同,意味著發生了不可重複讀。
幻讀(Phantom Read): 所謂幻讀,指的是當某個事務在讀取某個範圍內的記錄時,另外一個事務又在該範圍內插入了新的記錄,當之前的事務再次讀取該範圍的記錄時,會讀取到之前沒有讀到的資料。
假如賬戶表中目前只有小王的餘額為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 ; (如果讀到了‘小王’和‘小張’,則意味著發生了幻讀) |
|
commit; |
會話A事務中的第二次查詢,查到了第一次查詢沒有查到的 name ‘小張’,這就意味著出現了幻讀。
SQL標準制定的四種隔離級別
ISO 和 ANIS SQL 標準制定了四種事務隔離級別的標準,分別為:讀未提交(read uncommitted)、讀提交(read committed)、可重複讀(repeatable read)和序列化(serializable )。
我們先來看下這四種隔離級別的意思。
- 讀未提交: 一個事務還沒提交時,它做的變更就能被別的事務看到。
- 讀提交: 一個事務提交之後,它做的變更才會被其他事務看到。
- 可重複讀: 一個事務執行過程中看到的資料,總是跟這個事務在啟動時看到的資料是一致的。當然在可重複讀隔離級別下,未提交的變更對其他事務也是不可見的。
- 序列化: 顧名思義是對於同一行記錄,“寫”會加“寫鎖”,“讀”會加“讀鎖”。當出現讀寫鎖衝突的時候,後訪問的事務必須等前一個事務執行完成,才能繼續執行。
SQL 標準中規定,針對不同的隔離級別,併發事務可以發生不同嚴重程度的問題,具體情況如下:
( √ 表示可以發生;× 表示不可以發生)
隔離級別 | 髒讀 | 不可重複讀 | 幻讀 |
---|---|---|---|
讀未提交(read uncommitted) | √ | √ | √ |
讀提交(read committed) | × | √ | √ |
可重複讀(repeatable read) | × | × | √ |
序列化(serializable ) | × | × | × |
MySQL對四種隔離級別的支援情況
雖然 ISO 和 ANIS SQL 標準制定了四種事務隔離級別的標準,但不是所有資料庫廠商都遵循這些標準,比如 Oracle 資料庫就不支援讀未提交(read uncommitted)和可重複讀(repeatable read)的事務隔離級別。
MySQL InnoDB 儲存引擎支援4種隔離級別,但與 SQL 標準中定義的不同的是,InnoDB 儲存引擎在預設的可重複讀(repeatable read)事務隔離級別下,使用 Next-Key Lock 鎖的演算法,避免了幻讀的產生
。也就是說 InnoDB 儲存引擎在可重複讀(repeatable read)的事務隔離級別下,已經可以完全保證事務的隔離性要求,即達到了 SQL 標準中的序列化(serializable )隔離級別的要求。
如何設定事務的隔離級別
在 InnoDB 儲存引擎中,可以使用以下命令來設定全域性或者當前會話的事務隔離級別:
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
如想設定當前會話的隔離級別為讀提交,可以使用如下語句:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
如果想在 MySQL 資料庫啟動時就設定事務的預設隔離級別,那就需要修改配置檔案中 transaction-isolation 的值,比方說,我們在啟動前指定了 transaction-isolation = READ COMMITTED
,那麼事務的預設隔離級別就從原來的 REPEATABLE READ 變成了READ COMMITTED。
檢視當前會話的事務隔離級別,可以用如下語句:
SELECT @@transaction_isolation;
檢視全域性的事務隔離級別,可以使用如下語句:
SELECT @@global.transaction_isolation;
注意:transaction_isolation 是在 MySQL 5.7.20 的版本中引入來替換tx_isolation的,如果你使用的是之前版本的 MySQL,請將上述用到的 transaction_isolation 的地方替換為 tx_isolation 。
- 編譯安裝Mysql8.0.22
- 由Hadoop驅動的原始大資料時代已於2019年6月結束…….858
- Apache Jmeter 教程
- 中國科學院正式回覆饒毅:不再進行調查
- Android熱修復及外掛化原理
- 【資料庫MySQL】練習---備份及恢復
- 一組強大的CSS3 Material 按鈕
- No.8 bin和sbin的區別
- nginx配置ssl證書訪問不了https網站
- 大根堆與小根堆的理解,如何手寫一個堆,以及什麼時候用自己手寫的堆,什麼時候用語言提供堆的api,(二者的區別)
- FreeRTOS的License許可說明~
- 穩定流暢、高清晰, 華為HMS Core帶來一站式視訊服務
- “模板方法 職責鏈設計模式”解決業務場景重複以及場景之間依賴
- vi視窗切分命令(split命令)
- Linux學習筆記
- MySQL事務隔離級別以及髒讀、幻讀、不可重複讀示例
- 基於live555的rtsp播放器:資料接收(拉流)
- APACHE 2.2.15 TOMCAT6.0.26配置負載均衡
- 資料恢復基礎和進階教程(一)
- 2011年11月51CTO桌布點評活動獲獎名單【已結束】