mysql事物隔離級別和傳播行為

本文作者： 開發(fā)者首頁
本文鏈接： https://blog.kfzsy.com/mysql-transaction.html
版權(quán)聲明： 本博客所有文章除特別聲明外匆瓜，均采用 BY-NC-SA 許可協(xié)議脸秽。轉(zhuǎn)載請注明出處置媳！

事務(wù)隔離級別
事務(wù)隔離級別的語義：當(dāng)前事務(wù)執(zhí)行過程中入热，通過select八秃，update懦底，delete 操作，對其他事務(wù)的影響风范，反過來也是如此，通俗的說就是 當(dāng)前事務(wù)是否可以看到其他事務(wù)的操作結(jié)果沪么。
數(shù)據(jù)庫事務(wù)的隔離級別有4個硼婿，由低到高依次為Read uncommitted、Read committed禽车、Repeatable read寇漫、Serializable，這四個級別可以逐個解決臟讀殉摔、不可重復(fù)讀州胳、幻讀這幾類問題。
mysql 默認(rèn)的隔離級別是：Repeatable read

如何查詢當(dāng)前數(shù)據(jù)庫的隔離級別
select @@tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@global.tx_isolation;
設(shè)置
set tx_isolation=’read-committed’;

不同隔離級別的影響
ANSI/ISO SQL標(biāo)準(zhǔn)定義了4中事務(wù)隔離級別：未提交讀（read uncommitted）钦勘，提交讀（read committed），重復(fù)讀（repeatable read）亚亲，串行讀（serializable）彻采。
對于不同的事務(wù)，采用不同的隔離級別分別有不同的結(jié)果捌归。不同的隔離級別有不同的現(xiàn)象肛响。主要有下面3種現(xiàn)在：
1、臟讀（dirty read）：一個事務(wù)可以讀取另一個尚未提交事務(wù)的修改數(shù)據(jù)惜索。
2特笋、不可重復(fù)讀（nonrepeatable read）：在同一個事務(wù)中，同一個查詢在T1時間讀取某一行，在T2時間重新讀取這一行時候猎物，這一行的數(shù)據(jù)已經(jīng)發(fā)生修改虎囚，可能被更新了（update），也可能被刪除了（delete）蔫磨。
3淘讥、幻像讀（phantom read）：在同一事務(wù)中，同一查詢多次進(jìn)行時候堤如，由于其他插入操作（insert）的事務(wù)提交蒲列，導(dǎo)致每次返回不同的結(jié)果集。
不同的隔離級別有不同的現(xiàn)象搀罢，并有不同的鎖定/并發(fā)機(jī)制蝗岖，隔離級別越高，數(shù)據(jù)庫的并發(fā)性就越差榔至，4種事務(wù)隔離級別分別表現(xiàn)的現(xiàn)象如下圖：

注意：
1抵赢、repeatable read 允許幻讀，這是ANSI/ISO SQL標(biāo)準(zhǔn)的定義要求洛退，運(yùn)行幻讀依然有非常大的隱患瓣俯，mysql innodb引擎 在repeatable read 即可滿足沒有幻讀的要求。
2兵怯、不可重復(fù)讀和幻讀的區(qū)別：不可重復(fù)讀的重點(diǎn)是修改彩匕，幻讀的重點(diǎn)是插入或者刪除了新數(shù)據(jù)。兩都會造成系統(tǒng)錯誤媒区，但是避免的方法則區(qū)別比較大驼仪，對于前者, 只需要鎖住滿足條件的記錄，對于后者, 要鎖住滿足條件及其相近的記錄袜漩。

Read uncommitted
這是事務(wù)最低的隔離級別绪爸，它充許令外一個事務(wù)可以看到這個事務(wù)未提交的數(shù)據(jù)。這種隔離級別會產(chǎn)生臟讀宙攻，不可重復(fù)讀和幻像讀奠货。
(1)所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果
(2)本隔離級別很少用于實際應(yīng)用，因為它的性能也不比其他級別好多少
(3)該級別引發(fā)的問題是——臟讀(Dirty Read)：讀取到了未提交的數(shù)據(jù)

首先座掘，修改隔離級別

set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+

事務(wù)A：啟動一個事務(wù)

start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)B：也啟動一個事務(wù)(那么兩個事務(wù)交叉了)

   在事務(wù)B中執(zhí)行更新語句递惋，且不提交

start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)A：那么這時候事務(wù)A能看到這個更新了的數(shù)據(jù)嗎?

select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 | --->可以看到！說明我們讀到了事務(wù)B還沒有提交的數(shù)據(jù)
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)B：事務(wù)B回滾,仍然未提交

rollback;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)A：在事務(wù)A里面看到的也是B沒有提交的數(shù)據(jù)

