Posted by Norman Peelman on 05/08/07 03:24

e_matthes@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 like
www.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

[Back to original message]