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.


Subject: [PHP-DB] Get Confused : Use Transaction or not Date: Mon, 25 Apr 2005 16:07:36 +0800


I want to develop a multiuser system using PHP(5.04),MySQL(4.1.10) and
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 ?


PHP Database Mailing List (
To unsubscribe, visit:

-- PHP Database Mailing List ( To unsubscribe, visit:

Reply via email to