select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 | --->臟讀意味著我在這個事務(wù)中(A中)溢陪，事務(wù)B雖然沒有提交萍虽，但它任何一條數(shù)據(jù)變化，我都可以看到形真！
| 2 | 2 |
| 3 | 3 |
+------+------+
Read committed
保證一個事務(wù)修改的數(shù)據(jù)提交后才能被另外一個事務(wù)讀取杉编。另外一個事務(wù)不能讀取該事務(wù)未提交的數(shù)據(jù)
(1)這是大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別（但不是MySQL默認(rèn)的）
(2)它滿足了隔離的簡單定義：一個事務(wù)只能看見已經(jīng)提交事務(wù)所做的改變
(3)這種隔離級別出現(xiàn)的問題是——不可重復(fù)讀(Nonrepeatable Read)：不可重復(fù)讀意味著我們在同一個事務(wù)中執(zhí)行完全相同的select語句時可能看到不一樣的結(jié)果。
|——>導(dǎo)致這種情況的原因可能有：(1)有一個交叉的事務(wù)有新的commit，導(dǎo)致了數(shù)據(jù)的改變;(2)一個數(shù)據(jù)庫被多個實例操作時,同一事務(wù)的其他實例在該實例處理其間可能會有新的commit

首先修改隔離級別

set tx_isolation='read-committed';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

事務(wù)A：啟動一個事務(wù)

start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)B：也啟動一個事務(wù)(那么兩個事務(wù)交叉了)

   在這事務(wù)中更新數(shù)據(jù)邓馒，且未提交

start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)A：這個時候我們在事務(wù)A中能看到數(shù)據(jù)的變化嗎?

select * from tx; --------------->
+------+------+ |
| id | num | |
+------+------+ |
| 1 | 1 |--->并不能看到嘶朱！ |
| 2 | 2 | |
| 3 | 3 | |
+------+------+ |——>相同的select語句，結(jié)果卻不一樣
|

事務(wù)B：如果提交了事務(wù)B呢? |

commit; |
|

事務(wù)A: |

select * from tx; --------------->
+------+------+
| id | num |
+------+------+
| 1 | 10 |--->因為事務(wù)B已經(jīng)提交了绒净，所以在A中我們看到了數(shù)據(jù)變化
| 2 | 2 |
| 3 | 3 |
+------+------+
Repeatable read
這種事務(wù)隔離級別可以防止臟讀见咒，不可重復(fù)讀。但是可能出現(xiàn)幻像讀挂疆。它除了保證一個事務(wù)不能讀取另一個事務(wù)未提交的數(shù)據(jù)外改览，還保證了避免下面的情況產(chǎn)生(不可重復(fù)讀)。
(1)這是MySQL的默認(rèn)事務(wù)隔離級別
(2)它確保同一事務(wù)的多個實例在并發(fā)讀取數(shù)據(jù)時缤言，會看到同樣的數(shù)據(jù)行
(3)此級別可能出現(xiàn)的問題——幻讀(Phantom Read)：當(dāng)用戶讀取某一范圍的數(shù)據(jù)行時宝当，另一個事務(wù)又在該范圍內(nèi)插入了新行，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時胆萧，會發(fā)現(xiàn)有新的“幻影” 行
(4)InnoDB和Falcon存儲引擎通過多版本并發(fā)控制(MVCC庆揩，Multiversion Concurrency Control)機(jī)制解決了該問題

首先，更改隔離級別

set tx_isolation='repeatable-read';
select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

事務(wù)A：啟動一個事務(wù)

start transaction;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)B：開啟一個新事務(wù)(那么這兩個事務(wù)交叉了)

   在事務(wù)B中更新數(shù)據(jù)跌穗，并提交

start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
commit;

事務(wù)A：這時候即使事務(wù)B已經(jīng)提交了,但A能不能看到數(shù)據(jù)變化订晌？

select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 1 | --->還是看不到的！(這個級別2不一樣蚌吸，也說明級別3解決了不可重復(fù)讀問題)
| 2 | 2 |
| 3 | 3 |
+------+------+

事務(wù)A：只有當(dāng)事務(wù)A也提交了锈拨，它才能夠看到數(shù)據(jù)變化

commit;
select * from tx;
+------+------+
| id | num |
+------+------+
| 1 | 10 |
| 2 | 2 |
| 3 | 3 |
+------+------+
Serializable
這是花費(fèi)最高代價但是最可靠的事務(wù)隔離級別。事務(wù)被處理為順序執(zhí)行羹唠。除了防止臟讀奕枢，不可重復(fù)讀外，還避免了幻像讀佩微。
(1)這是最高的隔離級別
(2)它通過強(qiáng)制事務(wù)排序缝彬，使之不可能相互沖突，從而解決幻讀問題哺眯。簡言之,它是在每個讀的數(shù)據(jù)行上加上共享鎖谷浅。
(3)在這個級別，可能導(dǎo)致大量的超時現(xiàn)象和鎖競爭

