值班问题：insert语句插入了两条数据？
`c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT '0',
`c4` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 在使用如下的語法時，怎么會插入兩條?c2 = 1007 and c3 = 1的記錄？ insert into aa(c2,c4) select 1007, 8 from dual where not exists(select * from aa where c2 = 1007 and c3 = 1);
毫無疑問，先排查一下用戶實例的binlog，發現確實一前一后有兩條插入語句。
那會不會是MySQL的bug呢？即使是bug，也得要先復現出來吧。
?
如何復現呢？
session 1:
begin?work;
insert into aa(c2,c4) select 1007, 8 from dual where not exists(select * from aa where c2 = 1007 and c3 = 1);這時先不commit
?
session 2:
begin?work;
insert?into?aa(c2,c4)?select?1002,?5?from?dual?where?not?exists(select?*?from?aa?where?c2?=?1002?and?c3?=?0);
commit
?
session1:
commit
?
ok，有點眉目了。在這種情況下，是可以穩定復現的。用戶反映他的業務是自動提交的。如果兩個insert來自不同的服務器，第一次執行的時間很長還未提交，第二個就開始執行了。也是可能出現的。后來從SQL審計日志從驗證了的確如此。
?
不過客戶去復現時，卻依然復現不出來。這時想到可能是隔離級別不一樣。
果然，我是在Read committed的場景下復現，客戶是在Repeatable read的場景下復現。
?
那么，為什么兩個隔離級別會有不同的效果呢？
根本原因是RC隔離級別保證對讀取到的記錄加鎖 (記錄鎖)；
RR隔離級別保證對讀取到的記錄加鎖 (記錄鎖)，同時保證對讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (GAP鎖)，不存在幻讀現象。
具體是如何加鎖，可以直接看本文最后一個部分。
?
解決辦法：
1. 給（c2, c3）加唯一索引進行約束。（客戶的應用場景不支持，因為業務只有c2=? and c3=0的狀態只能出現一條，對于c3等于其他狀態值，可以允許多條記錄）
2. 將這個插入語句設置為 session級別的Repeatable read。這種方式對應用的改動最小。
?
如果設置為全局的Repeatable read隔離級別有什么問題？
1. 鎖等待的范圍擴大（增加了GAP鎖），可能更大概率的出現死鎖。
2. 在RR級別中，通過MVCC機制，雖然讓數據變得可重復讀，但讀到的數據可能是歷史數據，是不及時的數據，不是數據庫當前的數據！這在一些對于數據的時效特別敏感的業務中，就很可能出問題。
?
?
一些基本知識點：
兩段鎖
數據庫遵循的是兩段鎖協議，將事務分成兩個階段，加鎖階段和解鎖階段（所以叫兩段鎖）
- 加鎖階段：在該階段可以進行加鎖操作。在對任何數據進行讀操作之前要申請并獲得S鎖（共享鎖，其它事務可以繼續加共享鎖，但不能加排它鎖），在進行寫操作之前要申請并獲得X鎖（排它鎖，其它事務不能再獲得任何鎖）。加鎖不成功，則事務進入等待狀態，直到加鎖成功才繼續執行。
- 解鎖階段：當事務釋放了一個封鎖以后，事務進入解鎖階段，在該階段只能進行解鎖操作不能再進行加鎖操作。
| begin； | ? |
| insert into test ..... | 加insert對應的鎖 |
| update test set... | 加update對應的鎖 |
| delete from test .... | 加delete對應的鎖 |
| commit; | 事務提交時，同時釋放insert、update、delete對應的鎖 |
這種方式雖然無法避免死鎖，但是兩段鎖協議可以保證事務的并發調度是串行化（串行化很重要，尤其是在數據恢復和備份的時候）的。
?
事務的隔離級別
| 未提交讀（Read uncommitted） | 可能 | 可能 | 可能 |
| 已提交讀（Read committed） | 不可能 | 可能 | 可能 |
| 可重復讀（Repeatable read） | 不可能 | 不可能 | 可能 |
| 可串行化（Serializable ） | 不可能 | 不可能 | 不可能 |
- 未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據
- 提交讀(Read Committed)：只能讀取到已經提交的數據。針對當前讀，RC隔離級別保證對讀取到的記錄加鎖 (記錄鎖)，存在幻讀現象。
- 可重復讀(Repeated Read)：可重復讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。針對當前讀，RR隔離級別保證對讀取到的記錄加鎖 (記錄鎖)，同時保證對讀取的范圍加鎖，新的滿足查詢條件的記錄不能夠插入 (間隙鎖)，不存在幻讀現象。
- 串行讀(Serializable)：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。從MVCC并發控制退化為基于鎖的并發控制。不區別快照讀與當前讀，所有的讀操作均為當前讀，讀加讀鎖 (S鎖)，寫加寫鎖 (X鎖)。Serializable隔離級別下，讀寫沖突，因此并發度急劇下降，在MySQL/InnoDB下不建議使用。
Read Uncommitted這種級別，數據庫一般都不會用，而且任何操作都不會加鎖，這里就不討論了。
?
?
快照讀VS當前讀
在MVCC并發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。當前讀，讀取的是記錄的最新版本，并且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再并發修改這條記錄。
在一個支持MVCC并發控制的系統中，哪些讀操作是快照讀？哪些操作又是當前讀呢？以MySQL InnoDB為例：?
- 快照讀：簡單的select操作，屬于快照讀，不加鎖。
- select * from table where ?;?
- 當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，需要加鎖。
- 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 ?;
所有以上的語句，都屬于當前讀，讀取記錄的最新版本。并且，讀取之后，還需要保證其他并發事務不能修改當前記錄，對讀取記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共享鎖)外，其他的操作，都加的是X鎖 (排它鎖)。?
?
不同隔離級別，以及對于不同的索引情況會如何加鎖？
delete from t1 where id = 10;?
- 組合一：id列是主鍵，RC隔離級別：?只需要將主鍵上id = 10的記錄加上X鎖即可
- 組合二：id列是二級唯一索引，RC隔離級別：?需要加兩個X鎖，一個對應于id unique索引上的id = 10的記錄，另一把鎖對應于聚簇索引上的[name=’d’,id=10]的記錄。
- 組合三：id列是二級非唯一索引，RC隔離級別：對應的所有滿足SQL查詢條件的記錄，都會被加鎖。同時，這些記錄在主鍵索引上的記錄，也會被加鎖。
- 組合四：id列上沒有索引，RC隔離級別：SQL會走聚簇索引的全掃描進行過濾，由于過濾是由MySQL Server層面進行的。因此每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優化，對于不滿足條件的記錄，會在判斷后放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。
- 聚簇索引上所有的記錄，都被加上了X鎖。無論記錄是否滿足條件，全部被加上X鎖。這個鎖的效果和表鎖有什么區別？rc隔離級別下，有區別，記錄仍舊可以插入。rr下，功能上無區別。但是innodb不會主動升級表鎖。
- 為什么不是只在滿足條件的記錄上加鎖呢？這是由于MySQL的實現決定的。如果一個條件無法通過索引快速過濾，那么存儲引擎層面(innodb)就會將所有記錄加鎖后返回，然后由MySQL Server層進行過濾。因此也就把所有的記錄，都鎖上了。
- 在5.6后支持了Index Condition Pushdown， 可以在innodb層進行過濾。
- 組合五：id列是主鍵，RR隔離級別：加鎖與組合一[id主鍵，Read Committed]一致。
- 組合六：id列是二級唯一索引，RR隔離級別：?與組合二[id唯一索引，Read Committed]一致。
- 組合七：id列是二級非唯一索引，RR隔離級別：首先，通過id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖，然后返回；然后讀取下一條，重復進行。直至進行到第一條不滿足條件的記錄[11,f]，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結束。
- 組合八：id列上沒有索引，RR隔離級別：在Repeatable Read隔離級別下，如果進行全表掃描的當前讀，那么會鎖上表中的所有記錄，同時會鎖上聚簇索引內的所有GAP，杜絕所有的并發 更新/刪除/插入 操作。當然，也可以通過觸發semi-consistent read，來緩解加鎖開銷與并發影響，但是semi-consistent read本身也會帶來其他問題，不建議使用。
- 組合九：Serializable隔離級別：?在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是隔離級別相關的。Serializable隔離級別，讀不加鎖就不再成立，所有的讀操作，都是當前讀。
?
?
轉載于:https://www.cnblogs.com/yuyue2014/p/4747018.html
總結
以上是生活随笔為你收集整理的值班问题：insert语句插入了两条数据？的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 数的特殊特点
- 下一篇： Android 动态类加载实现免安装更新