mysql 閿

MySQL InnoDB 瀹炵幇鐨勬槸鍩轰簬澶氱増鏈殑骞跺彂鎺у埗鍗忚鈥斺擬VCC (Multi-Version Concurrency Control) (娉細涓嶮VCC鐩稿鐨勶紝鏄熀浜庨攣鐨勫苟鍙戞帶鍒讹紝Lock-Based Concurrency Control)銆侻VCC鐨勭壒鐐规槸璇荤殑鏃跺欎笉鍔犻攣锛屾墍浠ヨ鍐欎笉鍐茬獊锛屾墍浠ヤ細澧炲姞骞跺彂鑳藉姏锛屾墍浠ュぇ閮ㄥ垎鍏崇郴鍨嬫暟鎹簱閮芥敮鎸丮VCC銆

涓嶈繃涔熶笉鏄墍鏈夌殑璇婚兘涓嶅姞閿侊紝璇绘搷浣滃垎涓轰袱绉嶃
1. 蹇収璇(snapshot read) 绠鍗曠殑select鎿嶄綔锛屽睘浜庡揩鐓ц锛屼笉鍔犻攣
select * from table where
2. 褰撳墠璇(current read) 鐗规畩鐨勮鎿嶄綔锛屾彃鍏/鏇存柊/鍒犻櫎鎿嶄綔锛屽睘浜庡綋鍓嶈锛岄渶瑕佸姞閿併

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 ?;

闄や簡绗竴涓槸鍔燬閿 (鍏变韩閿)澶栵紝鍏朵粬鐨勬搷浣滐紝閮藉姞鐨勬槸X閿 (鎺掑畠閿)

浠庡浘涓紝鍙互鐪嬪埌锛屼竴涓猆pdate鎿嶄綔鐨勫叿浣撴祦绋嬨傚綋Update SQL琚彂缁橫ySQL鍚庯紝MySQL Server浼氭牴鎹畐here鏉′欢锛岃鍙栫涓鏉℃弧瓒虫潯浠剁殑璁板綍锛岀劧鍚嶪nnoDB寮曟搸浼氬皢绗竴鏉¤褰曡繑鍥烇紝骞跺姞閿 (current read)銆傚緟MySQL Server鏀跺埌杩欐潯鍔犻攣鐨勮褰曚箣鍚庯紝浼氬啀鍙戣捣涓涓猆pdate璇锋眰锛屾洿鏂拌繖鏉¤褰曘備竴鏉¤褰曟搷浣滃畬鎴愶紝鍐嶈鍙栦笅涓鏉¤褰曪紝鐩磋嚦娌℃湁婊¤冻鏉′欢鐨勮褰曚负姝€傚洜姝わ紝Update鎿嶄綔鍐呴儴锛屽氨鍖呭惈浜嗕竴涓綋鍓嶈銆傚悓鐞嗭紝Delete鎿嶄綔涔熶竴鏍枫侷nsert鎿嶄綔浼氱◢寰湁浜涗笉鍚岋紝绠鍗曟潵璇达紝灏辨槸Insert鎿嶄綔鍙兘浼氳Е鍙慤nique Key鐨勫啿绐佹鏌ワ紝涔熶細杩涜涓涓綋鍓嶈銆

娉細鏍规嵁涓婂浘鐨勪氦浜掞紝閽堝涓鏉″綋鍓嶈鐨凷QL璇彞锛孖nnoDB涓嶮ySQL Server鐨勪氦浜掞紝鏄竴鏉′竴鏉¤繘琛岀殑锛屽洜姝わ紝鍔犻攣涔熸槸涓鏉′竴鏉¤繘琛岀殑銆傚厛瀵逛竴鏉℃弧瓒虫潯浠剁殑璁板綍鍔犻攣锛岃繑鍥炵粰MySQL Server锛屽仛涓浜汥ML鎿嶄綔锛涚劧鍚庡湪璇诲彇涓嬩竴鏉″姞閿侊紝鐩磋嚦璇诲彇瀹屾瘯銆

鑱氱皣绱㈠紩 cluster index

InnoDB瀛樺偍寮曟搸鐨勬暟鎹粍缁囨柟寮忥紝鏄仛绨囩储寮曡〃锛氬畬鏁寸殑璁板綍锛屽瓨鍌ㄥ湪涓婚敭绱㈠紩涓紝閫氳繃涓婚敭绱㈠紩锛屽氨鍙互鑾峰彇璁板綍鎵鏈夌殑鍒椼

2pl: Two-Phase Locking

浼犵粺RDBMS鍔犻攣鐨勪竴涓師鍒欙紝灏辨槸2PL (浜岄樁娈甸攣)锛歍wo-Phase Locking銆傜浉瀵硅岃█锛2PL姣旇緝瀹规槗鐞嗚В锛岃鐨勬槸閿佹搷浣滃垎涓轰袱涓樁娈碉細鍔犻攣闃舵涓庤В閿侀樁娈碉紝骞朵笖淇濊瘉鍔犻攣闃舵涓庤В閿侀樁娈典笉鐩镐氦銆備笅闈紝浠嶆棫浠ySQL涓轰緥锛屾潵绠鍗曠湅鐪2PL鍦∕ySQL涓殑瀹炵幇銆

浠庝笂鍥惧彲浠ョ湅鍑猴紝2PL灏辨槸灏嗗姞閿/瑙i攣鍒嗕负涓や釜瀹屽叏涓嶇浉浜ょ殑闃舵銆傚姞閿侀樁娈碉細鍙姞閿侊紝涓嶆斁閿併傝В閿侀樁娈碉細鍙斁閿侊紝涓嶅姞閿併

闅旂绾у埆 lsolation level

