Posted by Jerry Stuckle on 05/09/07 22:52

e_matthes@hotmail.com wrote:
> 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.
>>

No, but you weren't clear in what you were trying to do, either.

And I really think you're going overboard on this - even without
transactions, I doubt you'll have enough traffic on your site that
you'll have problems.

And if you try to handle every possible contingency you'll quickly go crazy.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[Back to original message]