MySQL Lock ?

[Matthias Ainsworth]

Hi,

Could someone please help me ?

I have to increase a counter for each record I insert into a table (the
value of the counter is stored in the record). I cannot use 'auto_increment'
because the counter has to start from 0 every new day. The question is : I
have to perform a SELECT query to get the current counter value and then an
INSERT to put my record, but what will happen if two (or more) users are
querying at the "same" time ??? Is there a way to "lock" the database during
the SELECT/INSERT routine ?

Thanks for your tips.

Matt.

[Pedro Graca]

Matthias Ainsworth wrote:

... Is there a way to "lock" the database during
the SELECT/INSERT routine ?

goto http://www.mysql.com/

Find and select a 'search' text box

type "lock" (without the quotes) there

Click 'Go' (or press Enter)

choose a promising link and click it :)

Can't help you more; I've never needed to do something like
that, so I don't really know if it works ...

but sure looks like it does.
--
--= my mail box only accepts =--
--= Content-Type: text/plain =--
--= Size below 10001 bytes =--

[Cameron]

Matthias Ainsworth wrote:

Hi,

Could someone please help me ?

I have to increase a counter for each record I insert into a table (the
value of the counter is stored in the record). I cannot use 'auto_increment'
because the counter has to start from 0 every new day. The question is : I
have to perform a SELECT query to get the current counter value and then an
INSERT to put my record, but what will happen if two (or more) users are
querying at the "same" time ??? Is there a way to "lock" the database during
the SELECT/INSERT routine ?

Thanks for your tips.

Matt.

I was under the impression that MySQL has some sort of queue that
prevents corruption due to multiple concurrent writes.

~Cameron

[David Mackenzie]

On Sun, 08 Feb 2004 21:26:39 +0000, Cameron <fo*@bar.invalid> wrote:

Matthias Ainsworth wrote:

Hi,

Could someone please help me ?

I have to increase a counter for each record I insert into a table (the
value of the counter is stored in the record). I cannot use 'auto_increment'
because the counter has to start from 0 every new day. The question is : I
have to perform a SELECT query to get the current counter value and then an
INSERT to put my record, but what will happen if two (or more) users are
querying at the "same" time ??? Is there a way to "lock" the database during
the SELECT/INSERT routine ?

Thanks for your tips.

Matt.

I was under the impression that MySQL has some sort of queue that
prevents corruption due to multiple concurrent writes.

If he does

SELECT current value;
INSERT record with value;

it is possible, if more than one person is using the system, for
multiple users to read the same current value and so insert the same
value.

Without using triggers or subselects I'm not sure how you could do it
reliably.

--
David ( @priz.co.uk )

[Alex Farran]

David Mackenzie writes:

On Sun, 08 Feb 2004 21:26:39 +0000, Cameron <fo*@bar.invalid> wrote:

Matthias Ainsworth wrote:

Hi,

Could someone please help me ?

I have to increase a counter for each record I insert into a table (the
value of the counter is stored in the record). I cannot use 'auto_increment'
because the counter has to start from 0 every new day. The question is : I
have to perform a SELECT query to get the current counter value and then an
INSERT to put my record, but what will happen if two (or more) users are
querying at the "same" time ??? Is there a way to "lock" the database during
the SELECT/INSERT routine ?

Thanks for your tips.

Matt.
I was under the impression that MySQL has some sort of queue that
prevents corruption due to multiple concurrent writes.

If he does SELECT current value;
INSERT record with value; it is possible, if more than one person is using the system, for
multiple users to read the same current value and so insert the same
value. Without using triggers or subselects I'm not sure how you could do it
reliably.

Um, LOCK TABLES?

http://www.mysql.com/doc/en/LOCK_TABLES.html

If you can't do that for some reason, you could always implement your
own locking mechanism around the PHP that executes the query.

--

__o Alex Farran
_`\<,_ Analyst / Programmer
(_)/ (_) www.alexfarran.com

[David Mackenzie]

On Mon, 09 Feb 2004 15:16:51 +0000, Alex Farran <al**@alexfarran.com>
wrote:

David Mackenzie writes:

Without using triggers or subselects I'm not sure how you could do it
reliably.

Um, LOCK TABLES?

http://www.mysql.com/doc/en/LOCK_TABLES.html

Oops! Didn't know about that.

--
David ( @priz.co.uk )