|
|
Posted by e_matthes on 05/08/07 23:47
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.
Navigation:
[Reply to this message]
|