Read Uncommited锛孯ead Committed锛孯epeatable Read锛孲erializable
– Read Uncommited
鍙互璇诲彇鏈彁浜よ褰曘傛闅旂绾у埆锛屼笉浼氫娇鐢紝蹇界暐銆
– Read Committed (RC)
蹇収璇诲拷鐣ワ紝鏈枃涓嶈冭檻銆
閽堝褰撳墠璇伙紝RC闅旂绾у埆淇濊瘉瀵硅鍙栧埌鐨勮褰曞姞閿 (璁板綍閿)锛屽瓨鍦ㄥ够璇荤幇璞°
– Repeatable Read (RR)
蹇収璇诲拷鐣ワ紝鏈枃涓嶈冭檻銆
閽堝褰撳墠璇伙紝RR闅旂绾у埆淇濊瘉瀵硅鍙栧埌鐨勮褰曞姞閿 (璁板綍閿)锛屽悓鏃朵繚璇佸璇诲彇鐨勮寖鍥村姞閿侊紝鏂扮殑婊¤冻鏌ヨ鏉′欢鐨勮褰曚笉鑳藉鎻掑叆 (闂撮殭閿)锛屼笉瀛樺湪骞昏鐜拌薄銆
– Serializable
浠嶮VCC骞跺彂鎺у埗閫鍖栦负鍩轰簬閿佺殑骞跺彂鎺у埗銆備笉鍖哄埆蹇収璇讳笌褰撳墠璇伙紝鎵鏈夌殑璇绘搷浣滃潎涓哄綋鍓嶈锛岃鍔犺閿 (S閿)锛屽啓鍔犲啓閿 (X閿)銆

Serializable闅旂绾у埆涓嬶紝璇诲啓鍐茬獊锛屽洜姝ゅ苟鍙戝害鎬ュ墽涓嬮檷锛屽湪MySQL/InnoDB涓嬩笉寤鸿浣跨敤銆

瀹炶返 涓鏉$畝鍗昐QL鐨勫姞閿佸疄鐜板垎鏋

SQL1锛歴elect * from t1 where id = 10;
SQL2锛歞elete from t1 where id = 10;

杩欎釜SQL鍔犱粈涔堥攣锛熷氨濡傚悓涓嬮潰涓ゆ潯绠鍗曠殑SQL锛屼粬浠姞浠涔堥攣锛
閽堝杩欎釜闂锛岃鎬庝箞鍥炵瓟锛熸垜鑳芥兂璞″埌鐨勪竴涓瓟妗堟槸锛

SQL1锛氫笉鍔犻攣銆傚洜涓篗ySQL鏄娇鐢ㄥ鐗堟湰骞跺彂鎺у埗鐨勶紝璇讳笉鍔犻攣銆
SQL2锛氬id = 10鐨勮褰曞姞鍐欓攣 (璧颁富閿储寮)銆

蹇呴』杩樿鐭ラ亾浠ヤ笅鐨勪竴浜涘墠鎻愶紝鍓嶆彁涓嶅悓锛岃兘缁欏嚭鐨勭瓟妗堜篃灏变笉鍚
鍓嶆彁涓锛歩d鍒楁槸涓嶆槸涓婚敭锛
鍓嶆彁浜岋細褰撳墠绯荤粺鐨勯殧绂荤骇鍒槸浠涔堬紵
鍓嶆彁涓夛細id鍒楀鏋滀笉鏄富閿紝閭d箞id鍒椾笂鏈夌储寮曞悧锛
鍓嶆彁鍥涳細id鍒椾笂濡傛灉鏈変簩绾х储寮曪紝閭d箞杩欎釜绱㈠紩鏄敮涓绱㈠紩鍚楋紵
鍓嶆彁浜旓細涓や釜SQL鐨勬墽琛岃鍒掓槸浠涔堬紵绱㈠紩鎵弿锛熷叏琛ㄦ壂鎻忥紵

涓嬮潰鐨勮繖浜涚粍鍚堬紝鎴戝仛浜嗕竴涓墠鎻愬亣璁撅紝涔熷氨鏄湁绱㈠紩鏃讹紝鎵ц璁″垝涓瀹氫細閫夋嫨浣跨敤绱㈠紩杩涜杩囨护 (绱㈠紩鎵弿)銆備絾瀹為檯鎯呭喌浼氬鏉傚緢澶氾紝鐪熸鐨勬墽琛岃鍒掞紝杩樻槸闇瑕佹牴鎹甅ySQL杈撳嚭鐨勪负鍑嗐

缁勫悎涓锛歩d鍒楁槸涓婚敭锛孯C闅旂绾у埆

鍙渶瑕佸皢涓婚敭涓婏紝id = 10鐨勮褰曞姞涓奨閿佸嵆鍙

缁勫悎浜岋細id鍒楁槸浜岀骇鍞竴绱㈠紩锛孯C闅旂绾у埆

姝ょ粍鍚堜腑锛宨d鏄痷nique绱㈠紩锛岃屼富閿槸name鍒椼傛鏃讹紝鍔犻攣鐨勬儏鍐电敱浜庣粍鍚堜竴鏈夋墍涓嶅悓銆傜敱浜巌d鏄痷nique绱㈠紩锛屽洜姝elete璇彞浼氶夋嫨璧癷d鍒楃殑绱㈠紩杩涜where鏉′欢鐨勮繃婊わ紝鍦ㄦ壘鍒癷d=10鐨勮褰曞悗锛岄鍏堜細灏唘nique绱㈠紩涓婄殑id=10绱㈠紩璁板綍鍔犱笂X閿侊紝鍚屾椂锛屼細鏍规嵁璇诲彇鍒扮殑name鍒楋紝鍥炰富閿储寮(鑱氱皣绱㈠紩)锛岀劧鍚庡皢鑱氱皣绱㈠紩涓婄殑name = 鈥榙鈥 瀵瑰簲鐨勪富閿储寮曢」鍔燲閿併備负浠涔堣仛绨囩储寮曚笂鐨勮褰曚篃瑕佸姞閿侊紵璇曟兂涓涓嬶紝濡傛灉骞跺彂鐨勪竴涓猄QL锛屾槸閫氳繃涓婚敭绱㈠紩鏉ユ洿鏂帮細update t1 set id = 100 where name = 鈥榙鈥; 姝ゆ椂锛屽鏋渄elete璇彞娌℃湁灏嗕富閿储寮曚笂鐨勮褰曞姞閿侊紝閭d箞骞跺彂鐨剈pdate灏变細鎰熺煡涓嶅埌delete璇彞鐨勫瓨鍦紝杩濊儗浜嗗悓涓璁板綍涓婄殑鏇存柊/鍒犻櫎闇瑕佷覆琛屾墽琛岀殑绾︽潫銆

