|
|
Posted by Jerry Stuckle on 05/09/07 11:27
e_matthes@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.
For instance - what happens if you get a lock and display the page, but
your user closes his browser? You never get notified.
Also, web pages are transactional - once you've sent the page, all
resources (except for session variables) are released. This would
include locks.
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.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|