mysql事务实战_mysql事务隔离级别详解和实战
A事務做了操作?沒有提交?對B事務來說?就等于沒做?獲取的都是之前的數據
但是?在A事務中查詢的話?查到的都是操作之后的數據
沒有提交的數據只有自己看得到,并沒有update到數據庫。
查看InnoDB存儲引擎 系統級的隔離級別 和 會話級的隔離級別:
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
設置innodb的事務級別方法是:set 作用域 transaction isolation level 事務隔離級別,例如~
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
mysql> set global transaction isolation level read committed; //全局的
mysql> set session transaction isolation level read committed; //當前會話
SQL標準定義了4類隔離級別,包括了一些具體規則,用來限定事務內外的哪些改變是可見的,哪些是不可見的。低級別的隔離級一般支持更高的并發處理,并擁有更低的系統開銷。
Read Uncommitted(讀取未提交內容)
在該隔離級別,所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用,因為它的性能也不比其他級別好多少。讀取未提交的數據,也被稱之為臟讀(Dirty Read)。
Read Committed(讀取提交內容)
這是大多數數據庫系統的默認隔離級別(但不是MySQL默認的)。它滿足了隔離的簡單定義:一個事務只能看見已經提交事務所做的改變。這種隔離級別 也支持所謂的不可重復讀(Nonrepeatable Read),因為同一事務的其他實例在該實例處理其間可能會有新的commit,所以同一select可能返回不同結果。
Repeatable Read(可重讀)
這是MySQL的默認事務隔離級別,它確保同一事務的多個實例在并發讀取數據時,會看到同樣的數據行。不過理論上,這會導致另一個棘手的問題:幻讀 (Phantom Read)。簡單的說,幻讀指當用戶讀取某一范圍的數據行時,另一個事務又在該范圍內插入了新行,當用戶再讀取該范圍的數據行時,會發現有新的“幻影” 行。InnoDB和Falcon存儲引擎通過多版本并發控制(MVCC,Multiversion Concurrency Control)機制解決了該問題。
Serializable(可串行化)
這是最高的隔離級別,它通過強制事務排序,使之不可能相互沖突,從而解決幻讀問題。簡言之,它是在每個讀的數據行上加上共享鎖。在這個級別,可能導致大量的超時現象和鎖競爭。
這四種隔離級別采取不同的鎖類型來實現,若讀取的是同一個數據的話,就容易發生問題。例如:
臟讀(Drity Read):某個事務已更新一份數據,另一個事務在此時讀取了同一份數據,由于某些原因,前一個RollBack了操作,則后一個事務所讀取的數據就會是不正確的。
不可重復讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致,這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致,例如有一個事務查詢了幾列(Row)數據,而另一個事務卻在此時插入了新的幾列數據,先前的事務在接下來的查詢中,就會發現有幾列數據是它先前所沒有的。
在MySQL中,實現了這四種隔離級別,分別有可能產生問題如下所示:
InnoDB引擎的鎖機制
(之所以以InnoDB為主介紹鎖,是因為InnoDB支持事務,支持行鎖和表鎖用的比較多,Myisam不支持事務,只支持表鎖)
共享鎖(S):允許一個事務去讀一行,阻止其他事務獲得相同數據集的排他鎖。
排他鎖(X):允許獲得排他鎖的事務更新數據,阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。
意向共享鎖(IS):事務打算給數據行加行共享鎖,事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖(IX):事務打算給數據行加行排他鎖,事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。
說明:
1)共享鎖和排他鎖都是行鎖,意向鎖都是表鎖,應用中我們只會使用到共享鎖和排他鎖,意向鎖是mysql內部使用的,不需要用戶干預。
2)對于UPDATE、DELETE和INSERT語句,InnoDB會自動給涉及數據集加排他鎖(X);對于普通SELECT語句,InnoDB不會加任何鎖,事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。
共享鎖(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他鎖(X):SELECT * FROM table_name WHERE ... FOR UPDATE。
3)InnoDB行鎖是通過給索引上的索引項加鎖來實現的,因此InnoDB這種行鎖實現特點意味著:只有通過索引條件檢索數據,InnoDB才使用行級鎖,否則,InnoDB將使用表鎖!。
在?MySQL中的行級鎖,表級鎖,頁級鎖中介紹過,行級鎖是Mysql中鎖定粒度最細的一種鎖,行級鎖能大大減少數據庫操作的沖突。行級鎖分為共享鎖和排他鎖兩種,本文將詳細介紹共享鎖及排他鎖的概念、使用方式及注意事項等。
共享鎖(Share Lock)
共享鎖又稱讀鎖,是讀取操作創建的鎖。其他用戶可以并發讀取數據,但任何事務都不能對數據進行修改(獲取數據上的排他鎖),直到已釋放所有共享鎖。
如果事務T對數據A加上共享鎖后,則其他事務只能對A再加共享鎖,不能加排他鎖。獲得共享鎖的事務只能讀數據,不能修改數據。
用法
SELECT ... LOCK? IN SHARE MODE;
在查詢語句后面增加LOCK IN SHARE MODE,Mysql會對查詢結果中的每行都加共享鎖,當沒有其他線程對查詢結果集中的任何一行使用排他鎖時,可以成功申請共享鎖,否則會被阻塞。其他線程也可以讀取使用了共享鎖的表,而且這些線程讀取的是同一個版本的數據。
排他鎖(eXclusive Lock)
共享鎖又稱寫鎖,如果事務T對數據A加上排他鎖后,則其他事務不能再對A加任何類型的鎖,其它事務也不能對A做update,insert,delete操作,因為在innodb中這些操作默認加了排他鎖,可以進行select 操作因為查詢的時候是不加任何鎖的。
用法
SELECT ... FOR UPDATE;
在查詢語句后面增加FOR UPDATE,Mysql會對查詢結果中的每行都加排他鎖,當沒有其他線程對查詢結果集中的任何一行使用排他鎖時,可以成功申請排他鎖,否則會被阻塞。
意向鎖
InnoDB還有兩個表鎖:
意向共享鎖(IS):表示事務準備給數據行加入共享鎖,也就是說一個數據行加共享鎖前必須先取得該表的IS鎖
意向排他鎖(IX):類似上面,表示事務準備給數據行加入排他鎖,說明事務在一個數據行加排他鎖前必須先取得該表的IX鎖。
意向鎖是InnoDB自動加的,不需要用戶干預。
對于insert、update、delete,InnoDB會自動給涉及的數據加排他鎖(X);對于一般的Select語句,InnoDB不會加任何鎖,事務可以通過以下語句給顯示加共享鎖或排他鎖。
共享鎖:?SELECT ... LOCK IN SHARE MODE;
排他鎖:?SELECT ... FOR UPDATE;
注意事項
行級鎖與表級鎖
行級鎖都是基于索引的,如果一條SQL語句用不到索引是不會使用行級鎖的,會使用表級鎖。行級鎖的缺點是:由于需要請求大量的鎖資源,所以速度慢,內存消耗大。
行級鎖與死鎖
MyISAM中是不會產生死鎖的,因為MyISAM總是一次性獲得所需的全部鎖,要么全部滿足,要么全部等待。而在InnoDB中,鎖是逐步獲得的,就造成了死鎖的可能。
在MySQL中,行級鎖并不是直接鎖記錄,而是鎖索引。索引分為主鍵索引和非主鍵索引兩種,如果一條sql語句操作了主鍵索引,MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引,MySQL會先鎖定該非主鍵索引,再鎖定相關的主鍵索引。?在UPDATE、DELETE操作時,MySQL不僅鎖定WHERE條件掃描過的所有索引記錄,而且會鎖定相鄰的鍵值,即所謂的next-key locking。
當兩個事務同時執行,一個鎖住了主鍵索引在等待其他相關索引,一個鎖定了非主鍵索引,在等待主鍵索引。這樣就會發生死鎖。
發生死鎖后,InnoDB一般都可以檢測到,并使一個事務釋放鎖回退,另一個獲取鎖完成事務。
有多種方法可以避免死鎖,這里只介紹常見的三種
1、如果不同程序會并發存取多個表,盡量約定以相同的順序訪問表,可以大大降低死鎖機會。
2、在同一個事務中,盡可能做到一次鎖定所需要的所有資源,減少死鎖產生概率;
3、對于非常容易產生死鎖的業務部分,可以嘗試使用升級鎖定顆粒度,通過表級鎖定來減少死鎖產生的概率;
實戰:
數據庫隔離級別有四種,應用《高性能mysql》一書中的說明:
然后說說修改事務隔離級別的方法:
1.全局修改,修改mysql.ini配置文件,在最后加上
復制代碼代碼如下:
#可選參數有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ
這里全局默認是REPEATABLE-READ,其實MySQL本來默認也是這個級別
2.對當前session修改,在登錄mysql客戶端后,執行命令:
要記住mysql有一個autocommit參數,默認是on,他的作用是每一條單獨的查詢都是一個事務,并且自動開始,自動提交(執行完以后就自動結束了,如果你要適用select for update,而不手動調用 start transaction,這個for update的行鎖機制等于沒用,因為行鎖在自動提交后就釋放了),所以事務隔離級別和鎖機制即使你不顯式調用start transaction,這種機制在單獨的一條查詢語句中也是適用的,分析鎖的運作的時候一定要注意這一點
再來說說鎖機制:
共享鎖:由讀表操作加上的鎖,加鎖后其他用戶只能獲取該表或行的共享鎖,不能獲取排它鎖,也就是說只能讀不能寫
排它鎖:由寫表操作加上的鎖,加鎖后其他用戶不能獲取該表或行的任何鎖,典型是mysql事務中
復制代碼代碼如下:
start transaction;
select * from user where userId = 1 for update;
執行完這句以后
1)當其他事務想要獲取共享鎖,比如事務隔離級別為SERIALIZABLE的事務,執行
復制代碼代碼如下:
select * from user;
將會被掛起,因為SERIALIZABLE的select語句需要獲取共享鎖
2)當其他事務執行
復制代碼代碼如下:
select * from user where userId = 1 for update;
update user set userAge = 100 where userId = 1;
也會被掛起,因為for update會獲取這一行數據的排它鎖,需要等到前一個事務釋放該排它鎖才可以繼續進行
鎖的范圍:
行鎖: 對某行記錄加上鎖
表鎖: 對整個表加上鎖
這樣組合起來就有,行級共享鎖,表級共享鎖,行級排他鎖,表級排他鎖
下面來說說不同的事務隔離級別的實例效果,例子使用InnoDB,開啟兩個客戶端A,B,在A中修改事務隔離級別,在B中開啟事務并修改數據,然后在A中的事務查看B的事務修改效果:
1.READ-UNCOMMITTED(讀取未提交內容)級別
1)A修改事務級別并開始事務,對user表做一次查詢
2)B更新一條記錄
3)此時B事務還未提交,A在事務內做一次查詢,發現查詢結果已經改變
4)B進行事務回滾
5)A再做一次查詢,查詢結果又變回去了
6)A表對user表數據進行修改
7)B表重新開始事務后,對user表記錄進行修改,修改被掛起,直至超時,但是對另一條數據的修改成功,說明A的修改對user表的數據行加行共享鎖(因為可以使用select)
可以看出READ-UNCOMMITTED隔離級別,當兩個事務同時進行時,即使事務沒有提交,所做的修改也會對事務內的查詢做出影響,這種級別顯然很不安全。但是在表對某行進行修改時,會對該行加上行共享鎖
2. READ-COMMITTED(讀取提交內容)
1)設置A的事務隔離級別,并進入事務做一次查詢
2)B開始事務,并對記錄進行修改
3)A再對user表進行查詢,發現記錄沒有受到影響
4)B提交事務
5)A再對user表查詢,發現記錄被修改
6)A對user表進行修改
7)B重新開始事務,并對user表同一條進行修改,發現修改被掛起,直到超時,但對另一條記錄修改,卻是成功,說明A的修改對user表加上了行共享鎖(因為可以select)
READ-COMMITTED事務隔離級別,只有在事務提交后,才會對另一個事務產生影響,并且在對表進行修改時,會對表數據行加上行共享鎖
3. REPEATABLE-READ(可重讀)
1)A設置事務隔離級別,進入事務后查詢一次
2)B開始事務,并對user表進行修改
3)A查看user表數據,數據未發生改變
4)B提交事務
5)A再進行一次查詢,結果還是沒有變化
6)A提交事務后,再查看結果,結果已經更新
7)A重新開始事務,并對user表進行修改
8)B表重新開始事務,并對user表進行修改,修改被掛起,直到超時,對另一條記錄修改卻成功,說明A對表進行修改時加了行共享鎖(可以select)
REPEATABLE-READ事務隔離級別,當兩個事務同時進行時,其中一個事務修改數據對另一個事務不會造成影響,即使修改的事務已經提交也不會對另一個事務造成影響。
在事務中對某條記錄修改,會對記錄加上行共享鎖,直到事務結束才會釋放。
4.SERIERLIZED(可串行化)
1)修改A的事務隔離級別,并作一次查詢
2)B對表進行查詢,正常得出結果,可知對user表的查詢是可以進行的
3)B開始事務,并對記錄做修改,因為A事務未提交,所以B的修改處于等待狀態,等待A事務結束,最后超時,說明A在對user表做查詢操作后,對表加上了共享鎖
SERIALIZABLE事務隔離級別最嚴厲,在進行查詢時就會對表或行加上共享鎖,其他事務對該表將只能進行讀操作,而不能進行寫操作。
參考:
http://xm-king.iteye.com/blog/770721
http://blog.csdn.net/taylor_tao/article/details/7063639
http://blog.csdn.net/lemon89/article/details/51477497
總結
以上是生活随笔為你收集整理的mysql事务实战_mysql事务隔离级别详解和实战的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: mysql使用方法_Mysql的常用用法
- 下一篇: mysql中两次排序_MySQL中的两种