Thread: DBI/DBD::Pg and transactions

Hopefully the original post went astray and this isn't a duplicate.  I
don't see it in the archive so I'll assume I have a bogus SMTP setup at
work for my home account.


I've tried the Pg docs, DBI book, and Google searches but haven't found
anything useful on this topic.

I realize this is more a DBI question so if there is a better place to
ask please point me towards it.

Without lots of details here is what I'm trying to determine.

Will a

    $sth->execute()

of a "SELECT ... FOR UPDATE" statement allow me to update the selected
records using

    $uth->execute( ... )

if $sth and $uth are both created/prepared from the same database handle
"$dbh"?


Thanks,
Rod
--

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>Will a $sth->execute()
> of a "SELECT ... FOR UPDATE" statement allow me to update the selected
> records using
> $uth->execute( ... )
> if $sth and $uth are both created/prepared from the same database handle
> "$dbh"?

You are allowed to update either way, but the lock created by the first
execute will be in place for the second, as long as those statement handles
are from the same $dbh (database handle). In general, connecting via DBI
always gives you one connection unless you explicitly create a second one
by calling DBI->connect.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200707181533
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


-----BEGIN PGP SIGNATURE-----

iD8DBQFGnmspvJuQZxSWSsgRAxjIAJ0TRN5bTs9s1/Z3/YC/rzGdpEhWiACg11Ca
sdXpTplc1laTXywTrd+8nBw=
=5iA0
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> Will a $sth->execute()
>> of a "SELECT ... FOR UPDATE" statement allow me to update the selected
>> records using
>> $uth->execute( ... )
>> if $sth and $uth are both created/prepared from the same database handle
>> "$dbh"?
>
> You are allowed to update either way, but the lock created by the first
> execute will be in place for the second, as long as those statement handles
> are from the same $dbh (database handle).

So how would I update selected rows (tuples) from the first execute a
"SELECT ... FOR UPDATE" with the second.


*The rest of the story:*

This script will be run via cron.

It will query a table for orders that are ready.  It may take longer
than the cron interval to process the orders and some of the orders may
be left as ready ie. not done.

If the order gets done I need to update it's row (tuple) as done.

In the mean time if the script gets triggered again and the first
instance isn't finished the second needs to not be able to select those
records already being handled.

Which then, suddenly like a smack on top of the head, makes me think of
how to select using the same "SELECT ... FOR UPDATE" query only those
non-locked records.

Man this is getting deeper every time I look at it.


Rod
--
  In general, connecting via DBI
> always gives you one connection unless you explicitly create a second one
> by calling DBI->connect.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> End Point Corporation
> PGP Key: 0x14964AC8 200707181533
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
>
>
> -----BEGIN PGP SIGNATURE-----
>
> iD8DBQFGnmspvJuQZxSWSsgRAxjIAJ0TRN5bTs9s1/Z3/YC/rzGdpEhWiACg11Ca
> sdXpTplc1laTXywTrd+8nBw=
> =5iA0
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/

On Wednesday 18 July 2007 14:29, "Roderick A. Anderson" <raanders@acm.org>
wrote:
> In the mean time if the script gets triggered again and the first
> instance isn't finished the second needs to not be able to select those
> records already being handled.

select for update won't do that. It will sit waiting for locks on the same
rows the first process is handling.

--
"Remember when computers were frustrating because they did exactly what
you told them to?  That actually seems sort of quaint now." --J.D. Baldwin

Alan Hodgson wrote:
> On Wednesday 18 July 2007 14:29, "Roderick A. Anderson" <raanders@acm.org>
> wrote:
>> In the mean time if the script gets triggered again and the first
>> instance isn't finished the second needs to not be able to select those
>> records already being handled.
>
> select for update won't do that. It will sit waiting for locks on the same
> rows the first process is handling.

Let's see how do I put it ... Da[r|m]n!  But I now remember reading
something to this effect in one of my PG books or the on line docs.

Some other method is now in order.


Thanks to all for the thoughts and ideas.

I'll post my solution ... when I figure one out!


Rod
--

On Thursday 19 July 2007 00:03:19 Roderick A. Anderson wrote:


> I'll post my solution ... when I figure one out!

You could, in the script from cron:
1 - check for the presence of rows in a 'alive_scripts_table'
if any , then exit, and go for the next run,
alternativly, check that pid in 'ps -ef' so there actually is a script running
2 - create a record in a table, ie insert your pid into a alive_script_table,
3- do your thing
4 - delete your row from the alive_script_table

or use above with a file instead

or just check with ps/tasklist/wmic




--
/Björn