Re: Select for update

[prev] [thread] [next] [Date index for 2004/07/19]

--Apple-Mail-1-267288207
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=WINDOWS-1252;
	format=flowed

Hi Ian.

I have successfully used this technique with MySQL:

__PACKAGE__->add_constructor( 'next_available' =3D> qq{
         company_id =3D ? AND application_id IS NULL LIMIT 1 FOR UPDATE
     });

but i have turned off autocommit (generally with a local=20
$class->db_Main->{auto_commit} call).

Be careful to either rollback or commit your trans, though, so your=20
lock isn't held out there for too long.

As an aside, should there be something equivalent to=20
$class->begin_trans, instead of resorting to setting a DBH variable to=20=

start transactions?


-jason scott gessner
     jason@xxxxxxxx.xxx

On Jul 19, 2004, at 8:15 AM, Ian McDonald-ONLINE wrote:

> Hi,
>
> I'd like to make some of my units of work (collections of updates=20
> across multiple tables) atomic, and plan to do this by issuing "select=20=

> =85 for update" commands instead of simple selects.
>
> Am I right to think I need to overload private methods or use add_sql=20=

> for this?
>
> Thanks,
>
> Ian
>
> --=20
>  Dr Ian McDonald
> Software Engineer, Interactive Drama & Entertainment
>  Conquer the Earth! - The Doctor Who monsters game
>  The Rome Game: Do as the Romans do. Lie, cheat, and steal.
>  A300, 56-58 Wood Lane (CH), London W12 7SB
>  020 822 56162
>  ian.mcdonald@xxx.xx.xx / "Ian McDonald-ONLINE" (not "Ian MacDonald")
>
>
> http://www.bbc.co.uk/ - World Wide Wonderland
>
> This e-mail (and any attachments) is confidential and may contain
> personal views which are not the views of the BBC unless specifically
> stated.
> If you have received it in error, please delete it from your system.
> Do not use, copy or disclose the information in any way nor act in
> reliance on it and notify the sender immediately. Please note that the
> BBC monitors e-mails sent or received.
> Further communication will signify your consent to this.=20=

--Apple-Mail-1-267288207
Content-Transfer-Encoding: quoted-printable
Content-Type: text/enriched;
	charset=WINDOWS-1252

Hi Ian.


I have successfully used this technique with MySQL:


__PACKAGE__->add_constructor( 'next_available' =3D> qq{

        company_id =3D ? AND application_id IS NULL LIMIT 1 FOR UPDATE

    });


but i have turned off autocommit (generally with a local
$class->db_Main->{auto_commit} call).


Be careful to either rollback or commit your trans, though, so your
lock isn't held out there for too long.


As an aside, should there be something equivalent to
$class->begin_trans, instead of resorting to setting a DBH variable to
start transactions? =20



-jason scott gessner

    jason@xxxxxxxx.xxx


On Jul 19, 2004, at 8:15 AM, Ian McDonald-ONLINE wrote:


=
<excerpt><fontfamily><param>Arial</param><smaller>Hi,</smaller></fontfamil=
y>=20


<fontfamily><param>Arial</param><smaller>I'd like to make some of my
units of work (collections of updates across multiple tables) atomic,
and plan to do this by issuing "select =85 for update" commands instead
of simple selects.</smaller></fontfamily>


<fontfamily><param>Arial</param><smaller>Am I right to think I need to
overload private methods or use add_sql for
this?</smaller></fontfamily>=20


<fontfamily><param>Arial</param><smaller>Thanks,</smaller></fontfamily>=20=



<fontfamily><param>Arial</param><smaller>Ian</smaller></fontfamily>=20


=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>--</x-tad-smaller></smaller></color></fontfamily></excer=
pt><excerpt>=20


=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>Dr
Ian
=
McDonald</x-tad-smaller></smaller></color></fontfamily></excerpt><excerpt>=
=20

=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>Software
Engineer, Interactive Drama &
=
Entertainment</x-tad-smaller></smaller></color></fontfamily></excerpt><exc=
erpt>=20


=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>Conquer
the Earth! - The Doctor Who monsters
game</x-tad-smaller></smaller></color></fontfamily></excerpt><excerpt>=20=



=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>The
Rome Game: Do as the Romans do. Lie, cheat, and
steal.</x-tad-smaller></smaller></color></fontfamily></excerpt><excerpt>=20=



=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>A300,
56-58 Wood Lane (CH), London W12
7SB</x-tad-smaller></smaller></color></fontfamily></excerpt><excerpt>=20


=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>020
822
56162</x-tad-smaller></smaller></color></fontfamily></excerpt><excerpt>=20=



=
<fontfamily><param>Arial</param><color><param>8080,8080,8080</param><small=
er><x-tad-smaller>ian.mcdonald@xxx.xx.xx
/ "Ian McDonald-ONLINE" (not "Ian
=
MacDonald")</x-tad-smaller></smaller></color></fontfamily></excerpt><excer=
pt>=20



http://www.bbc.co.uk/ - World Wide Wonderland


This e-mail (and any attachments) is confidential and may contain

personal views which are not the views of the BBC unless specifically

stated.

If you have received it in error, please delete it from your system.=20

Do not use, copy or disclose the information in any way nor act in

reliance on it and notify the sender immediately. Please note that the

BBC monitors e-mails sent or received.=20

Further communication will signify your consent to this. </excerpt>=

--Apple-Mail-1-267288207--