首先修改隔離界別

set tx_isolation='serializable';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE |
+----------------+

事務(wù)A：開啟一個新事務(wù)

start transaction;

事務(wù)B：在A沒有commit之前奶卓，這個交叉事務(wù)是不能更改數(shù)據(jù)的

start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
事物傳播行為
PROPAGATION_REQUIRED
如果當(dāng)前沒有事務(wù)一疯，就創(chuàng)建一個新事務(wù)，如果當(dāng)前存在事務(wù)寝杖，就加入該事務(wù)违施，該設(shè)置是最常用的設(shè)置互纯。

PROPAGATION_SUPPORTS
支持當(dāng)前事務(wù)瑟幕，如果當(dāng)前存在事務(wù)，就加入該事務(wù)，如果當(dāng)前不存在事務(wù)只盹，就以非事務(wù)執(zhí)行辣往。‘

PROPAGATION_MANDATORY
支持當(dāng)前事務(wù)殖卑，如果當(dāng)前存在事務(wù)站削，就加入該事務(wù)，如果當(dāng)前不存在事務(wù)孵稽，就拋出異常许起。

PROPAGATION_REQUIRES_NEW
創(chuàng)建新事務(wù)，無論當(dāng)前存不存在事務(wù)菩鲜，都創(chuàng)建新事務(wù)园细。

PROPAGATION_NOT_SUPPORTED
以非事務(wù)方式執(zhí)行操作，如果當(dāng)前存在事務(wù)接校，就把當(dāng)前事務(wù)掛起猛频。

PROPAGATION_NEVER
以非事務(wù)方式執(zhí)行，如果當(dāng)前存在事務(wù)蛛勉，則拋出異常鹿寻。

PROPAGATION_NESTED
如果當(dāng)前存在事務(wù)，則在嵌套事務(wù)內(nèi)執(zhí)行诽凌。如果當(dāng)前沒有事務(wù)毡熏，則執(zhí)行與PROPAGATION_REQUIRED類似的操作。

Mysql(Innodb)如何避免幻讀
幻讀Phantom Rows
幻讀問題是指一個事務(wù)的兩次不同時間的相同查詢返回了不同的的結(jié)果集皿淋。例如:一個 select 語句執(zhí)行了兩次招刹，但是在第二次返回了第一次沒有返回的行,那么這些行就是“phantom” row.
read view(或者說 MVCC)實現(xiàn)了一致性不鎖定讀(Consistent Nonlocking Reads)，從而避免了幻讀

淺談mysql mvcc
mysql的大多數(shù)事務(wù)型存儲引擎實現(xiàn)的都不是簡單的行級鎖窝趣，基于提升并發(fā)性能的考慮疯暑，他們一般都同時實現(xiàn)了多版本并發(fā)控制，可以認(rèn)為MVCC是行級鎖的一個變種哑舒，但是它在很多情況下避免了加鎖操作妇拯，因此開銷更低，雖然實現(xiàn)機(jī)制有所不同洗鸵，但大都實現(xiàn)了非阻塞的讀操作越锈，寫操作也只鎖定必要的行。
MVCC的實現(xiàn)是通過保存數(shù)據(jù)在某個時間點(diǎn)的快照來實現(xiàn)的膘滨，也就是說甘凭，不管需要執(zhí)行多長時間，只要事務(wù)開始時間相同火邓，每個事務(wù)看到的數(shù)據(jù)都是一致的丹弱，事務(wù)開始的時間不同時德撬，每個事務(wù)對同一張表，同一時刻看到的數(shù)據(jù)可能是不一樣的（因為不同的時間點(diǎn)可能數(shù)據(jù)就已經(jīng)產(chǎn)生了不同的快照版本躲胳，而每個事務(wù)在默認(rèn)的RR隔離級別下只能看到事務(wù)開始時的數(shù)據(jù)快照）蜓洪。說道不同的存儲引擎的MVCC實現(xiàn)是不同的，典型的有樂觀并發(fā)控制和悲觀并發(fā)控制坯苹，下面簡單說明MVCC是如何工作的：
例如：
此時books表中有5條數(shù)據(jù)隆檀，版本號為1
事務(wù)A，系統(tǒng)版本號2：select * from books粹湃；因為1<=2所以此時會讀取5條數(shù)據(jù)恐仑。
事務(wù)B，系統(tǒng)版本號3：insert into books …为鳄，插入一條數(shù)據(jù)菊霜，新插入的數(shù)據(jù)版本號為3，而其他的數(shù)據(jù)的版本號仍然是2济赎，插入完成之后commit鉴逞，事務(wù)結(jié)束。
事務(wù)A司训，系統(tǒng)版本號2：再次select * from books构捡；只能讀取<=2的數(shù)據(jù)，事務(wù)B新插入的那條數(shù)據(jù)版本號為3壳猜，因此讀不出來勾徽，解決了幻讀的問題。
MVCC只在repeatable-read和read-committed兩個隔離級別下才工作统扳，其他兩個隔離級別都和MVCC不兼容喘帚，因為read uncommitted總是讀取最新的數(shù)據(jù)行，而不是符合當(dāng)前事務(wù)版本的數(shù)據(jù)行咒钟，而serializeble則會對所有讀取的行都加鎖吹由。
另外要注意：MVCC在RR和RC隔離級別下的區(qū)別，在RR隔離級別下朱嘴，一個事務(wù)只能讀取到事務(wù)開始的那個時刻的數(shù)據(jù)快照倾鲫，即，別的事務(wù)修改并提交的數(shù)據(jù)在自身沒有提交之前一般讀取不到（加for update語句的select除外萍嬉，因為這個語句要對數(shù)據(jù)加X鎖必須讀取最新的數(shù)據(jù)快照）乌昔， 在RC隔離級別下，事務(wù)總是讀取數(shù)據(jù)行的最新快照壤追，即會產(chǎn)生不可重復(fù)讀的問題磕道。
repeatable-read 解決幻讀還有一個特烈就是這個查詢可以看到于自己開始之后的同一個事務(wù)產(chǎn)生的變化，這個特例會產(chǎn)生一些反常的現(xiàn)象行冰。
repeatable-read 幻讀特例：