缁撹锛氳嫢id鍒楁槸unique鍒楋紝鍏朵笂鏈塽nique绱㈠紩銆傞偅涔圫QL闇瑕佸姞涓や釜X閿侊紝涓涓搴斾簬id unique绱㈠紩涓婄殑id = 10鐨勮褰曪紝鍙︿竴鎶婇攣瀵瑰簲浜庤仛绨囩储寮曚笂鐨刐name=’d’,id=10]鐨勮褰曘

缁勫悎涓夛細id鍒楁槸浜岀骇闈炲敮涓绱㈠紩锛孯C闅旂绾у埆

鏍规嵁姝ゅ浘锛屽彲浠ョ湅鍒帮紝棣栧厛锛宨d鍒楃储寮曚笂锛屾弧瓒砳d = 10鏌ヨ鏉′欢鐨勮褰曪紝鍧囧凡鍔犻攣銆傚悓鏃讹紝杩欎簺璁板綍瀵瑰簲鐨勪富閿储寮曚笂鐨勮褰曚篃閮藉姞涓婁簡閿併備笌缁勫悎浜屽敮涓鐨勫尯鍒湪浜庯紝缁勫悎浜屾渶澶氬彧鏈変竴涓弧瓒崇瓑鍊兼煡璇㈢殑璁板綍锛岃岀粍鍚堜笁浼氬皢鎵鏈夋弧瓒虫煡璇㈡潯浠剁殑璁板綍閮藉姞閿併

缁撹锛氳嫢id鍒椾笂鏈夐潪鍞竴绱㈠紩锛岄偅涔堝搴旂殑鎵鏈夋弧瓒砈QL鏌ヨ鏉′欢鐨勮褰曪紝閮戒細琚姞閿併傚悓鏃讹紝杩欎簺璁板綍鍦ㄤ富閿储寮曚笂鐨勮褰曪紝涔熶細琚姞閿併

缁勫悎鍥涳細id鍒椾笂娌℃湁绱㈠紩锛孯C闅旂绾у埆

鐢变簬id鍒椾笂娌℃湁绱㈠紩锛屽洜姝ゅ彧鑳借蛋鑱氱皣绱㈠紩锛岃繘琛屽叏閮ㄦ壂鎻忋備粠鍥句腑鍙互鐪嬪埌锛屾弧瓒冲垹闄ゆ潯浠剁殑璁板綍鏈変袱鏉★紝浣嗘槸锛岃仛绨囩储寮曚笂鎵鏈夌殑璁板綍锛岄兘琚姞涓婁簡X閿併傛棤璁鸿褰曟槸鍚︽弧瓒虫潯浠讹紝鍏ㄩ儴琚姞涓奨閿併傛棦涓嶆槸鍔犺〃閿侊紝涔熶笉鏄湪婊¤冻鏉′欢鐨勮褰曚笂鍔犺閿併

鏈変汉鍙兘浼氶棶锛熶负浠涔堜笉鏄彧鍦ㄦ弧瓒虫潯浠剁殑璁板綍涓婂姞閿佸憿锛熻繖鏄敱浜嶮ySQL鐨勫疄鐜板喅瀹氱殑銆傚鏋滀竴涓潯浠舵棤娉曢氳繃绱㈠紩蹇熻繃婊わ紝閭d箞瀛樺偍寮曟搸灞傞潰灏变細灏嗘墍鏈夎褰曞姞閿佸悗杩斿洖锛岀劧鍚庣敱MySQL Server灞傝繘琛岃繃婊ゃ傚洜姝や篃灏辨妸鎵鏈夌殑璁板綍锛岄兘閿佷笂浜嗐

娉細鍦ㄥ疄闄呯殑瀹炵幇涓紝MySQL鏈変竴浜涙敼杩涳紝鍦∕ySQL Server杩囨护鏉′欢锛屽彂鐜颁笉婊¤冻鍚庯紝浼氳皟鐢╱nlock_row鏂规硶锛屾妸涓嶆弧瓒虫潯浠剁殑璁板綍鏀鹃攣 (杩濊儗浜2PL鐨勭害鏉)銆傝繖鏍峰仛锛屼繚璇佷簡鏈鍚庡彧浼氭寔鏈夋弧瓒虫潯浠惰褰曚笂鐨勯攣锛屼絾鏄瘡鏉¤褰曠殑鍔犻攣鎿嶄綔杩樻槸涓嶈兘鐪佺暐鐨勩

缁撹锛氳嫢id鍒椾笂娌℃湁绱㈠紩锛孲QL浼氳蛋鑱氱皣绱㈠紩鐨勫叏鎵弿杩涜杩囨护锛岀敱浜庤繃婊ゆ槸鐢盡ySQL Server灞傞潰杩涜鐨勩傚洜姝ゆ瘡鏉¤褰曪紝鏃犺鏄惁婊¤冻鏉′欢锛岄兘浼氳鍔犱笂X閿併備絾鏄紝涓轰簡鏁堢巼鑰冮噺锛孧ySQL鍋氫簡浼樺寲锛屽浜庝笉婊¤冻鏉′欢鐨勮褰曪紝浼氬湪鍒ゆ柇鍚庢斁閿侊紝鏈缁堟寔鏈夌殑锛屾槸婊¤冻鏉′欢鐨勮褰曚笂鐨勯攣锛屼絾鏄笉婊¤冻鏉′欢鐨勮褰曚笂鐨勫姞閿/鏀鹃攣鍔ㄤ綔涓嶄細鐪佺暐銆傚悓鏃讹紝浼樺寲涔熻繚鑳屼簡2PL鐨勭害鏉熴

缁勫悎浜旓細id鍒楁槸涓婚敭锛孯R闅旂绾у埆

鍔犻攣涓嶳C涓鑷

缁勫悎鍏細id鍒楁槸浜岀骇鍞竴绱㈠紩锛孯R闅旂绾у埆

