|
|
Posted by shimmyshack on 05/08/07 15:59
On May 8, 4:10 pm, e_matt...@hotmail.com wrote:
> On May 8, 2:45 am, shimmyshack <matt.fa...@gmail.com> wrote:
>
>
>
> > 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.
>
> That is very interesting; I had not heard of PDO until now. I mention
> shared hosting because the host I'm most familiar with doesn't allow
> certain db engines, because of the potential for bad coding to eat up
> resources with that engine. So I'm stuck for now with MyISAM (no
> transaction support). I find this warning when reading about PDO athttp://us.php.net/manual/en/ref.pdo-mysql.php:
>
> "Beware: Some MySQL table types (storage engines) do not support
> transactions. When writing transactional database code using a table
> type that does not support transactions, MySQL will pretend that a
> transaction was initiated successfully. In addition, any DDL queries
> issued will implicitly commit any pending transactions."
>
> Does this mean PDO emulates transactions for engines that do not
> support transactions? Or is it a warning not to try using
> transactions in PDO when using an engine that doesn't support
> transactions?
ah MyISAM, yes sorry I missed that no go there I am afriad, change
host and start using InnoDB if you need transactions which are nice.
PDO doesnt emulate transactions unfortunately, I am sure you could
write some code that did, but then thats your actual problem! I think
this is all solved in the app myseld, but go with a host that isnt so
restrictive, that rolls with the times and uses their knowledge to
help not hinder you, solving the problems they come across as part of
the service, rather than saying "oh no cant let you do that".
Navigation:
[Reply to this message]
|