SESSION_A開始事務(wù)并創(chuàng)建快照
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)

SESSION_A更新了它并沒有"看"到的行
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
觀察實驗步驟可以發(fā)現(xiàn)溺蕉，在倒數(shù)第二次查詢中贯卦，出現(xiàn)了一個并不存在的狀態(tài)
這里A的前后兩次讀，均為快照讀焙贷，而且是在同一個事務(wù)中。但是B先插入直接提交贿堰，此時A再update辙芍，update屬于當(dāng)前讀，所以可以作用于新插入的行羹与，并且將修改行的當(dāng)前版本號設(shè)為A的事務(wù)號故硅，所以第二次的快照讀，是可以讀取到的纵搁，因為同事務(wù)號吃衅。這種情況符合MVCC的規(guī)則，如果要稱為一種幻讀也非不可腾誉，算為一個特殊情況來看待吧徘层。

深層次的原理分析
在MVCC并發(fā)控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當(dāng)前讀 (current read)利职。

快照讀趣效，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖猪贪。

當(dāng)前讀跷敬，讀取的是記錄的最新版本，并且热押，當(dāng)前讀返回的記錄西傀，都會加上鎖，保證其他事務(wù)不會再并發(fā)修改這條記錄桶癣。

在一個支持MVCC并發(fā)控制的系統(tǒng)中拥褂，哪些讀操作是快照讀？哪些操作又是當(dāng)前讀呢牙寞？以MySQL InnoDB為例：

快照讀：簡單的select操作肿仑，屬于快照讀，不加鎖碎税。(當(dāng)然尤慰，也有例外，下面會分析)
select * from table where ?;

當(dāng)前讀：特殊的讀操作雷蹂，插入/更新/刪除操作伟端，屬于當(dāng)前讀，需要加鎖匪煌。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的語句责蝠，都屬于當(dāng)前讀党巾，讀取記錄的最新版本。并且霜医，讀取之后齿拂，還需要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄，對讀取記錄加鎖肴敛。其中署海，除了第一條語句，對讀取記錄加S鎖 (共享鎖)外医男，其他的操作砸狞，都加的是X鎖 (排它鎖)。

MySQL/InnoDB定義的4種隔離級別：

Read Uncommited
可以讀取未提交記錄镀梭。此隔離級別刀森，不會使用，忽略报账。

Read Committed (RC)
快照讀（普通的select就是快照讀）是不會對讀取的行加鎖的研底，所以存在幻讀現(xiàn)象。

針對當(dāng)前讀透罢，RC隔離級別保證對讀取到的記錄加鎖 (記錄鎖)飘哨，加鎖后的內(nèi)容不允許update和delete，但是可以新增琐凭，所以存在幻讀現(xiàn)象芽隆。
例如：

事物1
SELECT * from qq for update ; 5條記錄
事物2
delete from qq limit 1; commit； 會阻塞 因為上面一條語句有記錄鎖
事物1
update qq set bb=2 ; commit统屈；會更新5條 然后事物2執(zhí)行完畢刪除一條
例如存在幻讀現(xiàn)象：

事物1
SELECT * from qq for update ; 5條記錄
事物2
insert into qq values(123456); commit胚吁；不會阻塞
事物1
update qq set bb=2 ; commit；會更新6條
Repeatable Read (RR)
快照讀（普通的select就是快照讀）是不會對讀取的行加鎖的愁憔，所以此情況還是會存在幻讀現(xiàn)象腕扶。