鍔犻攣涓嶳C涓鑷

缁勫悎涓冿細id鍒楁槸浜岀骇闈炲敮涓绱㈠紩锛孯R闅旂绾у埆

杩樿寰楀墠闈㈡彁鍒扮殑MySQL鐨勫洓绉嶉殧绂荤骇鍒殑鍖哄埆鍚楋紵RC闅旂绾у埆鍏佽骞昏锛岃孯R闅旂绾у埆锛屼笉鍏佽瀛樺湪骞昏銆備絾鏄湪缁勫悎浜斻佺粍鍚堝叚涓紝鍔犻攣琛屼负鍙堟槸涓嶳C涓嬬殑鍔犻攣琛屼负瀹屽叏涓鑷淬傞偅涔圧R闅旂绾у埆涓嬶紝濡備綍闃叉骞昏鍛紵闂鐨勭瓟妗堬紝灏卞湪缁勫悎涓冧腑鎻檽銆

缁勫悎涓冿紝Repeatable Read闅旂绾у埆锛宨d涓婃湁涓涓潪鍞竴绱㈠紩锛屾墽琛宒elete from t1 where id = 10; 鍋囪閫夋嫨id鍒椾笂鐨勭储寮曡繘琛屾潯浠惰繃婊わ紝鏈鍚庣殑鍔犻攣琛屼负锛屾槸鎬庝箞鏍风殑鍛紵鍚屾牱鐪嬩笅闈㈣繖骞呭浘锛

姝ゅ浘锛岀浉瀵逛簬缁勫悎涓夛細[id鍒椾笂闈炲敮涓閿侊紝Read Committed]鐪嬩技鐩稿悓锛屽叾瀹炲嵈鏈夊緢澶х殑鍖哄埆銆傛渶澶х殑鍖哄埆鍦ㄤ簬锛岃繖骞呭浘涓浜嗕竴涓狦AP閿侊紝鑰屼笖GAP閿佺湅璧锋潵涔熶笉鏄姞鍦ㄨ褰曚笂鐨勶紝鍊掑儚鏄姞杞戒袱鏉¤褰曚箣闂寸殑浣嶇疆锛孏AP閿佹湁浣曠敤锛

鍏跺疄杩欎釜澶氬嚭鏉ョ殑GAP閿侊紝灏辨槸RR闅旂绾у埆锛岀浉瀵逛簬RC闅旂绾у埆锛屼笉浼氬嚭鐜板够璇荤殑鍏抽敭銆傜‘瀹烇紝GAP閿侀攣浣忕殑浣嶇疆锛屼篃涓嶆槸璁板綍鏈韩锛岃屾槸涓ゆ潯璁板綍涔嬮棿鐨凣AP銆傛墍璋撳够璇伙紝灏辨槸鍚屼竴涓簨鍔★紝杩炵画鍋氫袱娆″綋鍓嶈 (渚嬪锛歴elect * from t1 where id = 10 for update;)锛岄偅涔堣繖涓ゆ褰撳墠璇昏繑鍥炵殑鏄畬鍏ㄧ浉鍚岀殑璁板綍 (璁板綍鏁伴噺涓鑷达紝璁板綍鏈韩涔熶竴鑷)锛岀浜屾鐨勫綋鍓嶈锛屼笉浼氭瘮绗竴娆¤繑鍥炴洿澶氱殑璁板綍 (骞昏薄)銆

濡備綍淇濊瘉涓ゆ褰撳墠璇昏繑鍥炰竴鑷寸殑璁板綍锛岄偅灏遍渶瑕佸湪绗竴娆″綋鍓嶈涓庣浜屾褰撳墠璇讳箣闂达紝鍏朵粬鐨勪簨鍔′笉浼氭彃鍏ユ柊鐨勬弧瓒虫潯浠剁殑璁板綍骞舵彁浜ゃ備负浜嗗疄鐜拌繖涓姛鑳斤紝GAP閿佸簲杩愯岀敓銆

濡傚浘涓墍绀猴紝鏈夊摢浜涗綅缃彲浠ユ彃鍏ユ柊鐨勬弧瓒虫潯浠剁殑椤 (id = 10)锛岃冭檻鍒癇+鏍戠储寮曠殑鏈夊簭鎬э紝婊¤冻鏉′欢鐨勯」涓瀹氭槸杩炵画瀛樻斁鐨勩傝褰昜6,c]涔嬪墠锛屼笉浼氭彃鍏d=10鐨勮褰曪紱[6,c]涓嶽10,b]闂村彲浠ユ彃鍏10, aa]锛沎10,b]涓嶽10,d]闂达紝鍙互鎻掑叆鏂扮殑[10,bb],[10,c]绛夛紱[10,d]涓嶽11,f]闂村彲浠ユ彃鍏ユ弧瓒虫潯浠剁殑[10,e],[10,z]绛夛紱鑰孾11,f]涔嬪悗涔熶笉浼氭彃鍏ユ弧瓒虫潯浠剁殑璁板綍銆傚洜姝わ紝涓轰簡淇濊瘉[6,c]涓嶽10,b]闂达紝[10,b]涓嶽10,d]闂达紝[10,d]涓嶽11,f]涓嶄細鎻掑叆鏂扮殑婊¤冻鏉′欢鐨勮褰曪紝MySQL閫夋嫨浜嗙敤GAP閿侊紝灏嗚繖涓変釜GAP缁欓攣璧锋潵銆

