Hi.
Please stop cross-posting and use the appropriate forum.
On Wed, Nov 21, 2001 at 03:27:23PM -0700, [EMAIL PROTECTED] wrote:
>
> (PHP-DB folks, ignore this top question - unless you want to answer)
>
> Which type of DB/Table provides table locking while a process is
> altering data?
ISAM and MyISAM. BDB has page locking. InnoDB has row locking.
> I don't care for locking while using SELECTs, but I do
> need locking when something's being INSERTed or UPDATEd, so I won't get
> two processes trying to do the same darned thing at the same time.
Well, the above was about implicit locking. If you use LOCK TABLE, you
will always get the whole table locked, I think.
> (MySQL folks, ignore this bottom question - unless you want to
> answer)
>
> I'm trying to go through a table and check for a particular
> information. If it doesn't exist, then go ahead and issue an INSERT
> with the relevant data. The next time another process comes along,
> it'll obviously find the information and issue an UPDATE instead. But,
> what happens if I get two processes running at the same time, trying to
> find information that does not exist yet?
You will get concurrency problems.
More serious answer: There are several solutions to this problem. One
is the beforementioned LOCK TABLE.
> If both issue a SELECT and find the information not there, both will
> try to issue an INSERT, where really I want one of those processes to
> wait for the first one to finish doing whatever it needs to do, prior to
> continuing. (all of this is being done in PHP by the way). Does anyone
> have any suggestions for this scenario (or am I just blowing smoke out
> my arse and there's something else, internally, that I'm not aware of?)
No. You have to program your application to take care of this. How
should MySQL know that are want to issue a INSERT after the SELECT?
> When (and how) to issue some sort of "wait" state for one process while
> the other's finishing what it needs to do, then restart the second
> process (which should re-check for existing information, and not blindly
> issue another INSERT right after the first one).
Another method is assure uniqueness via keys and to simply issue the
INSERT and retry with an UPDATE if the INSERT failed (because the row
already existed). In this case you don't need LOCK TABLE.
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