針對當(dāng)前讀，RR隔離級別保證對讀取到的記錄加鎖 (記錄鎖)吨掌，同時保證對讀取的范圍加鎖半抱，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)，不存在幻讀現(xiàn)象膜宋。
例如：

事物1
SELECT * from qq for update ; 5條記錄
事物2
insert into qq values(123456); commit窿侈；會阻塞 因為上面一條語句有間隙鎖
事物1
update qq set bb=2 ; commit；會更新5條 然后執(zhí)行事物2 新增一條
例如：

事物1
SELECT * from qq for update ; 5條記錄
事物2
delete from qq limit 1; commit秋茫； 會阻塞 因為上面一條語句有記錄鎖
事物1
update qq set bb=2 ; commit史简；會更新5條 然后事物2執(zhí)行完畢刪除一條
Serializable
從MVCC并發(fā)控制退化為基于鎖的并發(fā)控制。不區(qū)別快照讀與當(dāng)前讀肛著，所有的讀操作均為當(dāng)前讀圆兵，讀加讀鎖 (S鎖)跺讯，寫加寫鎖 (X鎖)。

Serializable隔離級別下殉农，讀寫沖突刀脏，因此并發(fā)度急劇下降，在MySQL/InnoDB下不建議使用超凳。

InnoDB通過Nextkey lock解決了當(dāng)前讀時的幻讀問題
Innodb行鎖分為:

類型 說明
Record Lock: 在索引上對單行記錄加鎖.
Gap Lock: 鎖定一個范圍的記錄,但不包括記錄本身.鎖加在未使用的空閑空間上,可能是兩個索引記錄之間愈污，也可能是第一個索引記錄之前或最后一個索引之后的空間.
Next-Key Lock: 行鎖與間隙鎖組合起來用就叫做Next-Key Lock。鎖定一個范圍聪建，并且鎖定記錄本身。對于行的查詢茫陆，都是采用該方法金麸，主要目的是解決幻讀的問題。
實驗1
創(chuàng)建表

(mysql@localhost) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
開始實驗

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>select * from t5 where id=7 for update;
+------+
| id |
+------+
| 7 |
+------+
1 row in set (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t5 values(5); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(7); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>insert into t5 values(9); --被阻塞
^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 4 |
| 7 |
| 10 |
| 12 |
+------+
6 rows in set (0.00 sec)
當(dāng)以當(dāng)前讀模式select * from t5 where id=7 for update;獲取 id=7的數(shù)據(jù)時,產(chǎn)生了 Next-Key Lock,鎖住了4-10范圍和 id=7單個record
從而阻塞了 SESSION_B在這個范圍內(nèi)插入數(shù)據(jù)簿盅，而在除此之外的范圍內(nèi)是可以插入數(shù)據(jù)的挥下。
在倒數(shù)第二個查詢中,因為 read view 的存在，避免了我們看到 2和12兩條數(shù)據(jù)桨醋，避免了幻讀
同時因為 Next-Key Lock 的存在,阻塞了其他回話插入數(shù)據(jù)棚瘟，因此當(dāng)前模式讀不會產(chǎn)生幻讀(select for update 是以當(dāng)前讀模式獲取數(shù)據(jù))

盡量使用唯一索引,因為唯一索引會把Next-Key Lock降級為Record Lock
實驗2
創(chuàng)建表

(mysql@localhost) [fandb]> create table t6(id int primary key);
Query OK, 0 rows affected (0.02 sec)

SESSION_A>insert into t6 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
開始實驗

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>select * from t6 where id=7 for update;
+----+
| id |
+----+
| 7 |
+----+
1 row in set (0.00 sec)

SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t6 values(5); --插入成功沒有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>insert into t6 values(8); --插入成功沒有阻塞
Query OK, 1 row affected (0.00 sec)

SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)

SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
+----+
6 rows in set (0.00 sec)
當(dāng) id 列有唯一索引,Next-Key Lock 會降級為 Records Lock

