MqSql的加锁分析
2019獨角獸企業重金招聘Python工程師標準>>>
（1）MVCC與基于鎖的并發控制
? ? ? ? ? ? ?MySql的InnoDB引擎，實現的是基于多版本的并發控制協議：MVCC。
? ? ? ? ? ? ?MVCC最大的好處，即讀不加鎖，讀寫不沖突。在讀多于寫的應用中，讀寫不沖突是非常重要的，極大的增強了系統的并發性能。這也就是為什么，幾乎所有的RDBMS都支持了MVCC。
? ? ? ? ? ? ?
（2）快照讀與當前讀
? ? ? ? ? 1.快照讀
? ? ? ? ? ? ? ? 簡單的select操作，屬于快照讀，不加鎖（當然如果是Serializable隔離級別還是要加鎖）
? ? ? ? ? ? ? ? 如：select * from table where ?
?
? ? ? ? ? 2.當前讀
? ? ? ? ? ? ? ? 包括特殊的讀操作，插入、更新、刪除操作，都屬于當前讀，需要加鎖
? ? ? ? ? ? ? ? 如：
? ? ? ? ? ? ? ? ? ? ?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鎖（排他鎖）
?
? ? ? ? ? 3.為何插入、更新、刪除操作都屬于當前讀？
? ? ? ? ? ? ? ? ?更新操作的執行流程如下：
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? 從圖中可以看到一個update操作的具體流程：
? ? ? ? ? ? ? 1.update sql發送到mysql后，mysql server會根據where條件，讀取第一條滿足條件的記錄，然后InnoDB引擎會將第一條記錄返回，并對記錄加鎖
? ? ? ? ? ? ? 2.mysql server收到這條加鎖的記錄后，會再發起一個update請求，更新這條記錄
? ? ? ? ? ? ? 3.這條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止?。
? ? ? ? ? ? ? 注意：針對一條當前讀的sql，InnoDB與mysql server的交互，是一條條進行的，因此加鎖也是一條條進行的。
? ? ? ? ? ? ? ? ? ? ? ? 先對一條滿足條件的記錄加鎖，返回給mysql server，做一些DML操作；然后再讀取下一條并加鎖，直至讀取完畢
?
（3）2PL：Two-Phase Locking
? ? ? ? ? ? ?傳統的RDBMS加鎖的一個原則就是2PL（二階段鎖）。
? ? ? ? ? ? ?簡單地說就是鎖操作分為2個階段：加鎖階段與解鎖階段，并且保證加鎖階段與解鎖階段不相交。
? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ?從上圖可以看出，2PL就是將加鎖、解鎖分為2個完全不相交的階段：加鎖階段：只加鎖不放鎖，解鎖階段：只放鎖不加鎖
?
（4）隔離級別與加鎖
? ? ? ? ? ?1.read uncommitted
? ? ? ? ? ? ? ?讀未提交，不會使用，忽略
? ? ? ? ? ?2.read committed（RC）
? ? ? ? ? ? ? ?快照讀不加鎖
? ? ? ? ? ? ? ?當前讀，RC隔離級別保證對讀取到的記錄加鎖，但存在幻讀情況
? ? ? ? ? ?3.repeatable read （RR）
? ? ? ? ? ? ? ?快照讀不加鎖
? ? ? ? ? ? ? ?當前讀，RR隔離級別保證對讀到的記錄加鎖，同時保證對讀取的范圍加鎖，因此新的滿足查詢條件的記錄不能夠插入（間隙鎖），因而不存在幻讀情況
? ? ? ? ? ?4.serializable
? ? ? ? ? ? ? ?從MVCC退化為基于鎖的并發控制。不區別快照讀和當前讀，所有的讀操作均為當前讀，讀加讀鎖（S鎖），寫加寫鎖（X鎖）
? ? ? ? ? ? ? ?serializable隔離級別下，讀寫沖突，因此并發度急劇下降，不建議使用。
?
（5）加鎖分析
? ? ? ? ? ? ?因為快照讀在RC、RR下都不加鎖，所以我們不討論快照讀，而只討論當前讀的情況
? ? ? ? ? ? ?接下來以delete from t1 where id=10這條sql為例，來分析加鎖的情況
? ? ? ? ? ??1.id主鍵+RC
? ? ? ? ? ? ? ? ?這個組合最為簡單。因為id為主鍵，where的條件為id=10，所以只需要在id=10的記錄上加X鎖即可
? ? ? ? ? ? ? ?
? ? ?
? ? ? ? ? ? 2.id唯一鍵+RC
? ? ? ? ? ? ? ? ?name為主鍵，id為唯一鍵，那么在RC隔離級別下，delete from t1 where id=10需要如何加鎖呢？
? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? 先對唯一索引樹上id=10的記錄加上X鎖，然后因為id=10對應的是name=d，那么從主鍵索引上查找到name=d的記錄，然后對這條記錄加X鎖。
?
? ? ? ? ? ? 3.id非唯一鍵索引+RC
? ? ? ? ? ? ?
? ? ? ? ? ? ?此時在id的索引樹上id=10的有2條記錄，所以先對這2條記錄加鎖。
? ? ? ? ? ? ?然后因為name是主鍵，所以在主鍵索引上查找到name=b和name=d的記錄，然后對這2條記錄加鎖
? ? ? ? ? ? 4.id無索引+RC
? ? ? ? ? ? ? ? ?因為id無索引，所以就只能全表掃描了，此時會對所有記錄加X鎖（但實際情況會優化）
? ? ? ? ? ? ? ? ?
?
? ? ? ? ? ? 5.id主鍵+RR
? ? ? ? ? ? ? ? ?id列是主鍵，RR隔離級別，這條sql與在RC級別下一致，都是對這條記錄加X鎖
? ? ? ? ? ? 6.id唯一鍵+RR
? ? ? ? ? ? ? ? ?與RC下一致，也是先對唯一索引樹上記錄加X鎖，然后對主鍵索引樹上對應記錄加X鎖
? ? ? ? ? ? 7.id非唯一鍵+RR
? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ?這與RC隔離級別下有很大差別，主要是多了一個gap鎖，而且gap鎖是加在記錄之間，而不是加在記錄之上。
? ? ? ? ? ? ? ?gap鎖有什么用呢？實際上這個gap鎖就是RR隔離級別，這是相對于RC隔離級別而言，不會出現幻讀的關鍵。
? ? ? ? ? ? ? ?gap鎖鎖住的位置，不是記錄本身，而是2條記錄之間的gap。所謂幻讀，就是在同一個事務中，連續做兩次當前讀，比如select * from t1 where id =10 for update，出現了2次記錄數量不一致的情況，就好像見到鬼了。
? ? ? ? ? ? ? ?那么如何保證兩次當前讀返回一致的記錄數呢？那就需要根據where條件確定記錄范圍，然后保證在這個范圍內，不會被其他事物插入新的、滿足條件的記錄。（舉個例子：假如條件為select * from table id>5 and id <10，而表中只有id=7，id=8兩條記錄滿足條件，如果有了gap鎖，那么就會將id=6的左邊和id=7的右邊邊界加上gap鎖，這樣如果其他事務要插入一條id=9的記錄，就需要等待了)這就是gap鎖的作用。
? ? ? ? ? ? ? ?? ? ? ? ? ? 7.id無索引+RR
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 這種情況必須要進行全表掃描，因而需要首先需要對所有記錄加X鎖，并且為了防止幻讀，對所有數據的間隙加上了gap鎖
? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?此時全表被鎖死，無法更新、刪除、插入
? ? ? ? ? ? ? ?? ? ? ? ? ? 8.serializable
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?讀寫全部加X鎖
?
?
轉載于:https://my.oschina.net/cpf2016/blog/726776
總結
以上是生活随笔為你收集整理的MqSql的加锁分析的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： HDU Problem 1272 小希的
- 下一篇： 数据库SQL及相关