Insert鎿嶄綔锛屽insert [10,aa]锛岄鍏堜細瀹氫綅鍒癧6,c]涓嶽10,b]闂达紝鐒跺悗鍦ㄦ彃鍏ュ墠锛屼細妫鏌ヨ繖涓狦AP鏄惁宸茬粡琚攣涓婏紝濡傛灉琚攣涓婏紝鍒橧nsert涓嶈兘鎻掑叆璁板綍銆傚洜姝わ紝閫氳繃绗竴閬嶇殑褰撳墠璇伙紝涓嶄粎灏嗘弧瓒虫潯浠剁殑璁板綍閿佷笂 (X閿)锛屼笌缁勫悎涓夌被浼笺傚悓鏃惰繕鏄鍔3鎶奊AP閿侊紝灏嗗彲鑳芥彃鍏ユ弧瓒虫潯浠惰褰曠殑3涓狦AP缁欓攣涓婏紝淇濊瘉鍚庣画鐨処nsert涓嶈兘鎻掑叆鏂扮殑id=10鐨勮褰曪紝涔熷氨鏉滅粷浜嗗悓涓浜嬪姟鐨勭浜屾褰撳墠璇伙紝鍑虹幇骞昏薄鐨勬儏鍐点

GAP閿佺殑鐩殑锛屾槸涓轰簡闃叉鍚屼竴浜嬪姟鐨勪袱娆″綋鍓嶈锛屽嚭鐜板够璇荤殑鎯呭喌銆傝岀粍鍚堜簲锛宨d鏄富閿紱缁勫悎鍏紝id鏄痷nique閿紝閮借兘澶熶繚璇佸敮涓鎬с備竴涓瓑鍊兼煡璇紝鏈澶氬彧鑳借繑鍥炰竴鏉¤褰曪紝鑰屼笖鏂扮殑鐩稿悓鍙栧肩殑璁板綍锛屼竴瀹氫笉浼氬湪鏂版彃鍏ヨ繘鏉ワ紝鍥犳涔熷氨閬垮厤浜咷AP閿佺殑浣跨敤銆傚叾瀹烇紝閽堝姝ら棶棰橈紝杩樻湁涓涓洿娣卞叆鐨勯棶棰橈細濡傛灉缁勫悎浜斻佺粍鍚堝叚涓嬶紝閽堝SQL锛歴elect * from t1 where id = 10 for update; 绗竴娆℃煡璇紝娌℃湁鎵惧埌婊¤冻鏌ヨ鏉′欢鐨勮褰曪紝閭d箞GAP閿佹槸鍚﹁繕鑳藉鐪佺暐锛熸闂鐣欑粰澶у鎬濊冦

缁撹锛歊epeatable Read闅旂绾у埆涓嬶紝id鍒椾笂鏈変竴涓潪鍞竴绱㈠紩锛屽搴擲QL锛歞elete from t1 where id = 10; 棣栧厛锛岄氳繃id绱㈠紩瀹氫綅鍒扮涓鏉℃弧瓒虫煡璇㈡潯浠剁殑璁板綍锛屽姞璁板綍涓婄殑X閿侊紝鍔燝AP涓婄殑GAP閿侊紝鐒跺悗鍔犱富閿仛绨囩储寮曚笂鐨勮褰昘閿侊紝鐒跺悗杩斿洖锛涚劧鍚庤鍙栦笅涓鏉★紝閲嶅杩涜銆傜洿鑷宠繘琛屽埌绗竴鏉′笉婊¤冻鏉′欢鐨勮褰昜11,f]锛屾鏃讹紝涓嶉渶瑕佸姞璁板綍X閿侊紝浣嗘槸浠嶆棫闇瑕佸姞GAP閿侊紝鏈鍚庤繑鍥炵粨鏉熴

缁勫悎鍏細id鍒椾笂娌℃湁绱㈠紩锛孯R闅旂绾у埆

濡傚浘锛岃繖鏄竴涓緢鎭愭栫殑鐜拌薄銆傞鍏堬紝鑱氱皣绱㈠紩涓婄殑鎵鏈夎褰曪紝閮借鍔犱笂浜哫閿併傚叾娆★紝鑱氱皣绱㈠紩姣忔潯璁板綍闂寸殑闂撮殭(GAP)锛屼篃鍚屾椂琚姞涓婁簡GAP閿併傝繖涓ず渚嬭〃锛屽彧鏈6鏉¤褰曪紝涓鍏遍渶瑕6涓褰曢攣锛7涓狦AP閿併傝瘯鎯筹紝濡傛灉琛ㄤ笂鏈1000涓囨潯璁板綍鍛紵

鍦ㄨ繖绉嶆儏鍐典笅锛岃繖涓〃涓婏紝闄や簡涓嶅姞閿佺殑蹇収搴︼紝鍏朵粬浠讳綍鍔犻攣鐨勫苟鍙慡QL锛屽潎涓嶈兘鎵ц锛屼笉鑳芥洿鏂帮紝涓嶈兘鍒犻櫎锛屼笉鑳芥彃鍏ワ紝鍏ㄨ〃琚攣姝汇

褰撶劧锛岃窡缁勫悎鍥涳細[id鏃犵储寮, Read Committed]绫讳技锛岃繖涓儏鍐典笅锛孧ySQL涔熷仛浜嗕竴浜涗紭鍖栵紝灏辨槸鎵璋撶殑semi-consistent read銆俿emi-consistent read寮鍚殑鎯呭喌涓嬶紝瀵逛簬涓嶆弧瓒虫煡璇㈡潯浠剁殑璁板綍锛孧ySQL浼氭彁鍓嶆斁閿併傞拡瀵逛笂闈㈢殑杩欎釜鐢ㄤ緥锛屽氨鏄櫎浜嗚褰昜d,10]锛孾g,10]涔嬪锛屾墍鏈夌殑璁板綍閿侀兘浼氳閲婃斁锛屽悓鏃朵笉鍔燝AP閿併俿emi-consistent read濡備綍瑙﹀彂锛氳涔堟槸read committed闅旂绾у埆锛涜涔堟槸Repeatable Read闅旂绾у埆锛屽悓鏃惰缃簡innodb_locks_unsafe_for_binlog 鍙傛暟銆

缁撹锛氬湪Repeatable Read闅旂绾у埆涓嬶紝濡傛灉杩涜鍏ㄨ〃鎵弿鐨勫綋鍓嶈锛岄偅涔堜細閿佷笂琛ㄤ腑鐨勬墍鏈夎褰曪紝鍚屾椂浼氶攣涓婅仛绨囩储寮曞唴鐨勬墍鏈塆AP锛屾潨缁濇墍鏈夌殑骞跺彂 鏇存柊/鍒犻櫎/鎻掑叆 鎿嶄綔銆傚綋鐒讹紝涔熷彲浠ラ氳繃瑙﹀彂semi-consistent read锛屾潵缂撹В鍔犻攣寮閿涓庡苟鍙戝奖鍝嶏紝浣嗘槸semi-consistent read鏈韩涔熶細甯︽潵鍏朵粬闂锛屼笉寤鸿浣跨敤銆