InnoDB引擎的行鎖和表鎖
行鎖和表鎖
在mysql 的 InnoDB引擎支持行鎖，與Oracle不同喜最，mysql的行鎖是通過索引加載的偎蘸，即是行鎖是加在索引響應(yīng)的行上的，要是對應(yīng)的SQL語句沒有走索引瞬内，則會全表掃描迷雪，
行鎖則無法實現(xiàn)，取而代之的是表鎖虫蝶。
表鎖：不會出現(xiàn)死鎖章咧，發(fā)生鎖沖突幾率高，并發(fā)低能真。
行鎖：會出現(xiàn)死鎖赁严，發(fā)生鎖沖突幾率低，并發(fā)高粉铐。
鎖沖突：例如說事務(wù)A將某幾行上鎖后疼约，事務(wù)B又對其上鎖，鎖不能共存否則會出現(xiàn)鎖沖突蝙泼。（但是共享鎖可以共存忆谓，共享鎖和排它鎖不能共存，排它鎖和排他鎖也不可以）
死鎖：例如說兩個事務(wù)踱承，事務(wù)A鎖住了15行倡缠，同時事務(wù)B鎖住了610行哨免，此時事務(wù)A請求鎖住610行，就會阻塞直到事務(wù)B施放610行的鎖昙沦，而隨后事務(wù)B又請求鎖住15行琢唾，事務(wù)B也阻塞直到事務(wù)A釋放15行的鎖。死鎖發(fā)生時盾饮，會產(chǎn)生Deadlock錯誤采桃。
鎖是對表操作的，所以自然鎖住全表的表鎖就不會出現(xiàn)死鎖丘损。

行鎖的類型
行鎖分 共享鎖 和 排它鎖普办。

共享鎖（Shared Lock，也叫S鎖）
表示對數(shù)據(jù)進(jìn)行讀操作徘钥。因此多個事務(wù)可以同時為一個對象加共享鎖衔蹲，對于同一數(shù)據(jù)都能訪問到數(shù)據(jù)，但是只能讀不能修改呈础。
產(chǎn)生共享鎖的sql：select * from ad_plan lock in share mode;
共享鎖的使用場景
　　SELECT … LOCK IN SHARE MODE走的是IS鎖(意向共享鎖)舆驶，即在符合條件的rows上都加了共享鎖，這樣的話而钞，其他人可以讀取這些記錄沙廉，也可以繼續(xù)添加IS鎖，但是無法修改這些記錄直到你這個加鎖的過程執(zhí)行完成(完成的情況有：事務(wù)的提交臼节，事務(wù)的回滾撬陵，否則直接鎖等待超時)。
　　SELECT … LOCK IN SHARE MODE的應(yīng)用場景適合于兩張表存在關(guān)系時的寫操作网缝，拿mysql官方文檔的例子來說袱结，一個表是child表，一個是parent表途凫，假設(shè)child表的某一列child_id映射到parent表的c_child_id列垢夹，那么從業(yè)務(wù)角度講，此時我直接insert一條child_id=100記錄到child表是存在風(fēng)險的维费，因為剛insert的時候可能在parent表里刪除了這條c_child_id=100的記錄果元，那么業(yè)務(wù)數(shù)據(jù)就存在不一致的風(fēng)險。正確的方法是再插入時執(zhí)行select * from parent where c_child_id=100 lock in share mode,鎖定了parent表的這條記錄犀盟，然后執(zhí)行insert into child(child_id) values (100)就不會存在這種問題了而晒。

排他鎖(Exclusive Lock，也叫X鎖)
排他鎖又稱為寫鎖阅畴，簡稱X鎖倡怎，顧名思義，排他鎖就是不能與其他所并存，如一個事務(wù)獲取了一個數(shù)據(jù)行的排他鎖监署，其他事務(wù)就不能再獲取該行的其他鎖颤专，包括共享鎖和排他鎖，可以直接通過select …from…查詢數(shù)據(jù)钠乏，因為普通查詢沒有任何鎖機(jī)制栖秕。獲取排他鎖的事務(wù)是可以對數(shù)據(jù)就行讀取和修改。
mysql InnoDB引擎默認(rèn)的修改數(shù)據(jù)語句晓避，update,delete,insert都會自動給涉及到的數(shù)據(jù)加上排他鎖簇捍，select語句默認(rèn)不會加任何鎖類型。
產(chǎn)生排他鎖的sql： select * from ad_plan for update;
排他鎖的使用場景：

使用場景一：訂單的商品數(shù)量
　　　　但是如果是同一張表的應(yīng)用場景俏拱，舉個例子暑塑，電商系統(tǒng)中計算一種商品的剩余數(shù)量，在產(chǎn)生訂單之前需要確認(rèn)商品數(shù)量>=1,產(chǎn)生訂單之后應(yīng)該將商品數(shù)量減1锅必。
　　　　1 select amount from product where product_name=’XX’;
　　　　2 update product set amount=amount-1 where product_name=’XX’;

顯然1的做法是是有問題事格，因為如果1查詢出amount為1，但是這時正好其他session也買了該商品并產(chǎn)生了訂單况毅，那么amount就變成了0分蓖，那么這時第二步再執(zhí)行就有問題尔艇。那么采用lock in share mode可行嗎尔许，也是不合理的，因為兩個session同時鎖定該行記錄時终娃，這時兩個session再update時必然會產(chǎn)生死鎖導(dǎo)致事務(wù)回滾味廊。以下是操作范例(按時間順序)

