|
|
Posted by e_matthes on 05/09/07 15:03
On May 9, 3:27 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> 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.
>
> OK, well, you NEVER want to hold ANY locks across user interfaces.
> That's not true for just MySQL or the web, but any database on any
> application.
I don't think I've said anything about holding a lock across a user
interface. All of my coding completes its database work before
serving the page.
> For instance - what happens if you get a lock and display the page, but
> your user closes his browser? You never get notified.
That's fine. The lock is released, and I code defensively for an
incomplete series of database operations, while working with a
database engine that does not support transactions.
> Also, web pages are transactional - once you've sent the page, all
> resources (except for session variables) are released. This would
> include locks.
This is good. I don't plan to hold any locks after I've sent the
page. Do you see anywhere in what I've written that seems to indicate
holding a lock after sending a page?
>
> You're correct - you need to think of a different way of doing it.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================
Navigation:
[Reply to this message]
|