|
|
Posted by Jerry Stuckle on 05/08/07 16:51
e_matthes@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 at
> http://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?
>
It's a warning that if you do use transactions they will be ignored.
But the real question here is - what problem are you trying to solve?
Two people registering at the same time with the same userid, for
instance? If so, it's not a problem - just put a unique index (or
primary key) on the user name column. The first user will get
registered, the second will fail with a duplicate id.
Or is there another problem you're trying to solve?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|