杩??㈣蛋?ヤ?涓?涓?椋?灏?浠?浠???韬?绌挎?煎??琛????峰??锛??????跨??涓?涓?MacBookPro锛??????ｇ?灏???????锛????ｄ?瀹規貳瀹????肩???
??蹇???涓?棰わ????夥?杩????舵??甯?锛??舵??甯??ラ?㈡???????????蹇???椤挎?朵?娣″??浜?锛?琛ㄩ?㈠?绋沖????蹇?????寰?涓??廣??
???訛?浠??????跨??????绠???锛?蹇???????浜?涓?涓?锛??跺???ㄧ?艱?浣?????浜?涓?涓???锛?涓??ュ氨寮?????
?㈣??瀹?锛???浣?绠???涓?璇寸簿??Mysql浼????規?锛?浣????ヨ?磋?翠?瀵?ysql??浜??＄??浜?瑙ｅ?с??
??蹇?????浜?涓?涓?锛?杩?涓?绠?????锛??ユ????????澶???澶???锛????ラ?㈣??涔???锛??╁氨????澶???锛?浜?璇?涓?璇達?涓??誨氨??????
??锛?濂界??锛??版??搴???浜??℃????涓?缁?sql璇??ョ??????版??搴??昏?澶???????锛??ㄨ?缁???sql??浣?涓?锛?瑕?涔??ㄩ?ㄦ?ц?????锛?瑕?涔??ㄩ?ㄦ?ц?澶辮觸??
??锛?杩?????涓?缁?sql??浣?锛?涓句釜绠?????缁??哥??渚?瀛?灏辨??杞?璐??锛?浜???涓?瑕?杩?琛?杞?璐???ｄ?杞??虹??璐??瘋??ｉ?憋?杞??ョ??璐??瘋????憋?杩?涓や釜??浣??藉?椤誨???舵?ц?????锛?涓轟?纭?淇??版????涓??存?с??
?㈣??瀹?锛?????浣????頒??版??涓??存?э?浣??ラ??浜??＄???規?у??锛?璇磋?翠?????瑙ｃ??
??锛???ysql涓?浜??＄????澶х?規?т富瑕?????锛???瀛???Atomicity)??涓??存??Consistent)????绂繪??Isalotion)????涔???Durable)锛?绠?绉頒負ACID??
??锛???瀛??ф????浜??＄????瀛??ф??浣?锛?瀵規?版????淇??矽?涔??ㄩ?ㄦ?ц?????锛?瑕?涔??ㄩ?ㄥけ璐ワ?瀹??頒??＄????瀛??э????轟??ュ???Redo/Undo?哄?躲??
??锛?涓??存?ф?????ц?浜??″???????舵??瑕?涓??達???浠ョ??瑙ｄ負?版??涓??存?с????绂繪?т晶????浜??′??寸?鎬???绂夥?涓???褰卞??锛?杩?涓?涓?浜??¤?劇疆????绂葷駭????瀵??????崇郴??
??锛???涔??у???????ㄤ?涓?浜??℃??浜ゅ??锛?杩?涓?浜??＄???舵??浼?琚???涔????版?版??搴?涓?锛?涔?灏辨??浜??℃??浜わ?瀵規?版?????闆????存?闆?浼???涔????版?版??搴?涓???
??锛??ㄦ??????瑙ｄ腑锛???瀛??с????绂繪?с????涔??ч?芥??涓轟?淇???涓??存?ц??瀛??ㄧ??锛?涓??存?т?????缁?????????
蹇???????娆㈠??锛???瀹?浜?锛?骞蟲?惰????澶?锛??㈣??灏變?璇達?骞稿ソ?句???????
?㈣??瀹?锛?????浣?璇村??瀛??ф???轟??ュ???Redo/Undo?哄?訛?浣??借?翠?璇?edo/Undo?哄?跺??锛?
????锛????借?翠?浠?涔?锛?涓?灏?蹇?缁???宸卞??浜?涓?棰?澶ч?楓??涓???锛??ヨ??瀛???杩???璐э???瑁??ユ??????????浜?????绉?锛??ョ??????
??锛?Redo/Undo?哄?舵??杈?绠???锛?瀹?浠?灏?????瀵規?版?????存?版??浣??藉???版?ュ?涓???
??锛?Redolog?ㄦ?ヨ?闆????版????琚?淇??瑰?????鹼???浠ョ?ㄦ?ユ?㈠???????atafile??宸叉????浜??℃?存?扮???版??锛?Undolog???ㄦ?ヨ?闆??版???存?闆?????鹼?淇?璇??版???存?闆け璐ヨ?藉???婊???
??锛???濡??版??搴??ㄦ?ц???杩?绋?涓?锛?涓?灏?蹇?宕╀?锛???浠ラ??杩?璇ユ?ュ????瑰?锛???婊?涔???宸茬??ц?????????浣?锛?瀹??頒??＄??涓??存?с??
?㈣??瀹?锛???浠ヤ婦涓?涓??烘??锛?璇翠?涓??蜂???瀹??版?绋???锛?
??锛???浠ョ??锛???濡???涓??跺?繪?版??搴?宕╂?锛??ㄥ穿婧?涔?????浜?????浜????ㄦ?ц?锛?浜???宸茬???浜わ???浜???杩?????浜ゃ??褰??版??搴?????杩?琛?crash-recovery?訛?灏變???杩?Redolog灏?宸茬???浜や??＄???存?瑰???版?版????浠訛???杩?娌℃????浜ょ??灏遍??杩?Undolog杩?琛?rollback??
?㈣??瀹?锛?涔???浣?杩????頒??＄????绂葷駭??锛?浣??借?翠?璇村??锛?
??锛???浠ョ??锛???ysql涓?浜??＄????绂葷駭????涓哄??澶х??绾э?璇繪????浜?READUNCOMMITTED)??璇繪??浜?READCOMMITTED)??????澶?璇?REPEATABLEREAD)??涓茶???(SERIALIZABLE)??
??锛?璇繪????浜や?璇誨?闆???涓?浜??＄??????浜ょ???版??锛?浜х????璇婚??棰?锛?璇繪??浜ゅ??瑙ｅ?充???璇葷??锛??虹?頒?涓?????澶?璇夥??沖?ㄤ?涓?浜??′換???跺?昏?誨?扮???版?????戒?涓??鳳????戒????闆?跺??浜??″?規?版??淇??規??浜ゅ????褰卞??锛?涓?????瀵逛?update????浣???
??锛?????澶?璇昏В?充?涔???涓?????澶?璇誨????璇葷????棰?锛?浣????卞甫?ヤ?骞昏?葷????棰?锛?骞昏?諱???????瀵?nser??浣???
??锛?渚?濡?锛?绗?涓?涓?浜??℃?ヨ???涓?User琛?d=100???頒?瀛??ㄨ?ユ?版??琛?锛?杩??剁??浜?涓?浜??″??杩??ヤ?锛??闆?浜?涓???d=100???版??琛?骞朵???浜や?浜??°??
??锛?杩??剁??涓?涓?浜??℃?闆?涓???d=100???版??琛?浼??ヤ富???茬??锛?绗?涓?涓?浜??″??select涓?涓?锛?????d=100?版??琛?宸茬?瀛????杩?灏辨??骞昏?彙??
?㈣??瀹?锛?灏?浼?瀛?浣??芥?绀轟?涓???锛???涓?澶?浼?浣??芥????????锛????佃???ㄨ???锛?浣?婕?绀烘????涓?????
?蜂漢???撮??浜虹??楝鹼???淇′?涓?楝鹼?浣?杩?绯???澶村????寰?寰?锛??烘?ヨ?X?繪??瑕?杩???锛????介?榛???娉???瀹??插????
??锛?棣?????寤轟?涓?User琛????涓轟?涓?娴?璇?琛??娴?璇?琛ㄩ???㈡??涓?涓?瀛?娈碉?骞舵???ヤ袱?℃?璇??版????
CREATETABLEUser(idINT(11)NOTNULLPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20),ageINTDEFAULT0)ENGINE=InnoDBDEFAULTCHARSET=gb2312;INSERTINTO`user`VALUES(1,'zhangsan',23);INSERTINTO`user`VALUES(2,'lisi',20);
??锛???ysql涓???浠ュ???ヨ???涓?浠???榛?璁ら??绂葷駭??锛???浠ョ????ysql??榛?璁ら??绂葷駭????REPEATABLE-READ??
??锛????ユ?绀轟?涓?璇繪????浜わ?????榛?璁ょ????绂葷駭??淇??逛負READUNCOMMITTED??
??锛?浠?璁劇疆??绂葷駭????璇??ヤ腑setglobaltransactionisolationlevelreaduncommitted锛?杩?????global涔???浠ユ?㈡??session锛?global琛ㄧず?ㄥ???锛???session琛ㄧず褰???浼?璇?锛?涔?灏辨??褰???绐??ｆ??????
??锛?褰?璁劇疆瀹???绂葷駭????瀵逛?涔?????寮???浼?璇?锛?????????锛?瑕????版??寮?涓?涓?绐??ｈ?劇疆??绂葷駭??????????
??锛??跺????寮???浜??★?Mysql涓?寮???浜??℃??涓ょ??瑰?begin/starttransaction锛???????浜や??℃?ц?commit锛???????婊?浜???ollback??
??锛??ㄦ?ц?begin/starttransaction?戒護锛?瀹?浠?骞朵???涓?涓?浜??＄??璧風?癸??ㄦ?ц?瀹?瀹?浠?????绗?涓?涓?sql璇??ワ???琛ㄧず浜??＄??姝ｇ?????ㄣ??
??锛?杩????存?ユ??寮?涓や釜?扮??绐??ｏ????跺???浜??★??ㄧ??涓?涓?绐??ｅ??update涓?涓?id=1???版??琛?name?逛負'??绉?????绉???'锛??ц???????
??锛??跺????绗?浜?涓?绐??ｆ?ц?涓ゆ?＄???ヨ?????????绐??ｄ?update涔??????ヨ?㈠??update涔??????ヨ????
??锛?绗?涓?涓?session浜х????????浜ょ??浜??＄???舵??灏變??存?ュ獎???扮??浜?sesison锛?涔?灏辨????璇彙??
??锛?瀵逛?璇繪??浜や???涓??風??锛?寮???浜??″??锛?绗?涓?涓?浜??″???ц??ヨ?㈡?版??锛??跺??绗?浜?涓?session?ц?update??浣?锛?浣???杩?娌℃??commit锛?杩???绗?涓?涓?session??娆?elect锛??版??骞舵病???瑰??锛???绗?浜?涓?session?ц?commit涔???锛?绗?涓?涓?session??娆?elect灏辨???瑰???????版??浜???
??锛?杩??風??涓?涓?浜??＄???ヨ?㈢???灏變??跺?扮??浜?浜??＄??褰卞??锛?杩?涓?涔?灏辨??浜х??涓?????澶?璇葷????棰???
?㈣??瀹?锛?灏?浼?瀛?浣??界?諱?涓?浠??ц???杩?绋??懼??锛?浣?璁茬???????逛貢锛???杩?娌℃??褰誨????姐??
??蹇???涓?涓???浠?涔?椹??ㄩ?杩?锛?娆插????娉?锛?杩??㈣??瀹????句己??锛?璇存?惰??ｆ?跺揩锛?浠?宸???″???藉?虹??锛?浠??沖??″???垮?虹焊锛?寮?濮??彙??
??锛?杩?涓???璇繪??浜ょ???堕?磋醬?撅?璇繪????浜ょ???堕?磋醬?撅?????涔?涓??風??锛?绗?浜?涓?select???跺???版??灏卞凡缁??瑰??浜???
杩????㈣??瀹??胯??????劇??浜?涓??癸?寰?寰????逛??瑰ご锛??磋??插?烘??????绗???锛????充?杩?绯???澶村??搴?璇ヤ?浼??????炬??浜??с??
?㈣??瀹?锛???锛?浣??ョ??婕?绀轟???????澶?璇誨?с??
??锛???锛?濂界??锛??跺??灏辨??????澶?璇夥???涔???涓??風????浣???
??锛?灏?涓や釜session寮???涓?EPEATABLEREAD锛????跺???浜??★??ㄧ??涓?涓?浜??′腑??select锛??跺???ㄧ??浜?涓?浜??￠????pdate?版??琛?锛???浠ュ???闆?充嬌绗?浜?涓?浜??″凡缁?commit锛?绗?涓?涓?浜??″??娆?elect?版??涔?杩???娌℃???瑰??锛?杩?灏辮В?充?涓?????澶?璇葷????棰???
??锛?杩?????涓?涓??????版?瑰氨????ysql涓?锛?榛?璁ょ??涓?????澶?璇諱釜??绂葷駭??涔?瑙ｅ?充?骞昏?葷????棰???
??锛?浠?涓??㈢??婕?绀轟腑??浠ョ???虹??涓?涓?浜??′腑??select涓?涓?id=3???版??琛?锛?杩??℃?版??琛???涓?瀛??ㄧ??锛?杩???Emptyset锛??跺??绗?浜?涓?浜??′腑insert涓???d=3???版??琛?骞朵?commit锛?绗?涓?涓?浜??′腑??娆?elect??锛??版??涔?濂芥??娌℃??id=3???版??琛???
??锛???????涓茶???锛??峰?姝ラ?や???涓??風??锛?缁???涔???Mysql涓?榛?璁ょ??涓?????澶?璇婚??绂葷駭????缁???涓??鳳?涓茶??????ц?娴?绋??稿?浜???浜??＄???ц?杩?绋???涓洪『搴??ц?锛???杩?杈瑰氨涓?????婕?绀轟???
??锛?杩???澶х??绾т?涓??頒?锛???绂葷??????????娓?澧?寮猴?浣????ц?藉?存??瓒??ヨ?宸???
?㈣??瀹?锛?????ц?借??ヨ?宸?锛?涓轟?涔?浼??ц?借??ヨ?宸?锛?浣??借?翠?璇村??????锛?
????锛???杩??達?灏?璇翠??ヤ?姝誨??锛?杩?涓?濂戒?锛?杩?涓?寰?璇村?灞?瀹??闆????浜?锛?浠????ョ????瑁??ユ??????锛?????浜???姝ｅ??ユ????????
??锛?杩?涓?寰?浠?Mysq????璇磋搗锛???ysql涓???????浠ュ??涓哄??浜???/璇婚??(SharedLocks)????浠???/????(ExclusiveLocks)???撮??????琛???(RecordLocks)??琛ㄩ????
??锛??ㄥ??涓???绂葷駭??涓???????瀹???瑕?娑????ц?界??锛???璇繪????浜ゆ??娌℃????浠諱?????锛???浠ュ?逛?瀹??ヨ?翠?灏辨??娌℃????绂葷??????锛???浠ュ?????ц?戒?????濂界????
??锛?瀵逛?涓茶?????????涓???澶ч??锛?璇葷???跺?????變韓??锛?涓??藉??锛??????跺??锛?????????瀹???锛??誨??跺??浜??＄?????ュ??璇誨??锛??ユ???跺????浜??￠?挎?堕?翠??藉???ュ氨浼??存?ユ?ヨ??訛???浠ュ?????ц?戒?????宸???锛?瀵逛?瀹??ュ氨娌℃??浠?涔?骞跺???у??瑷???
??锛?瀵逛?璇繪??浜ゅ??????澶?璇夥?浠?浠?淇╃??瀹??版???奸【瑙ｅ?蟲?版????棰?锛??跺????瑕???涓?瀹???骞跺??琛?锛???浠ュ?ㄥ???頒????哄?朵?姣?涓茶???浼???寰?澶?锛???楂?骞跺???э???浠ユ?ц?戒?浼?姣?杈?濂姐??
??锛?浠?浠?淇╃??搴?灞?瀹??伴???ㄧ????MVCC(澶?????骞跺???у???瑰?杩?琛?瀹??般??
?㈣??瀹?锛?浣??藉??璇翠?涓???杩???涓?????姒?蹇靛??锛???涓???寰???锛?璇磋?翠?????瑙ｃ??
??锛????濂界??锛??變韓??????瀵瑰??涓?浠芥?版??锛?澶?涓?璇繪??浣???浠ュ???惰?琛?锛?绠????ヨ?村?寵?誨????锛?涓??藉??骞朵???骞惰?璇夥???浠?????瀵瑰????浣?锛???濡?褰???????浣?娌℃??瀹???锛??ｄ?瀹?浼??繪???跺??????????璇婚??锛??沖??????锛??跺??璇誨???介?誨???
??锛???琛?????琛ㄩ??锛???浠?????绮?搴??杩?琛???????锛?琛?????瀹?褰????版??琛?锛?????绮?搴??锛???????????????茬????姒???灏?锛?骞跺??搴???锛?琛???涔???MyISAM??InnoDB???哄??涔?涓?锛?InnoDB????琛???骞朵?????浜??°??
??锛???琛ㄩ????????绮?搴?ぇ锛?????蹇?锛?寮???灏?锛?浣??????茬????姒???澶э?骞跺??搴????
??锛??撮????????涓轟袱绉?锛?GapLocks??Next-KeyLocks??GapLocks浼???浣?涓や釜绱㈠?涔??寸???洪?達?姣?濡?select*fromUserwhereid>3andid<5 for update锛?灏變??ㄥ?洪??3锛?5)涔??村??涓?Gap Locks??
??锛?Next-KeyLocks??GapLocks+RecordLocks褰㈡?????洪?撮??select*fromUserwhereid>=3andid=<5 for update锛?灏變??ㄥ?洪??3,5]涔??村??涓?Next-Key Locks??
?㈣??瀹?锛???ysql涓?浠?涔??跺??浼????????
??锛??ㄦ?版??搴???澧????????廣???ヤ腑锛?????澧????????規??浼???涓???瀹???锛????????ヨ?㈠苟涓?浼?????锛????介??杩???elect璇??ュ???懼???lockinsharemode????forupdate?ュ???變韓????????瀹?????
?㈣??瀹?锛?浣??ㄤ??㈡????VCC(澶?????骞跺???у??锛?浣??借?翠?璇村??????锛?
??锛??ㄥ????VCC?剁?ㄥ?頒?涓??存?ц??撅??ㄤ?????璇繪??浜ゅ??????澶?璇葷??瀹??般??
??锛??ㄥ???闆????澶?璇葷????绂葷駭??锛?????瑕??ㄤ??″?濮????跺????寤轟??存?ц??撅?涔?????蹇??э?涔??????ヨ?㈤???藉?辯?ㄨ?涓?涓??存?ц??撅???缁???浜??″?規?版?????存?規??瀵瑰???浜??℃??涓???瑙???锛?杩??峰氨瀹??頒?????澶?璇彙??
??锛???璇繪??浜わ?姣?涓?涓?璇??ユ?ц????戒????拌?＄???轟?涓??扮??瑙??撅?杩?涓?涔???????澶?璇誨??璇繪??浜ゅ??VCC瀹??闆???????哄????
?㈣??瀹?锛??ｄ??ラ??蹇???瑙?????VCC搴?灞?????涔?宸ヤ?????锛?
??锛???nnoDB涓?姣?涓?涓?浜??￠?芥??涓?涓???宸辯??浜???d锛?骞朵?????涓???锛???澧?????
??锛?瀵逛?Mysql涓???姣?涓?涓??版??琛??芥?????藉???ㄥ?涓?????锛??ㄦ??娆′??℃?存?版?版?????跺??锛??戒?????涓?涓??扮???版??????锛?骞朵?????宸辯???版??id璧??肩?褰?????????rowtrx_id??
?㈣??瀹?锛?灏?浼?瀛?浣???浠ョ?諱釜?炬????????锛???涓???寰????姐??
????浠?涔???娉????瀹??ㄦ病??娉?锛????藉??浠?灞??″?????垮?虹????绾革?杩??????諱?璧鋒?ワ?瑕???杩?娆￠?㈣??涓?杩?灏辮?浜???锛?娴?璐逛???涓ゅ?绾稿??绗?姘達??闆?ㄧ??绗???绾稿?璐靛??锛????借??哄?諱???
??锛?濡??句腑??绀猴???濡?涓?涓?浜??℃?存?頒???涓?琛??版??锛??ｄ?灏變???瀵瑰???涓?涓??版????????
??锛?瀹???涓?????1??????2骞堕??瀹????╃??瀛??ㄧ??锛????句腑??U1??U2瀹???灏辨??undolog锛?杩?v1??v2???????規??褰???v3??undolog璁＄???烘?ョ????
?㈣??瀹?锛??ｅ?逛?涓?涓?蹇??ф?ヨ?達?浣??ラ??瀹?瑕??靛驚浠?涔?瑙?????锛?
??锛???锛?瀵逛?涓?涓?浜??¤??炬?ヨ?撮?や?瀵矽??宸辨?存?扮???繪????瑙?锛????杩???涓?绉????碉?????????浜ょ??锛??芥??涓???瑙???锛?????宸茬???浜わ?浣??????ㄥ??寤鴻??句?????浜ょ??涔???涓???瑙???锛?????宸茬???浜わ??ユ???ㄥ??寤鴻??句?????浜ょ??????瑙?????
?㈣??瀹?锛???濡?涓や釜浜??℃?ц?????浣?锛?????涔?淇?璇?骞跺?????
??锛???濡?浜?????浜????借??ц?update??浣?锛?浜?????update?版??琛????跺??锛??????峰??琛???锛???瀹??版??锛?褰?浜???瑕?杩?琛?update??浣????跺??锛?涔?浼????峰??璇ユ?版??琛???琛???锛?浣???宸茬?琚?浜???????锛?浜???????ait??
??锛??ユ??浜????挎?堕?存病?????鵑??锛?浜???灏變??虹?拌??跺?甯搞??
?㈣??瀹?锛?杩?涓?????pdate??where?????′歡???ㄦ??绱㈠??????典??э?
??锛???锛???????
?㈣??瀹?锛??ｆ病??绱㈠????′歡涓????娌″??娉?蹇???瀹?浣??版?版??琛????
??锛??ユ??娌℃??绱㈠????′歡涓?锛?灏辮?峰??????琛?锛??藉??涓?琛???锛??跺??Mysql浼???娆¤?婊ょ?????′歡????琛?骞堕???鵑??锛?????绗????′歡??琛???浼?缁х畫????????
??锛?杩??風???ц?芥???涔?浼?姣?杈?澶с??
?㈣??瀹?锛?????
姝ゆ?堕?㈣??瀹???????琛ㄤ?涓?澶???宸茬?杩??諱?锛?涔?宸茬??頒?楗??規?跺?夥????充?搴?璇ユ????瀛?楗誇?锛?涓?浼?缁х畫杩介???э?涓や漢??缁??典?涓???绉?锛?浠?缁?浜?寮??ｄ???
?㈣??瀹?锛?灏?浼?瀛?锛??闆?ㄦ?堕?翠?宸茬??頒?楗??逛?锛?浠?澶╃???㈣??灏卞?版?ょ????э?浣????葷?????ュ?с??
??锛?????????????????????
璐ｄ換缂?杈?锛?xj
??????棰?锛???浠ヤ負??瀵?ySQL浜??″???锛??村?版?????頒??塊???㈣??瀹?
??绔??哄?锛???寰?淇″??浼??鳳??版??????涓?寮?????娆㈣?娣誨???蟲敞锛???绔?杞?杞借?鋒敞???哄???