I was just putting a few finishing touches on my simulateneous edit bug fix, and I have two quick questions for anyone willing to answer them:
* Do HEAP tables in MySQL disappear on restart, requiring you to run another CREATE TABLE query? * If yes, would it be better to grant CREATE TABLE permissions to enuser and create it on first use, or should something be put in a startup script?
Now some of you may be interested in what I'm doing mucking around with HEAP tables, so I attached a source file and I will now explain my rationale.
The simultaneous edit bug occurs very rarely (maybe once every few days), when two users pass the edit conflict check and then simultaneously try to update the article. No edit conflict is registered. My thinking went like this:
* Can't lock the whole table because it's not worth the performance degradation for such a rare error * Instead use MySQL's user locks to provide synchronisation * But PHP threads die all the time for no good reason, so there is a risk that a lock will be left unreleased. Due to persistent connections, the lock will stay active indefinitely. Only the DB thread which created the lock can release it. * Waiting for a timeout and then pressing on regardless is possible but not ideal * Instead, when a PHP thread gets a lock, it registers its DB thread ID in a HEAP table. * If another thread tries to get the lock but times out, it kills the offending DB thread
Using shared memory or something like that would be faster, it's just not scalable.
-- Tim Starling.
begin 666 DatabaseFunctions.php M/#-"F=L;V)A;" D25 [#0II;F-L=61E7V]N8V4H("(D25 O1G5L;'1E>'13 M=&]P;&ES="YP:' B("D[#0II;F-L=61E7V]N8V4H("(D25 O0V%C:&5-86YA M9V5R+G!H<"(@*3L-"@T*)'=G3&%S=$1A=&%B87-E475E<GD@/2 B(CL-"@T* M9G5N8W1I;VX@=V9'971$0B@@)&%L='5S97(@/2 B(BP@)&%L='!A<W-W;W)D M(#T@(B(L("1A;'1S97)V97(@/2 B(BP@)&%L=&1B(#T@(B(@*0T*>PT*"6=L M;V)A;" D=V=$0G-E<G9E<BP@)'=G1$)U<V5R+" D=V=$0G!A<W-W;W)D.PT* M"6=L;V)A;" D=V=$0FYA;64L("1W9T1"8V]N;F5C=&EO;BP@)'=G16UE<F=E M;F-Y0V]N=&%C=#L-"@T*"21N;V-O;FX@/2!S=')?<F5P;&%C92@@(B0Q(BP@ M)'=G1$)S97)V97(L('=F37-G*" B;F]C;VYN96-T(B I("D[#0H))&YO9&(@ M/2!S=')?<F5P;&%C92@@(B0Q(BP@)'=G1$)N86UE+"!W9DUS9R@@(FYO9&(B M("D@*3L-"@T*"21H96QP;64@/2 B7&X<#Y)9B!T:&ES(&5R<F]R('!E<G-I M<W1S(&%F=&5R(')E;&]A9&EN9R!A;F0@8VQE87)I;F<@(B N#0H)(" B>6]U M<B!B<F]W<V5R(&-A8VAE+"!P;&5A<V4@;F]T:69Y('1H92 \82!H<F5F/5PB M;6%I;'1O.B(@+@T*"2 @)'=G16UE<F=E;F-Y0V]N=&%C=" N(")<(CY7:6MI M<&5D:6$@9&5V96QO<&5R<SPO83XN/"]P/B([#0H-"@EI9B H("1A;'1U<V5R M("$]("(B("D@>PT*"0DD<V5R=F4@/2 H)&%L='-E<G9E<B _("1A;'1S97)V M97(@.B D=V=$0G-E<G9E<B I.PT*"0DD9&(@/2 H)&%L=&1B(#@)&%L=&1B M(#H@)'=G1$)N86UE("D[#0H)"21W9T1"8V]N;F5C=&EO;B ](&UY<W%L7V-O M;FYE8W0H("1S97)V92P@)&%L='5S97(L("1A;'1P87-S=V]R9" I#0H)"0EO M<B!D:64H(")B860@<W%L('5S97(B("D[#0H)"6UY<W%L7W-E;&5C=%]D8B@@ M)&1B+" D=V=$0F-O;FYE8W1I;VX@*2!O<B!D:64H#0H)"2 @:'1M;'-P96-I M86QC:&%R<RAM>7-Q;%]E<G)O<B@I*2 I.PT*"7T-"@T*"6EF("@@(2 D=V=$ M0F-O;FYE8W1I;VX@*2![#0H)"4 D=V=$0F-O;FYE8W1I;VX@/2!M>7-Q;%]P M8V]N;F5C="@@)'=G1$)S97)V97(L("1W9T1"=7-E<BP@)'=G1$)P87-S=V]R M9" I#0H)"0EO<B!W9D5M97)G96YC>4%B;W)T*"D[#0H)"0T*"0EI9B@@(6UY M<W%L7W-E;&5C=%]D8B@@)'=G1$)N86UE+" D=V=$0F-O;FYE8W1I;VX@*2 I M('L-"@D)"2\J(%!E<G-I<W1E;G0@8V]N;F5C=&EO;G,@;6%Y(&)E8V]M92!S M='5C:R!I;B!A;B!U;G5S86)L92!S=&%T92 J+PT*"0D)=V9$96)U9R@@(E!E M<G-I<W1E;G0@8V]N;F5C=&EO;B!I<R!B<F]K96X_7&XB+"!T<G5E("D[#0H) M"0D-"@D)"4 D=V=$0F-O;FYE8W1I;VX@/2!M>7-Q;%]C;VYN96-T*" D=V=$ M0G-E<G9E<BP@)'=G1$)U<V5R+" D=V=$0G!A<W-W;W)D("D-"@D)"0EO<B!W M9D5M97)G96YC>4%B;W)T*"D[#0H)"0D-"@D)"4!M>7-Q;%]S96QE8W1?9&(H M("1W9T1";F%M92P@)'=G1$)C;VYN96-T:6]N("D-"@D)"0EO<B!W9D5M97)G M96YC>4%B;W)T*"D[#0H)"7T-"@E]#0H)(R!M>7-Q;%]P:6YG*" D=V=$0F-O M;FYE8W1I;VX@*3L-"@ER971U<FX@)'=G1$)C;VYN96-T:6]N.PT*?0T*#0HO M*B!#86QL('1H:7,@9G5N8W1I;VX@:68@=V4@8V]U;&1N)W0@8V]N=&%C="!T M:&4@9&%T86)A<V4N+BX-"B @(%=E)VQL('1R>2!T;R!U<V4@=&AE(&-A8VAE M('1O(&1I<W!L87D@<V]M971H:6YG(&EN('1H92!M96%N=&EM92 J+PT*9G5N M8W1I;VX@=V9%;65R9V5N8WE!8F]R="@@)&US9R ]("(B("D@>PT*"6=L;V)A M;" D=V=4:71L92P@)'=G57-E1FEL94-A8VAE+" D=&ET;&4L("1W9T]U='!U M=$5N8V]D:6YG.PT*"0T*"6AE861E<B@@(D-O;G1E;G0M='EP93H@=&5X="]H M=&UL.R!C:&%R<V5T/21W9T]U='!U=$5N8V]D:6YG(B I.PT*"6EF*"1M<V<@ M/3T@(B(I("1M<V<@/2!W9DUS9R@@(FYO8V]N;F5C="(@*3L-"@DD=&5X=" ] M("1M<V<[#0H-"@EI9B@D=V=5<V5&:6QE0V%C:&4I('L-"@D):68H)'=G5&ET M;&4I('L-"@D)"21T(#TF("1W9U1I=&QE.PT*"0E](&5L<V4@>PT*"0D):68H M)'1I=&QE*2![#0H)"0D))'0@/2!4:71L93HZ;F5W1G)O;5523"@@)'1I=&QE M("D[#0H)"0E](&5L<V4@>PT*"0D)"21T(#T@5&ET;&4Z.FYE=T9R;VU497AT M*"!W9DUS9R@B;6%I;G!A9V4B*2 I.PT*"0D)?0T*"0E]#0H-"@D))&-A8VAE M(#T@;F5W($-A8VAE36%N86=E<B@@)'0@*3L-"@D):68H("1C86-H92T^:7-& M:6QE0V%C:&5D*"D@*2![#0H)"0DD;7-G(#T@(CQP('-T>6QE/2=C;VQO<CH@ M<F5D)SX\8CXD;7-G/&)R/EQN(B N#0H)"0D)=V9-<V<H(")C86-H961E<G)O M<B(@*2 N("(+V(^/"]P/EQN(CL-"@D)"0T*"0D))'1A9R ]("(\9&EV(&ED M/2=A<G1I8VQE)SXB.PT*"0D))'1E>'0@/2!S=')?<F5P;&%C92@-"@D)"0DD M=&%G+ T*"0D)"21T86<@+B D;7-G+ T*"0D)"21C86-H92T^9F5T8VA086=E M5&5X="@I("D[#0H)"7T-"@E]#0H)#0H)+RH@1&]N)W0@8V%C:&4@97)R;W(@ M<&%G97,A("!4:&5Y(&-A=7-E(&YO(&5N9"!O9B!T<F]U8FQE+BXN("HO#0H) M:&5A9&5R*" B0V%C:&4M8V]N=')O;#H@;F]N92(@*3L-"@EH96%D97(H(")0 M<F%G;6$Z(&YO8V%C:&4B("D[#0H)96-H;R D=&5X=#L-"@EE>&ET.PT*?0T* M#0IF=6YC=&EO;B!W9E%U97)Y*" D<W%L+" D9FYA;64@/2 B(B I#0I[#0H) M9VQO8F%L("1W9TQA<W1$871A8F%S95%U97)Y+" D=V=/=70[#0HC(PEW9E!R M;V9I;&5);B@@(G=F475E<GDB("D[#0H))'=G3&%S=$1A=&%B87-E475E<GD@ M/2 D<W%L.PT*#0H))&-O;FX@/2!W9D=E=$1"*"D[#0H))')E=" ](&UY<W%L M7W%U97)Y*" D<W%L+" D8V]N;B I.PT*#0H):68@*" B(B A/2 D9FYA;64@ M*2=V9$96)U9R@@(E-13#H@>R1S<6Q]7&XB M+"!T<G5E("D[#0H)?0T*"6EF("@@9F%L<V4@/3T]("1R970@*2![#0H)"21W M9T]U="T^9&%T86)A<V5%<G)O<B@@)&9N86UE("D[#0H)"65X:70[#0H)?0T* M(R,)=V90<F]F:6QE3W5T*"D[#0H)<F5T=7)N("1R970[#0I]#0H-"F9U;F-T M:6]N('=F1G)E95)E<W5L="@@)')E<R I('L@;7ES<6Q?9G)E95]R97-U;'0H M("1R97,@*3L@?0T*9G5N8W1I;VX@=V9&971C:$]B:F5C="@@)')E<R I('L@ M<F5T=7)N(&UY<W%L7V9E=&-H7V]B:F5C="@@)')E<R I.R!]#0IF=6YC=&EO M;B!W9DYU;5)O=W,H("1R97,@*2![(')E='5R;B!M>7-Q;%]N=6U?<F]W<R@@ M)')E<R I.R!]#0IF=6YC=&EO;B!W9DYU;49I96QD<R@@)')E<R I('L@<F5T M=7)N(&UY<W%L7VYU;5]F:65L9',H("1R97,@*3L@?0T*9G5N8W1I;VX@=V9& M:65L9$YA;64H("1R97,L("1N("D@>R!R971U<FX@;7ES<6Q?9FEE;&1?;F%M M92@@)')E<RP@)&X@*3L@?0T*9G5N8W1I;VX@=V9);G-E<G1)9"@I('L@<F5T M=7)N(&UY<W%L7VEN<V5R=%]I9"@@=V9'971$0B@I("D[('T-"F9U;F-T:6]N M('=F1&%T85-E96LH("1R97,L("1R;W<@*2![(')E='5R;B!M>7-Q;%]D871A M7W-E96LH("1R97,L("1R;W<@*3L@?0T*9G5N8W1I;VX@=V9,87-T17)R;F\H M*2![(')E='5R;B!M>7-Q;%]E<G)N;R@I.R!]#0IF=6YC=&EO;B!W9DQA<W1% M<G)O<B@I('L@<F5T=7)N(&UY<W%L7V5R<F]R*"D[('T-"@T*9G5N8W1I;VX@ M=V9,87-T1$)Q=65R>2@I#0I[#0H)9VQO8F%L("1W9TQA<W1$871A8F%S95%U M97)Y.PT*"7)E='5R;B D=V=,87-T1&%T86)A<V51=65R>3L-"GT-"@T*9G5N M8W1I;VX@=V93971344PH("1T86)L92P@)'9A<BP@)'9A;'5E+" D8V]N9" I M#0I[#0H))'-Q;" ](")54$1!5$4@)'1A8FQE(%-%5" D=F%R(#T@)R(@+@T* M"2 @=V93=')E;F-O9&4H("1V86QU92 I("X@(B<@5TA%4D4@*"1C;VYD*2([ M#0H)=V91=65R>2@@)'-Q;"P@(G=F4V5T4U%,(B I.PT*?0T*#0IF=6YC=&EO M;B!W9D=E=%-13"@@)'1A8FQE+" D=F%R+" D8V]N9" I#0I[#0H))'-Q;" ] M(")314Q%0U0@)'9A<B!&4D]-("1T86)L92!72$5212 H)&-O;F0I(CL-"@DD M<F5S=6QT(#T@=V91=65R>2@@)'-Q;"P@(G=F1V5T4U%,(B I.PT*#0H))')E M=" ]("(B.PT*"6EF("@@;7ES<6Q?;G5M7W)O=W,H("1R97-U;'0@*2 ^(# @ M*2![#0H)"21S(#T@;7ES<6Q?9F5T8VA?;V)J96-T*" D<F5S=6QT("D[#0H) M"21R970@/2 D<RT^)'9A<CL-"@D);7ES<6Q?9G)E95]R97-U;'0H("1R97-U M;'0@*3L-"@E]#0H)<F5T=7)N("1R970[#0I]#0H-"F9U;F-T:6]N('=F4W1R M96YC;V1E*" D<R I#0I[#0H)<F5T=7)N(&%D9'-L87-H97,H("1S("D[#0I] M#0H-"B,@261E86QL>2!W92=D(&)E('5S:6YG(&%C='5A;"!T:6UE(&9I96QD M<R!I;B!T:&4@9&(-"F9U;F-T:6]N('=F5&EM97-T86UP,E5N:7@H("1T<R I M('L-"@ER971U<FX@9VUM:W1I;64H("@@*&EN="ES=6)S='(H("1T<RP@."P@ M,BD@*2P-"@D)(" H:6YT*7-U8G-T<B@@)'1S+" Q,"P@,B I+" H:6YT*7-U M8G-T<B@@)'1S+" Q,BP@,B I+ T*"0D@("AI;G0I<W5B<W1R*" D=',L(#0L M(#(@*2P@*&EN="ES=6)S='(H("1T<RP@-BP@,B I+ T*"0D@("AI;G0I<W5B M<W1R*" D=',L(# L(#0@*2 I.PT*?0T*#0IF=6YC=&EO;B!W9E5N:7@R5&EM M97-T86UP*" D=6YI>'1I;64@*2![#0H)<F5T=7)N(&=M9&%T92@@(EEM9$AI M<R(L("1U;FEX=&EM92 I.PT*?0T*#0IF=6YC=&EO;B!W9E1I;65S=&%M<$YO M=R@I('L-"@DC(')E='5R;B!.3U<-"@ER971U<FX@9VUD871E*" B66UD2&ES M(B I.PT*?0T*#0HC(%-O<G1I;F<@:&%C:R!F;W(@37E344P@,RP@=VAI8V@@ M9&]E<VXG="!U<V4@:6YD97@@<V]R=',@9F]R($1%4T,-"F9U;F-T:6]N('=F M26YV97)T5&EM97-T86UP*" D=',@*2![#0H)<F5T=7)N('-T<G1R* T*"0DD M=',L#0H)"2(P,3(S-#4V-S@Y(BP-"@D)(CDX-S8U-#,R,3 B#0H)*3L-"GT- M"@T*(R @($QO8VL@;F%M97,@<W1A<G0@=VET:"!T=V@;&5T=&5R<R!T96QL M:6YG('EO=2!W:&%T('-O<G0@;V8@;&]C:R!I=" -"B,@:7,N(")%0R(@;65A M;G,@86X@961I="!C;VYF;&EC="!L;V-K+"!W:&EC:"!E9F9E8W1I=F5L>2!L M;V-K<R!A(&=I=F5N#0HC(&%R=&EC;&4@9G)O;2!E9&ET:6YG+"!W:&EL92!A M('-A=F4@:7,@=&%K:6YG('!L86-E+@T*(R @($EF(&$@=&AR96%D(&AA;F=S M(&]N('1O(&$@;&]C:R!F;W(@=&]O(&QO;F<L('1H92!T:')E861S('=A:71I M;F<@9V5T( T*(R!V:6]L96YT+B!!('!O<W-I8FQE(&9U='5R92!E>'1E;G-I M;VX@=V]U;&0@8F4@=&@<F5L96%S92!L;V-K<R!O;B -"B,@<VAU=&1O=VXL M(&)U="!R96%D('1H92!02% @;6%N=6%L(&%B;W5T(&-O;FYE8W1I;VX@:&%N M9&QI;F<@9FER<W0N#0HC(" @4F5T=7)N<R P(&%F=&5R("1M87A!='1E;7!T M<R!T:6UE;W5T<R!O<B Q(&]N('-U8V-E<W,-"@T*9G5N8W1I;VX@=V9'971, M;V-K*" D;F%M92P@)'1I;65O=70L("1M87A!='1E;7!T<R I('L-"@DD<W5C M8V5S<R ](# [#0H))&AA<V@@/2!M9#4H("1N86UE("D[#0H))&9N86UE(#T@ M(G=F1V5T3&]C:R([#0H-"@DC($-R96%T92!L;V-K<R!T86)L90T*#0H)=V91 M=65R>2@@(@T*"0E#4D5!5$4@5$%"3$4@248@3D]4($5825-44R!L;V-K<R H M#0H)"2 @;&]C:W-?=&ET;&4@0TA!4B@S,BD@3D]4($Y53$P@4%))34%262!+ M15DL#0H)"2 @;&]C:W-?=&ED($E.5 T*"0DI(%194$4]2$5!4"(L("1F;F%M M92D[#0H)#0H)9F]R("@@)&%T=&5M<'0@/2 P.R D871T96UP=" ("1M87A! M='1E;7!T<R F)B A)'-U8V-E<W,[("LK)&%T=&5M<'0@*2![#0H)"2,@5')Y M('1O(&=E="!T:&4@;&]C:PT*"0DD<F5S(#T@=V91=65R>2@@(E-%3$5#5"!' M151?3$]#2R@G>R1H87-H?2<L('LD=&EM96]U='TI(BP@)&9N86UE("D[#0H) M"21A(#T@;7ES<6Q?9F5T8VA?<F]W*" D<F5S("D[#0H)"6EF("@@)&$@(3T] M($9!3%-%*2![#0H)"0DD<W5C8V5S<R ]("1A6S!=.PT*"0E]#0H)"6UY<W%L M7V9R965?<F5S=6QT*" D<F5S("D[#0H)"0T*"0EI9B H("1S=6-C97-S(#T] M(#$I('L-"@D)"2,@268@<W5C8V5S<V9U;"P@<F5G:7-T97(@=&AR96%D($E$ M(&EN(&$@2$5!4"!T86)L90T*"0D))'1I9" ](&UY<W%L7W1H<F5A9%]I9"@I M.PT*"0D)=V91=65R>2@@(DE.4T525"!)3E1/(&QO8VMS("AL;V-K<U]T:60L M(&QO8VMS7W1I=&QE*2!604Q515,@*'LD=&ED?2DB+" D9FYA;64@*3L-"@D) M?2!E;'-E:68@*" D<W5C8V5S<R ]/2 P*2![#0H)"0DC(%1I;65O=70N(%1R M>2!T;R!K:6QL('1H92!O9F9E;F1I;F<@=&AR96%D+@T*"0D))')E<R ]('=F M475E<GDH(")314Q%0U0@;&]C:W-?=&ED($923TT@;&]C:W,@5TA%4D4@;&]C M:W-?=&ET;&4])WLD:&%S:'TG(BP@)&9N86UE("D[#0H)"0DD=&ED(#T@,#L- M"@T*"0D))&]B:B ](&UY<W%L7V9E=&-H7V]B:F5C="@@)')E<R I.PT*"0D) M:68@*" D;V)J("$]/2!&04Q312 I('L-"@D)"0DD=&ED(#T@)&]B:BT^)&QO M8VMS7W1I9#L-"@D)"7T-"@D)"6UY<W%L7V9R965?<F5S=6QT*" D<F5S("D[ M#0H-"@D)"6EF("@@)'1I9" I('L-"@D)"0EW9E%U97)Y*" B1$5,151%($92 M3TT@;&]C:W,@5TA%4D4@;&]C:W-?=&ET;&4])WLD:&%S:'TG(BP@)&9N86UE M("D[#0H)"0D)=V91=65R>2@@(DM)3$P@>R1T:61](BP@)&9N86UE("D[#0H) M"0E]#0H)"7T-"@E]#0H)<F5T=7)N("1S=6-C97-S.PT*?0T*#0IF=6YC=&EO M;B!W9E)E;&5A<V5,;V-K*" D;F%M92 I('L-"@DD:&%S:" ](&UD-2@@)&YA M;64@*3L-"@DD9FYA;64@/2 B=V9296QE87-E3&]C:R([#0H-"@EW9E%U97)Y M*" B4T5,14-4(%)%3$5!4T5?3$]#2R@G>R1H87-H?2<I(BP@)&9N86UE("D[ M#0H)=V91=65R>2@@(D1%3$5412!&4D]-(&QO8VMS(%=(15)%(&QO8VMS7W1I D=&QE/2=[)&AA<VA])R(L("1F;F%M92 I.PT*?0T*#0H_/@T* ` end
Tim Starling wrote:
I was just putting a few finishing touches on my simulateneous edit bug fix,
Yay!
and I have two quick questions for anyone willing to answer them:
- Do HEAP tables in MySQL disappear on restart, requiring you to run another
CREATE TABLE query?
My quick test says no. The data all disappears, but the table still exists and can be used the next time 'round.
The simultaneous edit bug occurs very rarely (maybe once every few days),
Once every few days is SERIOUSLY WAY TO OFTEN. I was worried about it when it had happened twice ever to my knowledge... If it's happening regularly, this must be fixed immediately.
when two users pass the edit conflict check and then simultaneously try to update the article. No edit conflict is registered. My thinking went like this:
- Can't lock the whole table because it's not worth the performance
degradation for such a rare error
- Instead use MySQL's user locks to provide synchronisation
Why not try wrapping it in a BEGIN / COMMIT block? Transactions are designed for exactly this kind of thing.
- But PHP threads die all the time for no good reason, so there is a risk
that a lock will be left unreleased. Due to persistent connections, the lock will stay active indefinitely. Only the DB thread which created the lock can release it.
Threads may also get stuck in a transaction, but it _should_ be possible to get out of it by running a 'ROLLBACK' at the start of each run to cancel anything that may have gotten stuck. (Warning: untested theory!) Further, nothing has to lock; the first transaction to commit wins, and any conflicting transactions will get an error when they try to commit, or just vanish into the ether if the thread dies.
- If another thread tries to get the lock but times out, it kills the
offending DB thread
Which is now doing something unrelated serving a different web request...
-- brion vibber (brion @ pobox.com)
Brion said:
The simultaneous edit bug occurs very rarely (maybe once every few days),
Once every few days is SERIOUSLY WAY TO OFTEN. I was worried about it when it had happened twice ever to my knowledge... If it's happening regularly, this must be fixed immediately.
I just made that figure up, based on Eloquence saying it's his "favorite frequently asked question" and the vague idea that the problem would often go unreported. If we knew how big the window was we could estimate the frequency of occurrence.
Why not try wrapping it in a BEGIN / COMMIT block? Transactions are designed for exactly this kind of thing.
Oh, I thought we were still using MyISAM tables. Obviously not. Plus I was misled by the statement in the MySQL manual that you can only do full-text search with MyISAM tables. Do any of the languages still do it that way?
Just thinking about it... I don't think transactions will work anyway. The problem here is delaying SELECT statements until a transaction is complete, and transactions don't do that. Between the BEGIN statement, and the time when the COMMIT finally acquires the locks it needs, AFAIK the problem still occurs.
I guess if we use transactions, the edit will be saved in the old table properly, it just won't trigger an edit conflict. It'll look like user 2 reverted user 1's work. I think I'll stick with my way for now, assuming it works.
-- Tim Starling <tstarlingphysicsunimelbeduau>
Tim Starling wrote:
Why not try wrapping it in a BEGIN / COMMIT block? Transactions are designed for exactly this kind of thing.
Oh, I thought we were still using MyISAM tables. Obviously not. Plus I was misled by the statement in the MySQL manual that you can only do full-text search with MyISAM tables. Do any of the languages still do it that way?
The search index fields have been in a separate table for a long time specifically so we can use innodb tables for the rest. Most of the smaller languages haven't actually been converted, but English and German are at least mostly on innodb.
Just thinking about it... I don't think transactions will work anyway. The problem here is delaying SELECT statements until a transaction is complete, and transactions don't do that. Between the BEGIN statement, and the time when the COMMIT finally acquires the locks it needs, AFAIK the problem still occurs.
I guess if we use transactions, the edit will be saved in the old table properly, it just won't trigger an edit conflict. It'll look like user 2 reverted user 1's work. I think I'll stick with my way for now, assuming it works.
Hmm... Here's how the bug works now:
THREAD A THREAD B SELECT data from cur SELECT data from cur check for edit conflict check for edit conflict UPDATE cur with new data UPDATE cur with new data [overwriting what A just did] INSERT prev data to old [the previous one, not B's] INSERT prev data to old [second copy of the previous, not A's]
with the result that neither thread detects an edit conflict, old contains two copies of the previous version, and A's edit is gone.
Here's how it would work with transactions if I understand it correctly:
THREAD A THREAD B BEGIN BEGIN SELECT data from cur SELECT data from cur check for edit conflict check for edit conflict UPDATE cur with new data UPDATE cur with new data [not saved yet] [not saved yet] INSERT prev data to old INSERT prev data to old [still not saved yet] [still not saved yet] COMMIT [successfully saves edit] COMMIT [transaction is rejected, mysql reports an auto rollback] we know something's wrong start over: SELECT data from cur check for edit conflict display edit conflict screen
with the result that A's edit is saved intact and B can be shown an edit conflict screen.
-- brion vibber (brion @ pobox.com)
Brion wrote:
Hmm... Here's how the bug works now:
THREAD A THREAD B SELECT data from cur SELECT data from cur check for edit conflict check for edit conflict UPDATE cur with new data UPDATE cur with new data [overwriting what A just did] INSERT prev data to old [the previous one, not B's] INSERT prev data to old [second copy of the previous, not A's]
with the result that neither thread detects an edit conflict, old contains two copies of the previous version, and A's edit is gone.
Actually the old INSERT happens before the cur UPDATE, so the problem exists for the duration of the old query, plus the time it takes to acquire the lock in the UPDATE query. This means that there are a number of possible manifestations, for example
A B SELECT INSERT SELECT INSERT UPDATE UPDATE
or
A B SELECT SELECT INSERT INSERT UPDATE UPDATE
or even
A B SELECT SELECT INSERT UPDATE INSERT UPDATE
They all have the same effect: like you say, two copies of the previous revision in old, and B's version in cur.
Here's how it would work with transactions if I understand it correctly:
THREAD A THREAD B BEGIN BEGIN SELECT data from cur SELECT data from cur check for edit conflict check for edit conflict UPDATE cur with new data UPDATE cur with new data [not saved yet] [not saved yet] INSERT prev data to old INSERT prev data to old [still not saved yet] [still not saved yet] COMMIT [successfully saves edit] COMMIT [transaction is rejected, mysql reports an auto rollback] we know something's wrong start over: SELECT data from cur check for edit conflict display edit conflict screen
with the result that A's edit is saved intact and B can be shown an edit conflict screen.
I couldn't find any reference to this sort of behaviour in the MySQL manual, so I did a couple of tests. It seems transactions work like this:
BEGIN UPDATE cur... -- gets a lock on the cur table COMMIT -- writes to cur regardless of previous activity
I opened up a connection to the test DB in an SSH window, and did something along the lines of the following:
mysql: set autocommit=0; mysql: begin;
Then I changed [[Test2]] to "5" using my browser. This worked, because MySQL hadn't acquired a lock. Then I did this...
mysql: update cur set cur_text="6" where cur_title="Test2"; mysql: commit;
MySQL had no problem with either of the last two queries, successfully updating cur to 6. When I did this:
mysql: begin; mysql: update cur set cur_text="hello" where cur_title="Test2";
update in browser...
mysql: commit;
The attempt to update in the browser failed, returning a lock timeout error -- like that time I told you to flush the tables when you were already doing it. So having PHP threads die on us in the middle of transactions is currently very, very bad. Perhaps we should use register_shutdown_function() to rollback any active transactions. Or kill on timeout like in my scheme. Killing a thread which is serving an unrelated legitimate query is really much better than attempting to contact someone with root access in the middle of the US night.
Note that using register_shutdown_function() will effectively disable user aborts -- see chapter 20 of the PHP manual.
-- Tim Starling.
Just replying to my own message again. I didn't say what I think will happen to this bug, with transactions implemented. Here is my current understanding:
A B SELECT SELECT BEGIN BEGIN INSERT INSERT waits for lock... UPDATE ... ... COMMIT ... INSERT returns UPDATE COMMIT
So the end result is exactly the same. The danger period is still the duration of the INSERT plus part of the UPDATE.
-- Tim Starling <tstarlingphysicsunimelbeduau>
I've just checked a patch into the unstable branch which changes the order of save operations a bit in a way which should help protect against the bug, without adding any transactions or locking or whathaveyou.
The UPDATE to cur is moved up before the INSERT, *and* its WHERE condition is modified to include the timestamp of the old edit (which we've been using to detect edit conflicts, after all).
We then ask MySQL how many rows it modified in the UPDATE statement. If it's zero, we know that there was an edit conflict; we abort the rest of the save operation and (if I've got it right; I haven't tested it in actual conflict) show an edit conflict screen.
Patch affects Article.php, EditPage.php, DatabaseFunctions.php. New code should be running on test.wikipedia.org now.
This isn't complete protection; there are probably scenarios under which funny things can still happen. This also won't affect simultaneous page _creation_, though that should be fixed by adding a UNIQUE KEY index on cur_namespace and cur_title, so the second INSERT attempt would fail instead of creating an inaccessible phantom entry.
Simultaneous edit scenarios, normal:
A B SELECT cur SELECT cur UPDATE cur UPDATE cur [report conflict!] INSERT old
A B SELECT cur UPDATE cur SELECT cur [report conflict!] INSERT old
A B SELECT cur UPDATE cur INSERT old SELECT cur [report conflict!]
Simultaneous edit scenarios, pathologically slow server:
A B SELECT cur UPDATE cur [open edit _screen_] SELECT cur [start saving edit] SELECT cur INSERT old UPDATE cur INSERT old
This actually shouldn't be too wrong. Thread A would save the pre-A edit into old, B would save A's edit into old, and the history should show them all in the correct order, even if B'd insert to old actually happened before A's, they're sorted by timestamp, not insert order and will show correctly.
Updates of some of the other tables could conceivably end up out of order or overwriting each other, though.
Simultaneous edit scenarios, pathologically fast editing:
A B SELECT cur UPDATE cur INSERT old SELECT cur SELECT cur UPDATE cur [with same timestamp as B's previous edit] UPDATE cur [miss conflict due to same-second timestamp, overwrite A's edit] INSERT old INSERT old [duplicate of B's previous edit]
That could probably be protected against by checking the cur_user_text field as well as cur_timestamp.
-- brion vibber (brion @ pobox.com)
"Brion Vibber" wrote:
I've just checked a patch into the unstable branch which changes the order of save operations a bit in a way which should help protect against the bug, without adding any transactions or locking or
whathaveyou.
The UPDATE to cur is moved up before the INSERT, *and* its WHERE condition is modified to include the timestamp of the old edit (which we've been using to detect edit conflicts, after all).
We then ask MySQL how many rows it modified in the UPDATE statement. If it's zero, we know that there was an edit conflict; we abort the rest of the save operation and (if I've got it right; I haven't tested it in actual conflict) show an edit conflict screen.
You're a legend Brion. Don't ever leave us, okay?
-- Tim Starling <tstarlingphysicsunimelbeduau>
Tim Starling wrote:
You're a legend Brion. Don't ever leave us, okay?
<blush> Well, don't go lauding me until we know whether this thing helps in practise. ;)
Having seen no "this will never work you fool!" messages, I'm checking this into stable and installing it as an emergency bug fix.
-- brion vibber (brion @ pobox.com)
-- brion vibber (brion @ pobox.com) wrote: That could probably be protected against by checking the cur_user_text field as well as cur_timestamp.
You sure checking in/out is a bad idea?
-S-
__________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
Steve Vertigo wrote:
-- brion vibber (brion @ pobox.com) wrote: That could probably be protected against by checking the cur_user_text field as well as cur_timestamp.
You sure checking in/out is a bad idea?
I'm not sure I understand what you're referring to.
-- brion vibber (brion @ pobox.com)
Sorry, I missed this part of your message:
Threads may also get stuck in a transaction, but it _should_ be possible to get out of it by running a 'ROLLBACK' at the start of each run to cancel anything that may have gotten stuck. (Warning: untested theory!) Further, nothing has to lock; the first transaction to commit wins, and any conflicting transactions will get an error when they try to commit, or just vanish into the ether if the thread dies.
I think I should read up on this again. BTW, BEGIN will commit any active transactions.
- If another thread tries to get the lock but times out, it kills the
offending DB thread
Which is now doing something unrelated serving a different web request...
Good point. The script could check the HEAP table when it first acquires the connection, but it may be better to do it your way.
-- Tim Starling <tstarlingphysicsunimelbeduau>
wikitech-l@lists.wikimedia.org