|
|
Posted by e_matthes on 05/09/07 06:29
On May 8, 3:47 pm, e_matt...@hotmail.com wrote:
> On May 8, 6:59 am, shimmyshack <matt.fa...@gmail.com> wrote:
>
>
>
> > 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".
>
> Back in school I played a game where everyone wrote a sentence on a
> piece of paper. Then everyone passed their paper to the right, and
> everyone added a sentence to the paper in front of them. By the time
> the papers made it back to the original people, you had a bunch of
> stories that no one could have predicted. Let's say we want to set up
> a page to make these stories.
>
> Only registered users can participate, and they can only contribute
> one sentence to each story. Each story is fairly long, say 500
> sentences, and you want to make 500 stories altogether. Here's how I
> would store each sentence:
>
> 1 - Verify it's a sentence, sanitize input, all the usual pre-database
> work.
> 2 - Lock on the empty semaphore table as write, read-lock on all other
> tables necessary.
> 3 - Pull the last sentence stored, make sure it is the same last
> sentence the user built off of.
> 4 - Store the current sentence.
> 5 - If this was the 500th sentence, create a new table to hold the
> next story.
> 6 - Release table locks.
>
> This is not the scenario I am implementing (although it would be
> fun!), but it gets at similar issues - checking against a previous
> record before writing, checking to see if it is time for a new table
> to be created. I know there are many ways to code this, as well, and
> I will look around at hosts which offer transaction-support db
> engines. But going back to the original question, would the semaphore-
> table approach work reasonably? Or are there holes in this logic?
>
> Thanks for the feedback.
I answered my own question about the semaphore table. When you issue
a lock, you can't work with any tables you haven't locked. Write-
locking a semaphore table and read-locking the other tables does not
work, because then you can't write to those other tables. So, you
have to use a write lock on all the tables you'd write to anyway,
which brings us back to the over-restrictive lock tables approach.
Time to revisit one of the other approaches, which will probably mean
more efficient database design in the first place.
Navigation:
[Reply to this message]
|