RE: Get Confused : Use Transaction or not

["Bastien Koert" <bastien_k@xxxxxxxxxxx>]

Transactions are not necessarily the answer, though they are useful. What 
you need to do is decide if your design should allow multiple users to 
access the same record at the same time. Concurrency is the real issue, not 
the use of transactions. If users should not be allowed access to the same 
row at the same time (as if becomes difficult to manage whose changes should 
be made to 'stick'), then you should flag the row in some manner to allow 
other users to 'see' the data (read consistency) but not to change the data. 
and they should be informed that another user has locked the record against 
changes.

One option is to consider adding a row in the table called 'locked' which is 
a tinyint with values of 1 for locked and 0 for open, and a datestamp of 
when its status changed. When the record is locked the 1 is returned in the 
dataset and a message is passed to the user to notify them the record is 
locked against their changes (perhaps the record only gets displayed in a 
non-editable form like a straight echo or as disabled imput elements.)

When the user who locked the record, is finished with the record, update 
that col to set it to 0 to allow other users access to that record.

The thing to watch out for here is that its possible for that record to 
become locked indefinitely if that user doesn't finish with that record. 
Like if the user goes home and simply closes the browser or never hits the 
submit button to make the changes. You will need a mechanism to unlock those 
records automatically if possible thru a cron job or a scheduled task. You 
would need to decide how long a user to hold a record locked, whether its 20 
minutes or a few hours, whatever, but at predefined intervals, a script 
should be run to checked for those locked records and unlock them when they 
exceed the time out value.

Bastien

> From: Phil <phil.cyc@xxxxxxxxxxxxx>
> To: php-db@xxxxxxxxxxxxx
> Subject:  Get Confused : Use Transaction or not Date: Mon, 25 Apr 
> 2005 16:07:36 +0800
>
> Hi,
>
> I want to develop a multiuser system using PHP(5.04),MySQL(4.1.10) and
> PEAR(DataObject).
> There is only one MySQL account for PHP to operate data.
> All user's information will be stored in database,include id,passwd and 
> etc.
> However,I get confused when I write queries in PHP script.
> Because one data row may be modified by different user at the same time.
>
> Should I use Transaction in my queries ?
>
> Regards,
> Phil
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php