事務(wù)隔離級別

幻讀：事務(wù)在插入已經(jīng)檢查過不存在的記錄時漂辐，驚奇的發(fā)現(xiàn)這些數(shù)據(jù)已經(jīng)存在了嗜逻，之前檢測獲取到的數(shù)據(jù)如同鬼影一般。

不可重復(fù)讀：同樣的條件灾挨，你讀取過的數(shù)據(jù)讼溺，再次讀取出來發(fā)現(xiàn)值不一樣了吉懊。

mysql 在RR級別下拖吼，通過MVCC解決了部分幻讀問題。

準(zhǔn)備

drop table if exists city;
create table city(
    id int(11) primary key,
    name varchar(200)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

一些命令

-- 獲取當(dāng)前的隔離級別
select @@tx_isolation;


SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
-- 設(shè)置鎖超時時間
set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;

READ-UNCOMMITTED級別下

從上面執(zhí)行過程看以看到琳水，T1事務(wù)肆糕，在執(zhí)行2操作之后，還沒提交在孝，T2去讀的時候诚啃，就讀到了數(shù)據(jù)，當(dāng)T1 回滾之后私沮，T2再次讀取始赎，這條數(shù)據(jù)就不見了。

這就是在RU級別下，產(chǎn)生的讀取到別人未提交的數(shù)據(jù)從而產(chǎn)生的臟讀极阅。

READ-COMMITTED級別下

幻讀

T1和T2同時開啟事務(wù)胃碾，且這個時候city表為空。

在第2步筋搏，T1插入一條數(shù)據(jù)仆百，未提交。T2這個時候讀取數(shù)據(jù)是讀取不到的（RU級別的區(qū)別）奔脐。
但是在T1 COMMIT之后俄周，T2這個時候查詢的時候就跟之前不一樣，這就是讀到了別人已經(jīng)提交過的數(shù)據(jù)髓迎。

不可重復(fù)讀

在第7行峦朗，T2讀取到了T1未提交的數(shù)據(jù)，5,7兩次讀取到的結(jié)果不一致排龄，產(chǎn)生了不可重復(fù)讀波势。

REPEATABLE READ 級別下

解決不可重復(fù)讀

無論T1是否提交，T2讀到的數(shù)據(jù)始終是開啟事務(wù)時查詢到的數(shù)據(jù)

解決一般性幻讀

可以看到橄维，無論T1是否提交數(shù)據(jù)尺铣，在T2事務(wù)中總是看不到最新的數(shù)據(jù)，這樣就解決了幻讀争舞。
MYSQL通過MVCC解決了一般性幻讀和可重復(fù)讀凛忿；

InnoDB存儲引擎MVCC的實(shí)現(xiàn)策略

在每一行數(shù)據(jù)中額外保存兩個隱藏的列：當(dāng)前行創(chuàng)建時的版本號和刪除時的版本號（可能為空，其實(shí)還有一列稱為回滾指針竞川，用于事務(wù)回滾）店溢。這里的版本號并不是實(shí)際的時間值，而是系統(tǒng)版本號委乌。每開始新的事務(wù)床牧，系統(tǒng)版本號都會自動遞增。事務(wù)開始時刻的系統(tǒng)版本號作為事務(wù)的版本號福澡，用來和查詢每行記錄的版本號進(jìn)行比較叠赦。

MVCC下InnoDB的增刪改查是如何工作的

• 插入數(shù)據(jù)（insert）：記錄的版本號即當(dāng)前事務(wù)的版本號驹马。
  執(zhí)行一條插入的SQL語句,insert into test values(1,"test");
  假設(shè)事務(wù)ID為1革砸，那么插入后的數(shù)據(jù)行如下：

• 在更新操作的時候，采用的是先標(biāo)記舊的那行記錄為已刪除糯累，并且刪除版本號是事務(wù)版本號算利，然后插入一行新的記錄的方式。

比如執(zhí)行更新語句：update test set name="new_test" where id=1;
事務(wù)ID=2

• 刪除操作的時候泳姐，就把事務(wù)版本號作為刪除版本號效拭。
  比如：delete from test where id=1;
  事務(wù)ID=3

• 查詢操作：

從上面的描述可看到，在查詢時要符合以下兩個條件的記錄才能被事務(wù)查詢出來：

1. 刪除版本號未指定或者大于當(dāng)前事務(wù)版本號，即查詢事務(wù)開啟后確保讀取的行未被刪除缎患。（即上述事務(wù)ID=2的事務(wù)查詢時慕的，依然能讀取到事務(wù)ID=3所刪除的數(shù)據(jù)行）

2. 創(chuàng)建版本號小于或者等于當(dāng)前事務(wù)版本號，也就是說記錄創(chuàng)建是在當(dāng)前事務(wù)中（等于的情況）或者在當(dāng)前事務(wù)啟動之前的其他事務(wù)進(jìn)行的insert

仍然存在幻讀

T2明明沒有查詢到數(shù)據(jù)挤渔，但是插入的時候肮街，卻提示已經(jīng)存在了數(shù)據(jù)，就像見鬼了一樣判导。

是什么造成了這樣一個問題呢嫉父？

在MVCC中，讀操作分為兩種：一種是快照讀眼刃，另外一種是當(dāng)前讀绕辖。

• 快照讀即按照版本號讀取
• 當(dāng)前讀則是讀取最新的數(shù)據(jù)，操作：insert/update/delete都屬于當(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 ?;

delete ,update ,insert ,select .. for update 都是加的X鎖即排它鎖。
select .. lock in share mode 是共享鎖

insert into city values(11,"test");

通過next key lock 解決幻讀

Next Key Lock

InnoDB有三種行鎖算法：

• Record Lock:單條記錄上的鎖
• Gap Lock:間隙鎖昵骤，鎖定一個范圍弟头，但不包括記錄本身。
• Next Key Lock:Record Lock + Gap Lock涉茧；鎖定一個范圍赴恨，并且鎖定記錄本身。

GAP鎖的目的是防止出現(xiàn)兩次當(dāng)前讀不一致的情況

drop table if exists person;
CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into person values(1,10,"t1");
insert into person values(2,20,"t2");
insert into person values(3,30,"t3");

可以發(fā)現(xiàn)在age索引范圍內(nèi)[10,30)

使用普通索引增加X鎖

 drop table if exists book;
 create table book(
    id int ,
    key id_x(id)
) engine=innodb;

insert into book values(1),(3),(5),(8),(11);

如果查詢條件中沒有使用索引且加了X鎖伴栓，那么將會鎖住整張表伦连。

create table book(
    id int ,
    price int ,
    primary key(price)
) engine=innodb;

 insert into book values (1,9),(3,5),(5,8),(8,6),(11,2);

GAP的范圍

drop table if exists book;
create table book(
  id int,
  key id_x(id)
) engine=innodb;
insert into book values(8),(3);

從3,4,5,6操作中可以看到，能夠插入的數(shù)據(jù)只有 id<3的情況
why ??
我們先看下 2操作之后產(chǎn)生間隙
(3,8] (8 ~ ∞]
所以钳垮，id>=8都無法插入惑淳；

但是為什么3也插入不了呢？

接下來看下面的操作：

create table book (id int ,seq int primary key ,key id_x(id)) engine=innodb;

insert into book values(8,10),(3,5);

第一個表是沒有主鍵的饺窿，所以mysql會有一個rowid作為主鍵歧焦，是遞增的。而下面的操作是有主鍵的肚医。
對比上下兩個操作绢馍，我們可以得出一個結(jié)論：
假設(shè)間隙是： (3,8]₍₈∞)
那么 可插入的值為： id<=3 and seq <5

接著繼續(xù)插入一條數(shù)據(jù)：

insert into book values(11,2);

3操作之后，加鎖的間隙為：
(3,8],(8,11]
那么可插入的值為：
(id <=3 and seq<5 ) or (id >=11 and seq>2)

間隙是根據(jù)什么來的呢肠套？舰涌？
索引的結(jié)構(gòu)

在B+Tree索引的葉子節(jié)點(diǎn)是一個順序的列表，那么上述的結(jié)構(gòu)的鏈表如下所示：

(3,5) -> (8,10) -> (11,2)
從這個結(jié)構(gòu)來看你稚，如果現(xiàn)在有一條數(shù)據(jù)(11,0)插入瓷耙，結(jié)果產(chǎn)生的鏈表應(yīng)該為：
(3,5) -> (8,10) -> (11,0) -> (11,2)

所以朱躺，間隙就是根據(jù)鏈表結(jié)構(gòu)來的：
以上面鏈表來看，如果 執(zhí)行的是select * from book where id=8 for update ;

那么插入的范圍應(yīng)該是：
(id <=3 and seq<4) or (id>=11 and seq>0)

當(dāng)查詢的索引含有唯一屬性的時候搁痛，Next-Key Lock會進(jìn)行優(yōu)化长搀，將其降級為Record Lock，即僅鎖住索引本身鸡典，而不是范圍盈滴。

drop table if exists book;
create table book(
     id int primary key,
     seq int,
    unique key seq_x(seq)
)engine=innodb;
insert into book values(3,3),(5,5),(8,8),(11,11),(1,1);

降級為record鎖

從操作3可以看到，2操作并沒有鎖定間隙轿钠。

注意： 通過主鍵或者唯一索引來鎖定不存在的值巢钓，也會產(chǎn)生GAP鎖定。

從操作3可以看到疗垛，操作2鎖定了一條不存在的數(shù)據(jù)症汹，仍然產(chǎn)生了間隙鎖。

幻讀的理解

首先贷腕，mysql 幻讀并非是“一個事務(wù)內(nèi)進(jìn)行兩次相同操作居然得到了不一樣的結(jié)果”背镇，因?yàn)樗静豢赡馨l(fā)生在使用了read view/MVCC的RR隔離級別下，這種幻讀的定義更適合給Oracle泽裳，Oracle的事務(wù)隔離級別只有兩級瞒斩，RC和Serializable。