缁勫悎涔濓細Serializable闅旂绾у埆

閽堝鍓嶉潰鎻愬埌鐨勭畝鍗曠殑SQL锛屾渶鍚庝竴涓儏鍐碉細Serializable闅旂绾у埆銆傚浜嶴QL2锛歞elete from t1 where id = 10; 鏉ヨ锛孲erializable闅旂绾у埆涓嶳epeatable Read闅旂绾у埆瀹屽叏涓鑷达紝鍥犳涓嶅仛浠嬬粛銆

Serializable闅旂绾у埆锛屽奖鍝嶇殑鏄疭QL1锛歴elect * from t1 where id = 10; 杩欐潯SQL锛屽湪RC锛孯R闅旂绾у埆涓嬶紝閮芥槸蹇収璇伙紝涓嶅姞閿併備絾鏄湪Serializable闅旂绾у埆锛孲QL1浼氬姞璇婚攣锛屼篃灏辨槸璇村揩鐓ц涓嶅瀛樺湪锛孧VCC骞跺彂鎺у埗闄嶇骇涓篖ock-Based CC銆

缁撹锛氬湪MySQL/InnoDB涓紝鎵璋撶殑璇讳笉鍔犻攣锛屽苟涓嶉傜敤浜庢墍鏈夌殑鎯呭喌锛岃屾槸闅旂绾у埆鐩稿叧鐨勩係erializable闅旂绾у埆锛岃涓嶅姞閿佸氨涓嶅啀鎴愮珛锛屾墍鏈夌殑璇绘搷浣滐紝閮芥槸褰撳墠璇汇

涓鏉″鏉傜殑SQL

Index key锛歱ubtime > 1 and puptime < 20銆傛鏉′欢锛岀敤浜庣‘瀹歋QL鍦╥dx_t1_pu绱㈠紩涓婄殑鏌ヨ鑼冨洿銆

Index Filter锛歶serid = 鈥榟dc鈥 銆傛鏉′欢锛屽彲浠ュ湪idx_t1_pu绱㈠紩涓婅繘琛岃繃婊わ紝浣嗕笉灞炰簬Index Key銆
Table Filter锛歝omment is not NULL銆傛鏉′欢锛屽湪idx_t1_pu绱㈠紩涓婃棤娉曡繃婊わ紝鍙兘鍦ㄨ仛绨囩储寮曚笂杩囨护銆

浠庡浘涓彲浠ョ湅鍑猴紝鍦≧epeatable Read闅旂绾у埆涓嬶紝鐢盜ndex Key鎵纭畾鐨勮寖鍥达紝琚姞涓婁簡GAP閿侊紱Index Filter閿佺粰瀹氱殑鏉′欢 (userid = 鈥榟dc鈥)浣曟椂杩囨护锛岃MySQL鐨勭増鏈屽畾锛屽湪MySQL 5.6鐗堟湰涔嬪墠锛屼笉鏀寔Index Condition Pushdown(ICP)锛屽洜姝ndex Filter鍦∕ySQL Server灞傝繃婊わ紝鍦5.6鍚庢敮鎸佷簡Index Condition Pushdown锛屽垯鍦╥ndex涓婅繃婊ゃ傝嫢涓嶆敮鎸両CP锛屼笉婊¤冻Index Filter鐨勮褰曪紝涔熼渶瑕佸姞涓婅褰昘閿侊紝鑻ユ敮鎸両CP锛屽垯涓嶆弧瓒矷ndex Filter鐨勮褰曪紝鏃犻渶鍔犺褰昘閿 (鍥句腑锛岀敤绾㈣壊绠ご鏍囧嚭鐨刋閿侊紝鏄惁瑕佸姞锛岃鏄惁鏀寔ICP鑰屽畾)锛涜孴able Filter瀵瑰簲鐨勮繃婊ゆ潯浠讹紝鍒欏湪鑱氱皣绱㈠紩涓鍙栧悗锛屽湪MySQL Server灞傞潰杩囨护锛屽洜姝よ仛绨囩储寮曚笂涔熼渶瑕乆閿併傛渶鍚庯紝閫夊彇鍑轰簡涓鏉℃弧瓒虫潯浠剁殑璁板綍[8,hdc,d,5,good]锛屼絾鏄姞閿佺殑鏁伴噺锛岃杩滆繙澶т簬婊¤冻鏉′欢鐨勮褰曟暟閲忋

缁撹锛氬湪Repeatable Read闅旂绾у埆涓嬶紝閽堝涓涓鏉傜殑SQL锛岄鍏堥渶瑕佹彁鍙栧叾where鏉′欢銆侷ndex Key纭畾鐨勮寖鍥达紝闇瑕佸姞涓奊AP閿侊紱Index Filter杩囨护鏉′欢锛岃MySQL鐗堟湰鏄惁鏀寔ICP锛岃嫢鏀寔ICP锛屽垯涓嶆弧瓒矷ndex Filter鐨勮褰曪紝涓嶅姞X閿侊紝鍚﹀垯闇瑕乆閿侊紱Table Filter杩囨护鏉′欢锛屾棤璁烘槸鍚︽弧瓒筹紝閮介渶瑕佸姞X閿併

浜嬪姟鐨勬蹇
浜嬪姟鐨勬蹇垫潵鑷簬涓や釜鐙珛鐨勯渶姹傦細骞跺彂鏁版嵁搴撹闂紝绯荤粺閿欒鎭㈠銆

涓涓簨鍔℃槸鍙互琚湅浣滀竴涓崟鍏冪殑涓绯诲垪SQL璇彞鐨勯泦鍚堛