使用場景一：數(shù)據(jù)表的狀態(tài)

如果存在一張表記錄一個商品的狀態(tài)，在訂單的變化過程中棠耕，訂單的狀態(tài)是不斷變化的余佛，而且變化的過程中肯定也會有并發(fā)的問題，而且很多時候與其他系統(tǒng)有交互窍荧，會存在補(bǔ)償?shù)那闆r辉巡，所以并發(fā)的可能性很大,補(bǔ)償或者為了增加狀態(tài)修改的成功可能性，2次改變狀態(tài)的情況也有蕊退，樓主就遇到了這種情況郊楣，真操蛋。于是看到有這樣的for update寫法瓤荔。

1 update order set status = 1 where product_id = ‘1’;

2 insert order_flow (…………..) value (………)

這樣的情況下就有可能訂單的狀態(tài)已經(jīng)更新完成了净蚤，但是補(bǔ)償這些額外的消息把狀態(tài)又更新為待處理或者插入了多條流水的情況(多條流水的可能性大，狀態(tài)的那種可能補(bǔ)償滯后)输硝。這個時候就可以使用select …. from order where order_id = ‘1’ for update今瀑，先鎖住要修改狀態(tài)的表，這樣就不會別人操作了，自己先后面把流水插入橘荠，然后更新狀態(tài)屿附，完美。但是加了鎖之后性能就很慢了砾医，擔(dān)心性能影響拿撩，而且有可能存在死鎖的情況，后面我就修改為流水表中增加一個唯一索引如蚜，這樣插入流水報錯就是已經(jīng)處理過的記錄了压恒。這樣就不會存在性能問題。

通過對比错邦，lock in share mode適用于兩張表存在業(yè)務(wù)關(guān)系時的一致性要求探赫，for update適用于操作同一張表時的一致性要求。

行鎖注意幾點(diǎn)
1.行鎖必須有索引才能實現(xiàn)撬呢，否則會自動鎖全表伦吠，那么就不是行鎖了。
2.兩個事務(wù)不能鎖同一個索引魂拦，例如：

事務(wù)A先執(zhí)行：
select math from zje where math>60 for update;

事務(wù)B再執(zhí)行：
select math from zje where math<60 for update毛仪；
這樣的話，事務(wù)B是會阻塞的芯勘。如果事務(wù)B把 math索引換成其他索引就不會阻塞箱靴，但注意，換成其他索引鎖住的行不能和math索引鎖住的行有重復(fù)荷愕。
3.insert 衡怀，delete ， update在事務(wù)中都會自動默認(rèn)加上排它鎖安疗。
實現(xiàn)：

會話1：
begin抛杨；
select math from zje where math>60 for update；
會話2：
begin荐类；
update zje set math=99 where math=68怖现；
阻塞...........
會話相當(dāng)與用戶
如上丰包，會話1先把zje表中math>60的行上排它鎖佩谷。然后會話2試圖把math=68的行進(jìn)行修改，math=68處于math>60中详幽，所以是已經(jīng)被鎖的厌小，會話2進(jìn)行操作時恢共，
就會阻塞，等待會話1把鎖釋放璧亚。當(dāng)commit時或者程序結(jié)束時讨韭，會釋放鎖脂信。

mysql死鎖
所謂死鎖: 是指兩個或兩個以上的進(jìn)程在執(zhí)行過程中,
因爭奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用,它們都將無法推進(jìn)下去.
此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖,這些永遠(yuǎn)在互相等竺的進(jìn)程稱為死鎖進(jìn)程.
表級鎖不會產(chǎn)生死鎖.所以解決死鎖主要還是針對于最常用的InnoDB.
死鎖的關(guān)鍵在于：兩個(或以上)的Session加鎖的順序不一致。
那么對應(yīng)的解決死鎖問題的關(guān)鍵就是：讓不同的session加鎖有次序
死鎖例子：
下面兩個事物都執(zhí)行了第一條update語句透硝，更新了該數(shù)據(jù)同事鎖定了該行數(shù)據(jù)接著每個事物都去執(zhí)行第二天語句狰闪，卻發(fā)現(xiàn)都被對方鎖定，進(jìn)入死循環(huán)濒生。

事物1
start TRANSACTION
update STOCKPRICE SET close = 89 where stock_id =4 and date = '2002-05-01'
update STOCKPRICE SET close = 22 where stock_id =3 and date = '2002-05-02'

事物2
start TRANSACTION
update STOCKPRICE SET close = 11 where stock_id =3 and date = '2002-05-02'
update STOCKPRICE SET close = 33 where stock_id =4 and date = '2002-05-01'
死鎖解決辦法：數(shù)據(jù)庫實現(xiàn)了個各種死鎖的檢測和超時機(jī)制埋泵。
InnoDb可以檢測到死鎖的循環(huán)依賴，并返回一個錯誤罪治，另一個就是查詢時間達(dá)到鎖超時時間設(shè)定后自動放棄鎖請求丽声。

