Re: Alternative MySQL PEAR DB sequence behavior

uksi@uksiland.com (Oleg Rekutin) wrote in news:Xns90E697DCA66F4ukstah@216.92.131.4: > Uh oh, just as I began implementing LOCK TABLES during the conversion, > I realized that we really should not it because LOCK TABLES unlocks all > previous locked tables and commits any active transactions. This will > mess up any application that attempts to retrieve the next ID during a > transaction or if it locked some other tables in the system. > > I'm thinking of ways how to eliminate race conditions and not break any > table locks or transactions. Alright, attached (hopefully the attachment goes thru, trying for the first time w/ this client) is a patch that: - fixes new IDs to start from 1 - puts a user-level lock on the conversion to prevent a race condition where two threads attempt to convert the IDs table at the same time Now, there are a few catches w/ the conversion. I avoid using LOCK TABLES, and instead use GET_LOCK, which does not commit any current transactions, nor does it abort any current table locks. It does clear any current user- level locks, but that's better than no lock at all. Possible multi-threading issues: 1) Two threads attempt to convert the table at the same time. Outcome: one of the threads will perform the conversion, then the second one will either time out waiting on the conversion to perform (unlikely) or will attempt to perform the conversion again (likely). The latter will have no effect (DELETE will affect 0 rows). 2) One thread begins conversion (pre-lock), while another thread attempts to UPDATE to get the nextId. Outcome: the 2nd thread's UPDATE will fail due to duplicates and it will attempt to perform the conversion, with the case #1 occurring. 3) One thread is after SELECT highest ID but before DELETE all but highest ID, while another thread attempts to UPDATE to get the nextId. Outcome: the 2nd thread's UPDATE will fail due to duplicates and it will attempt to perform the conversion, moving on to case #1. So I think we're in good shape... - Oleg begin 644 mysql-diff.txt M+2TM(&]L9"UM>7-Q;"YP:')4W5N($IU;"R,BQ,SHS-SHT,"R,#`Q#0HK M*RL@;7ES<6PN<&AP"5-U;B!*=6P@,C(@,38Z,C4Z,#@,CP,0T*0$`@+3,X M,RPQ,RK,S@S+#,R($!#0H@("@("@("@("@("`@("1R97-U;'0M/F=E M=$-O9&4H*2]/2!$0E]%4E)/4E].3U-50TA404),12D@>PT*("@("@("@ M("@("@("D<F5P96%T(#T@,3L-"B@("@("@("@("@("`@)')E<W5L M="]("1T:&ES+3YC<F5A=&5397%U96YC92@D<V5Q7VYA;64I.PT**R@("`@ M("@("@("@("O+R!3:6YC92!C<F5A=&5397%U96YC92!I;FET:6%L:7IE M<R!T:&4@240@=&\@8F4@,2P@#0HK("@("@("@("@("`@("\O('=E(&1O M(&YO="!N965D('1O(')E=')I979E('1H92!)1"!A9V%I;B`H;W(@=V4@=VEL M;"!G970@,BD-"B@("@("@("@("@("@:68@*$1".CII<T5R<F]R*"1R M97-U;'0I*2![#0H@("@("@("@("@("@("@("!R971U<FX@)')E<W5L M=#L-"BL@("@("@("@("@("@?2!E;'-E('L-"BL@("@("@("@("`@ M("@("@("\O($9I<G-T($E$(&]F(&$@;F5W;'D@8W)E871E9"!S97%U96YC M92!I<RQ#0HK("@("@("@("@("@("@("!R971U<FX@,3L-"B@("`@ M("@("@("@("@?0T*("@("@("@("@('T@96QS96EF("A$0CHZ:7-% M<G)O<B@D<F5S=6QT*2F)@T*("@("@("@("@("@("`D<F5S=6QT+3YG M971#;V1E*"D@/3T@1$)?15)23U)?04Q214%$65]%6$E35%,I('L-"B@("@ M("@("@("@("@+R\@375S="!B92!U<VEN9R!O;&0@<V5Q=65N8V4@96UU M;&%T:6]N(&EM<&QE;65N=&%T:6]N+T*+2@("@("@("@("@("`O+R!C M;&5A;B!U<"!T:&4@9'5P97,-"BL@("@("@("@("@("`@+R\@=V4@;F5E M9"!T;R!C;&5A;B!U<"!T:&4@9'5P97,N#0HK#0HK("@("@("@("@("`@ M("\O($]B=&%I;B!A('5S97(M;&5V96P@;&]C:RXN+B!T:&ES('=I;&P@<F5L M96%S92!A;GD@<')E=FEO=7,-"BL@("@("@("@("@("`@+R\@87!P;&EC M871I;VX@;&]C:W,L(&)U="!U;FQI:V4@3$]#2R!404),15,L(&ET(&1O97,@ M;F]T(&%B;W)T#0HK("@("@("@("@("`@("\O('1H92!C=7)R96YT('1R M86YS86-T:6]N(&%N9"!I<R!M=6-H(&QE<W,@9G)E<75E;G1L>2!U<V5D+@T* M*R@("@("@("@("@("D<F5S=6QT(#T@)'1H:7,M/F=E=$]N92@B4T5, M14-4($=%5%],3T-+*"<D>W-Q;GU?<V5Q7VQO8VLG+#$P*2(I.PT**R@("@ M("@("@("@("!I9BH1$(Z.FES17)R;W(H)')E<W5L="DI('L@#0HK("`@ M("@("@("@("@("@("!R971U<FX@)')E<W5L=#L-"BL@("@("@("@ M("@("@?0T**R@("@("@("@("@("!I9BH)')E<W5L="]/2P*2![ M#0HK("@("@("@("@("@("@("`O+R!&86EL960@=&\@9V5T('1H92!L M;V-K+"!C86XG="!D;R!T:&4@8V]N=F5R<VEO;BP@8F%I;T**R@("@("@ M("@("@("@("@+R\@=VET:"!A($1"7T524D]27TY/5%],3T-+140@97)R M;W(-"BL@("@("@("@("@("@("@(')E='5R;B`D=&AI<RT^;7ES<6Q2 M86ES945R<F]R*#$Q,#I.PT**R@("@("@("@("@("!]#0HK#0H@("`@ M("@("@("@("@("1H:6=H97-T7VED(#T@)'1H:7,M/F=E=$]N92@B4T5, M14-4($U!6"AI9"D@1E)/32D>W-Q;GU?<V5Q(BD[#0H@("@("@("@("`@ M("@(&EF("A$0CHZ:7-%<G)O<B@D:&EG:&5S=%]I9"DI('L-"B@("@("@ M("@("@("@("@(')E='5R;BD:&EG:&5S=%]I9#L-"D!("TT,#$L-B`K M-#(P+#$U($!#0H@("@("@("@("@("@(&EF("A$0CHZ:7-%<G)O<B@D M<F5S=6QT*2D@>PT*("@("@("@("@("@("@("`@<F5T=7)N("1R97-U M;'0[#0H@("@("@("@("@("@('T-"BL-"BL@("@("@("@("@("@ M+R\@268@86YO=&AE<B!T:')E860@:&%S(&)E96X@=V%I=&EN9R!F;W(@=&AI M<R!L;V-K+"-"BL@("@("@("@("@("@+R\@:70@=VEL;"!G;R!T:')U M('1H92!A8F]V92!P<F]C961U<F4L(&)U="!W:6QL(&AA=F4@;F\-"BL@("`@ M("@("@("@("@+R\@<F5A;"!E9F9E8W0-"BL@("@("@("@("@("`@ M)')E<W5L="`]("1T:&ES+3YG971/;F4H(E-%3$5#5"!214Q%05-%7TQ/0TLH M)R1[<W%N?5]S97%?;&]C:R<I(BD[#0HK("@("@("@("@("`@(&EF("A$ M0CHZ:7-%<G)O<B@D<F5S=6QT*2D@>R-"BL@("@("@("@("@("@("`@ M(')E='5R;BD<F5S=6QT.PT**R@("@("@("@("@("!]#0HK#0H@("`@ M("@("@("@("@("\O(%1H:7,@<VAO=6QD(&MI;&P@86QL(')O=W,@97AC M97!T('1H92!H:6=H97-T+"!N;W<@=V4-"B@("@("@("@("@("@+R\@ M8V%N('1R>2!A9V%I;@T*("@("@("@("@("@("D<F5P96%T(#T@,3L- M"D!("TT,C8L-RK-#4T+#<@0$-"B@("@("@(&EF("A$0CHZ:7-%<G)O M<B@D<F5S*2D@>PT*("@("@("@("@(')E='5R;BD<F5S.PT*("@("`@ M("@?0T*+2@("@("@+R\@;F5X=$ED(&-A;&P@=VEL;"!G96YE<F%T92!) M1"Q#0HK("@("@("O+R!I;G-E<G0@>6EE;&1S('9A;'5E(#$L(&YE>'1) M9"!C86QL('=I;&P@9V5N97)A=&4@240@,@T*("@("@("`@<F5T=7)N("1T M:&ES+3YQ=65R>2@B24Y315)4($E.5$\@)'MS<6Y]7W-E<2!604Q515,H,"DB /*3L-"B@("@?0T*(`T* ` end