|
|
Posted by shimmyshack on 05/08/07 10:45
On May 8, 4:24 am, Norman Peelman <npeel...@cfl.rr.com> wrote:
> e_matt...@hotmail.com wrote:
> > Hello everyone,
>
> > I have read many threads about concurrency issues, and I think I
> > understand some of the pieces, but not the whole picture.
>
> > I believe I am like many people using php: developing a personal
> > website on a windows machine, that will be hosted on a shared linux
> > server. Financial reality makes it impossible to think of a dedicated
> > server, and I'm not ready to jump into developing on a linux box.
>
> > I am looking at how to efficiently code some database operations,
> > while using a MyISAM mySQL database. The options seem to be:
>
> > 1. Use flock() in the code to lock a semaphore file. This sounded
> > fine, until I read that different systems respond to flock()
> > differently, and we get into making temporary directories and checking
> > if they exist. This seems difficult to develop on a local machine,
> > then play around on the shared server environment.
>
> > 2. Use LOCK TABLES in mysql. This would be fine, but results in
> > locking tables more often and more restrictively than they need to be
> > locked.
>
> > 3. Use a lockStatus table. I make a row that's called
> > "registerNewUser" and set it to "locked"; do my work; then set
> > "registerNewUser" back to "unlocked". This is fine, but if the user
> > clicks out or the steps are not completed, "registerNewUser" remains
> > set to "locked".
>
> > What I am leaning towards is a LOCK TABLES approach that tries to
> > mimic the semaphore file approach.
> > A- I create an empty (semaphore) table called registerNewUser.
> > B- I get a write lock on this table, and a read lock on all tables
> > needed for the subsequent work.
> > C- I release the locks.
>
> > Any other operation that would conflict with registering a new user
> > would also get a lock on the semaphore table registerNewUser. What I
> > think this achieves is that any other user can read from the tables
> > that are being written to (which is not usually a problem - if it is,
> > that user gets a read lock on the appropriate semaphore table). Also,
> > if the connection is lost, the lock is released.
>
> > Can anyone shoot holes in this thinking? Am I right in thinking this
> > reasonably imitates the semaphore file approach?
>
> Maybe over thinking a bit... the idea behind LOCK TABLES is that
> MySQL will que the requests as they come in. You want to spend as little
> time as possible processing locked tables as you can. Don't do anything
> that will take considerable amounts of time. Sometimes you don't even
> need LOCK TABLES. My process is (simplified):
>
> table users = id big int, email char(), first char(), last char(),
> status enum('U','R','D'), date date()
>
> 1) new user registers...
> 2) is there a match in the db already? ... options
> 3) no match found, add info, get LAST_INSERT_ID(). status is 'U'
> unregistered.
> 4) continue with other processing with newly acquired id
> 5) at this point you have a tag (id) to the person. I usually do email
> verification such as a clickable/cut/paste url likewww.yourdomain.com/complete_registration.php?id=????
> 6) when the person follows the link, I UPDATE their account to 'R' for
> registered. I have ('U','R','D'). 'D' for deletable. Unregistered
> accounts over ??? days old.
>
> INSERTs are atomic (won't conflict with each other) and LAST_INSERT_ID()
> is connection specific (no cross-over). Basically, no one ends up with
> the same id. And besides, your options 1 and 2 only provide the
> functionality of what MySQL is already doing for you behind the scenes.
>
> Norm
Use PDO.
$db->beginTransaction();
$db->exec($query);
$db->commit();
if( some condition )
{
$db->rollBack();
}
but i dont really see you have a massive problem, its not about shared
hosting, its about many concurrent users of yours, operating on the
same table. Fix it with logic in your code using PDOs abilities.
Navigation:
[Reply to this message]
|