浜嬪姟鐨勭壒鎬э紙ACID锛
A, atomacity 鍘熷瓙鎬т簨鍔″繀椤绘槸鍘熷瓙宸ヤ綔鍗曞厓锛涘浜庡叾鏁版嵁淇敼锛岃涔堝叏閮芥墽琛岋紝瑕佷箞鍏ㄩ兘涓嶆墽琛屻傞氬父锛屼笌鏌愪釜浜嬪姟鍏宠仈鐨勬搷浣滃叿鏈夊叡鍚岀殑鐩爣锛屽苟涓旀槸鐩镐簰渚濊禆鐨勩傚鏋滅郴缁熷彧鎵ц杩欎簺鎿嶄綔鐨勪竴涓瓙闆嗭紝鍒欏彲鑳戒細鐮村潖浜嬪姟鐨勬讳綋鐩爣銆傚師瀛愭ф秷闄や簡绯荤粺澶勭悊鎿嶄綔瀛愰泦鐨勫彲鑳芥с

C, consistency 涓鑷存

浜嬪姟灏嗘暟鎹簱浠庝竴绉嶄竴鑷寸姸鎬佽浆鍙樹负涓嬩竴绉嶄竴鑷寸姸鎬併備篃灏辨槸璇达紝浜嬪姟鍦ㄥ畬鎴愭椂锛屽繀椤讳娇鎵鏈夌殑鏁版嵁閮戒繚鎸佷竴鑷寸姸鎬侊紙鍚勭 constraint 涓嶈鐮村潖锛夈

I, isolation 闅旂鎬х敱骞跺彂浜嬪姟鎵浣滅殑淇敼蹇呴』涓庝换浣曞叾瀹冨苟鍙戜簨鍔℃墍浣滅殑淇敼闅旂銆備簨鍔℃煡鐪嬫暟鎹椂鏁版嵁鎵澶勭殑鐘舵侊紝瑕佷箞鏄彟涓骞跺彂浜嬪姟淇敼瀹冧箣鍓嶇殑鐘舵侊紝瑕佷箞鏄彟涓浜嬪姟淇敼瀹冧箣鍚庣殑鐘舵侊紝浜嬪姟涓嶄細鏌ョ湅涓棿鐘舵佺殑鏁版嵁銆傛崲鍙ヨ瘽璇达紝涓涓簨鍔$殑褰卞搷鍦ㄨ浜嬪姟鎻愪氦鍓嶅鍏朵粬浜嬪姟閮戒笉鍙銆

D, 鎸佷箙鎬

浜嬪姟瀹屾垚涔嬪悗锛屽畠瀵逛簬绯荤粺鐨勫奖鍝嶆槸姘镐箙鎬х殑銆傝淇敼鍗充娇鍑虹幇鑷村懡鐨勭郴缁熸晠闅滀篃灏嗕竴鐩翠繚鎸併

浜嬪姟鐨勯殧绂荤骇鍒
濡傛灉涓嶅鏁版嵁搴撹繘琛屽苟鍙戞帶鍒讹紝鍙兘浼氫骇鐢熷紓甯告儏鍐碉細

鑴忚(Dirty Read)

褰撲竴涓簨鍔¤鍙栧彟涓涓簨鍔″皻鏈彁浜ょ殑淇敼鏃讹紝浜х敓鑴忚銆

鍚屼竴浜嬪姟鍐呬笉鏄剰璇汇備竴涓簨鍔″紑濮嬭鍙栦簡鏌愯鏁版嵁锛屼絾鏄彟澶栦竴涓簨鍔″凡缁忔洿鏂颁簡姝ゆ暟鎹絾娌℃湁鑳藉鍙婃椂鎻愪氦銆傝繖鏄浉褰撳嵄闄╃殑锛屽洜涓哄緢鍙兘鎵鏈夌殑鎿嶄綔閮借鍥炴粴锛屼篃灏辨槸璇磋鍙栧嚭鐨勬暟鎹叾瀹炴槸閿欒鐨勩

闈為噸澶嶈(Nonrepeatable Read)涓涓簨鍔″鍚屼竴琛屾暟鎹噸澶嶈鍙栦袱娆★紝浣嗘槸鍗村緱鍒颁簡涓嶅悓鐨勭粨鏋溿傚悓涓鏌ヨ鍦ㄥ悓涓浜嬪姟涓娆¤繘琛岋紝鐢变簬鍏朵粬鎻愪氦浜嬪姟鎵鍋氱殑淇敼鎴栧垹闄わ紝姣忔杩斿洖涓嶅悓鐨勭粨鏋滈泦锛屾鏃跺彂鐢熼潪閲嶅璇汇

骞诲儚璇(Phantom Reads)浜嬪姟鍦ㄦ搷浣滆繃绋嬩腑杩涜涓ゆ鏌ヨ锛岀浜屾鏌ヨ鐨勭粨鏋滃寘鍚簡绗竴娆℃煡璇腑鏈嚭鐜扮殑鏁版嵁锛堣繖閲屽苟涓嶈姹備袱娆℃煡璇㈢殑SQL璇彞鐩稿悓锛夈傝繖鏄洜涓哄湪涓ゆ鏌ヨ杩囩▼涓湁鍙﹀涓涓簨鍔℃彃鍏ユ暟鎹犳垚鐨勩

褰撳鏌愯鎵ц鎻掑叆鎴栧垹闄ゆ搷浣滐紝鑰岃琛屽睘浜庢煇涓簨鍔℃鍦ㄨ鍙栫殑琛岀殑鑼冨洿鏃讹紝浼氬彂鐢熷够鍍忚闂銆

涓㈠け淇敼(Lost Update)

绗竴绫伙細褰撲袱涓簨鍔℃洿鏂扮浉鍚岀殑鏁版嵁婧愶紝濡傛灉绗竴涓簨鍔¤鎻愪氦锛岀浜屼釜鍗磋鎾ら攢锛岄偅涔堣繛鍚岀涓涓簨鍔″仛鐨勬洿鏂颁篃琚挙閿銆

