blocking mysql to read

Hi Everyone,

I have a php batch job that i'd like to run on two machines. The
batch job creates reports and sends them to my users. Im trying to
develop a way that the scripts wont send the same report to my users
twice.

Does anyone have some suggestions on how to block one script from
reading the table while the other script is reading the table?

so for instace: in my table i have :

* reports
-- id
-- status

I want job #1 to read reports where status = 0 and then send them out
then update to status = 1.

I dont want job #2 to be able to read the table in the mean time and
run the same process.

-d

Hello,

David Wang <dasn0wie@gmail.com> schrieb:
> Does anyone have some suggestions on how to block one script from
> reading the table while the other script is reading the table?

Do you understand the concept of transactions?

kind regards,
Andreas

starting a transaction here would not help me as i'm trying to
disallow a read of that specific row. the only thing that i've seen
that may work is locking the entire table, but im not sure if that is
the correct approach. suggestions?

-d

On Jun 25, 11:22 am, Andreas Stieger <Andreas.Stie...@gmx.de> wrote:
> Hello,
>
> David Wang <dasn0...@gmail.com> schrieb:
>
> > Does anyone have some suggestions on how to block one script from
> > reading the table while the other script is reading the table?
>
> Do you understand the concept of transactions?
>
> kind regards,
> Andreas

David Wang <dasn0wie@gmail.com> wrote in news:1182789456.165433.6730
@d30g2000prg.googlegroups.com:

> starting a transaction here would not help me as i'm trying to
> disallow a read of that specific row. the only thing that i've seen
> that may work is locking the entire table, but im not sure if that is
> the correct approach. suggestions?

sounds it... better remember to unlock it, though!

On Jun 25, 12:37 pm, David Wang <dasn0...@gmail.com> wrote:
> starting a transaction here would not help me as i'm trying to
> disallow a read of that specific row. the only thing that i've seen
> that may work is locking the entire table, but im not sure if that is
> the correct approach. suggestions?
>
> -d
>
> On Jun 25, 11:22 am, Andreas Stieger <Andreas.Stie...@gmx.de> wrote:
>
>
>
> > Hello,
>
> > David Wang <dasn0...@gmail.com> schrieb:
>
> > > Does anyone have some suggestions on how to block one script from
> > > reading the table while the other script is reading the table?
>
> > Do you understand the concept of transactions?
>
> > kind regards,
> > Andreas- Hide quoted text -
>
> - Show quoted text -

It sounds like a table lock (and yes, of course followed by an unlock)
is what you need. If the total time your queries take while the table
is locked is short then it shouldn't be a problem.

Just wanted to give an update for other developers out there on what I
did.

To guarantee that each process didn't mash each other, i locked the
table as well as did a select ... for update. it might be over kill
but i got the best results with this and no mashing of data and no
deadlock errors.

thanks for all the suggestions!

On Jun 28, 12:58 pm, Kevin Day <kda...@gmail.com> wrote:
> On Jun 25, 12:37 pm, David Wang <dasn0...@gmail.com> wrote:
>
>
>
> > starting a transaction here would not help me as i'm trying to
> > disallow a read of that specific row. the only thing that i've seen
> > that may work is locking the entire table, but im not sure if that is
> > the correct approach. suggestions?
>
> > -d
>
> > On Jun 25, 11:22 am, Andreas Stieger <Andreas.Stie...@gmx.de> wrote:
>
> > > Hello,
>
> > > David Wang <dasn0...@gmail.com> schrieb:
>
> > > > Does anyone have some suggestions on how to block one script from
> > > > reading the table while the other script is reading the table?
>
> > > Do you understand the concept of transactions?
>
> > > kind regards,
> > > Andreas- Hide quoted text -
>
> > - Show quoted text -
>
> It sounds like a table lock (and yes, of course followed by an unlock)
> is what you need. If the total time your queries take while the table
> is locked is short then it shouldn't be a problem.