這里給出mysql幻讀的比較形象的場景：
users:id 主鍵

1.T1:select * from users where id = 1;
2.T2:insert into `users`(`id`,`name`) values(1,'big cat');
3.T1:insert into `users`(`id`,`name`) values(1,'big cat');

T1:主事務(wù)涮总，檢測表中是否有id為1的記錄胸囱，沒有則插入，這是我們期望的正常業(yè)務(wù)邏輯瀑梗。
T2:干擾事務(wù)烹笔，目的在于擾亂T1的正常的事務(wù)執(zhí)行。
在RR隔離級別下抛丽，1,2是會正常執(zhí)行的谤职，3則會報錯主鍵沖突，對于T1的業(yè)務(wù)來說是執(zhí)行失敗的亿鲜，這里T1就是發(fā)生了幻讀允蜈，因?yàn)門1讀取的數(shù)據(jù)狀態(tài)并不能支持他的下一步的業(yè)務(wù)，見鬼了一樣蒿柳。
在Serializable隔離級別下饶套，1執(zhí)行時是會隱式的添加gap共享鎖的，從而2會被阻塞其馏，3會正常執(zhí)行凤跑，對于T1來說業(yè)務(wù)是正確的，成功的扼殺了擾亂業(yè)務(wù)的T2叛复，對于T1來說他讀取的狀態(tài)是可以拿來支持業(yè)務(wù)的。
所以mysql的幻讀并非什么讀取兩次返回結(jié)果集不同，而是事務(wù)在插入事先檢測不存在的記錄時褐奥，驚奇的發(fā)現(xiàn)這些數(shù)據(jù)已經(jīng)存在了咖耘，之前的檢測讀獲取到的數(shù)據(jù)如同鬼影一般。
這里要靈活的理解讀取的意思撬码，第一次select是讀取儿倒，第二次的insert其實(shí)也屬于隱式的讀取，只不過是在mysql的機(jī)制中讀取的呜笑，插入數(shù)據(jù)也是要先讀取一下有沒有主鍵沖突才能決定是否執(zhí)行插入夫否。
不可重復(fù)讀側(cè)重表達(dá)讀-讀，幻讀則是說讀-寫叫胁，用寫來證實(shí)讀的是鬼影凰慈。

死鎖

死鎖檢測回滾策略：保留回滾代價最小的事務(wù)。