如何盡可能避免死鎖
1）以固定的順序訪問表和行。比如對第2節(jié)兩個job批量更新的情形觉义，簡單方法是對id列表先排序雁社，后執(zhí)行，這樣就避免了交叉等待鎖的情形晒骇；又比如對于3.1節(jié)的情形霉撵，將兩個事務(wù)的sql順序調(diào)整為一致，也能避免死鎖洪囤。

2）大事務(wù)拆小徒坡。大事務(wù)更傾向于死鎖，如果業(yè)務(wù)允許瘤缩，將大事務(wù)拆小喇完。

3）在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源款咖，減少死鎖概率何暮。

4）降低隔離級別奄喂。如果業(yè)務(wù)允許铐殃，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC跨新，可以避免掉很多因為gap鎖造成的死鎖富腊。

5）為表添加合理的索引∮蛘剩可以看到如果不走索引將會為表的每一行記錄添加上鎖赘被，死鎖的概率大大增大。

死鎖查詢
1肖揣、查詢是否鎖表
show OPEN TABLES where In_use > 0;

2民假、查詢進(jìn)程
show processlist
查詢到相對應(yīng)的進(jìn)程===然后 kill id

3、查看正在鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

4龙优、查看等待鎖的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

本文作者： 開發(fā)者首頁
本文鏈接： https://blog.kfzsy.com/mysql-transaction.html
版權(quán)聲明： 本博客所有文章除特別聲明外羊异，均采用 BY-NC-SA 許可協(xié)議。轉(zhuǎn)載請注明出處！

本文由博客群發(fā)一文多發(fā)等運(yùn)營工具平臺 OpenWrite 發(fā)布

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末野舶，一起剝皮案震驚了整個濱河市易迹，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌平道，老刑警劉巖睹欲，帶你破解...
    沈念sama閱讀 206,214評論 6贊 481
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異一屋，居然都是意外死亡窘疮，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,307評論 2贊 382
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門冀墨，熙熙樓的掌柜王于貴愁眉苦臉地迎上來考余，“玉大人，你說我怎么就攤上這事轧苫〕蹋” “怎么了？”我有些...
    開封第一講書人閱讀 152,543評論 0贊 341
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵含懊，是天一觀的道長身冬。 經(jīng)常有香客問我，道長岔乔，這世上最難降的妖魔是什么酥筝？ 我笑而不...
    開封第一講書人閱讀 55,221評論 1贊 279
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任，我火速辦了婚禮雏门，結(jié)果婚禮上嘿歌，老公的妹妹穿的比我還像新娘。我一直安慰自己茁影，他們只是感情好宙帝，可當(dāng)我...
    茶點(diǎn)故事閱讀 64,224評論 5贊 371
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布。 她就那樣靜靜地躺著募闲，像睡著了一般步脓。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上浩螺，一...
    開封第一講書人閱讀 49,007評論 1贊 284
  • 城市分裂傳說
    那天靴患，我揣著相機(jī)與錄音，去河邊找鬼要出。 笑死鸳君，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的患蹂。 我是一名探鬼主播或颊，決...
    沈念sama閱讀 38,313評論 3贊 399
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼腿时，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了饭宾？” 一聲冷哼從身側(cè)響起批糟，我...
    開封第一講書人閱讀 36,956評論 0贊 259
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎看铆，沒想到半個月后徽鼎，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
    沈念sama閱讀 43,441評論 1贊 300
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡弹惦，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 35,925評論 2贊 323
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年否淤，在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片棠隐。...
    茶點(diǎn)故事閱讀 38,018評論 1贊 333
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡石抡，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出助泽，到底是詐尸還是另有隱情啰扛，我是刑警寧澤，帶...
    沈念sama閱讀 33,685評論 4贊 322
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布嗡贺，位于F島的核電站隐解，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏诫睬。R本人自食惡果不足惜煞茫，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,234評論 3贊 307
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望摄凡。 院中可真熱鬧续徽，春花似錦、人聲如沸亲澡。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,240評論 0贊 19
  • 一樁弒父案，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽谷扣。三九已至土全，卻和暖如春捎琐，著一層夾襖步出監(jiān)牢的瞬間会涎，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,464評論 1贊 261
  • 情欲美人皮
    我被黑心中介騙來泰國打工瑞凑， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留末秃，地道東北人。 一個月前我還...
    沈念sama閱讀 45,467評論 2贊 352
  • 代替公主和親
    正文 我出身青樓籽御，卻偏偏與公主長得像练慕，于是被迫代替她去往敵國和親惰匙。 傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,762評論 2贊 345