Using HEAP tables - Wikitech-l

16 Aug 2003


      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![#0HC"0EW9D1E8G5G*" B>R1F;F%M97TZ4U%,.B![)'-Q;'U<;B(L('1R
M=64@*3L-"@E](&5L<V4@>PT*(PD)=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