绗簩绫伙細鏈変袱涓苟鍙戜簨鍔″悓鏃惰鍙栧悓涓琛屾暟鎹紝鐒跺悗鍏朵腑涓涓瀹冭繘琛屼慨鏀规彁浜わ紝鑰屽彟涓涓篃杩涜浜嗕慨鏀规彁浜ゃ傝繖灏变細閫犳垚绗竴娆″啓鎿嶄綔澶辨晥銆

涓轰簡鍏奸【骞跺彂鏁堢巼鍜屽紓甯告帶鍒讹紝鍦ㄦ爣鍑哠QL瑙勮寖涓紝瀹氫箟浜4涓簨鍔¢殧绂荤骇鍒紝锛 Oracle 鍜 SQL Server 瀵规爣鍑嗛殧绂荤骇鍒湁涓嶅悓鐨勫疄鐜 锛

鏈彁浜よ(Read Uncommitted)

鐩磋瘧灏辨槸鈥濊鏈彁浜も濓紝鎰忔濆氨鏄嵆浣夸竴涓洿鏂拌鍙ユ病鏈夋彁浜わ紝浣嗘槸鍒殑浜嬪姟鍙互璇诲埌杩欎釜鏀瑰彉銆

Read Uncommitted鍏佽鑴忚銆

宸叉彁浜よ(Read Committed)

鐩磋瘧灏辨槸鈥濊鎻愪氦鈥濓紝鎰忔濆氨鏄鍙ユ彁浜や互鍚庯紝鍗虫墽琛屼簡 Commit 浠ュ悗鍒殑浜嬪姟灏辫兘璇诲埌杩欎釜鏀瑰彉锛屽彧鑳借鍙栧埌宸茬粡鎻愪氦鐨勬暟鎹侽racle绛夊鏁版暟鎹簱榛樿閮芥槸璇ョ骇鍒

Read Commited 涓嶅厑璁歌剰璇伙紝浣嗕細鍑虹幇闈為噸澶嶈銆

鍙噸澶嶈(Repeatable Read)锛

鐩磋瘧灏辨槸鈥濆彲浠ラ噸澶嶈鈥濓紝杩欐槸璇村湪鍚屼竴涓簨鍔¢噷闈㈠厛鍚庢墽琛屽悓涓涓煡璇㈣鍙ョ殑鏃跺欙紝寰楀埌鐨勭粨鏋滄槸涓鏍风殑銆

Repeatable Read 涓嶅厑璁歌剰璇伙紝涓嶅厑璁搁潪閲嶅璇伙紝浣嗘槸浼氬嚭鐜板够璞¤銆

涓茶璇(Serializable)

鐩磋瘧灏辨槸鈥濆簭鍒楀寲鈥濓紝鎰忔濇槸璇磋繖涓簨鍔℃墽琛岀殑鏃跺欎笉鍏佽鍒殑浜嬪姟骞跺彂鎵ц銆傚畬鍏ㄤ覆琛屽寲鐨勮锛屾瘡娆¤閮介渶瑕佽幏寰楄〃绾у叡浜攣锛岃鍐欑浉浜掗兘浼氶樆濉炪

Serializable 涓嶅厑璁镐笉涓鑷寸幇璞$殑鍑虹幇銆

浜嬪姟闅旂鐨勫疄鐜扳斺旈攣
鍏变韩閿(S閿)

鐢ㄤ簬鍙鎿嶄綔(SELECT)锛岄攣瀹氬叡浜殑璧勬簮銆傚叡浜攣涓嶄細闃绘鍏朵粬鐢ㄦ埛璇伙紝浣嗘槸闃绘鍏朵粬鐨勭敤鎴峰啓鍜屼慨鏀广

鏇存柊閿(U閿)

鐢ㄤ簬鍙洿鏂扮殑璧勬簮涓傞槻姝㈠綋澶氫釜浼氳瘽鍦ㄨ鍙栥侀攣瀹氫互鍙婇殢鍚庡彲鑳借繘琛岀殑璧勬簮鏇存柊鏃跺彂鐢熷父瑙佸舰寮忕殑姝婚攣銆

鐙崰閿(X閿侊紝涔熷彨鎺掍粬閿)

涓娆″彧鑳芥湁涓涓嫭鍗犻攣鐢ㄥ湪涓涓祫婧愪笂锛屽苟涓旈樆姝㈠叾浠栨墍鏈夌殑閿佸寘鎷叡浜缉銆傚啓鏄嫭鍗犻攣锛屽彲浠ユ湁鏁堢殑闃叉鈥滆剰璇烩濄

Read Uncommited 濡傛灉涓涓簨鍔″凡缁忓紑濮嬪啓鏁版嵁锛屽垯鍙﹀涓涓暟鎹垯涓嶅厑璁稿悓鏃惰繘琛屽啓鎿嶄綔锛屼絾鍏佽鍏朵粬浜嬪姟璇绘琛屾暟鎹傝闅旂绾у埆鍙互閫氳繃鈥滄帓浠栧啓閿佲濆疄鐜般

Read Committed 璇诲彇鏁版嵁鐨勪簨鍔″厑璁稿叾浠栦簨鍔$户缁闂琛屾暟鎹紝浣嗘槸鏈彁浜ょ殑鍐欎簨鍔″皢浼氱姝㈠叾浠栦簨鍔¤闂琛屻傚彲浠ラ氳繃鈥滅灛闂村叡浜閿佲濆拰鈥滄帓浠栧啓閿佲濆疄鐜般

Repeatable Read 璇诲彇鏁版嵁鐨勪簨鍔″皢浼氱姝㈠啓浜嬪姟锛堜絾鍏佽璇讳簨鍔★級锛屽啓浜嬪姟鍒欑姝换浣曞叾浠栦簨鍔°傚彲浠ラ氳繃鈥滃叡浜閿佲濆拰鈥滄帓浠栧啓閿佲濆疄鐜般

Serializable 璇诲姞鍏变韩閿侊紝鍐欏姞鎺掍粬閿侊紝璇诲啓浜掓枼銆

All posts

Other pages

鍙戣〃璇勮

閭鍦板潃涓嶄細琚叕寮銆 蹇呭~椤瑰凡鐢*鏍囨敞