Re: Get Confused : Use Transaction or not

Hi Phil 1. The InnoDB engine type (for mysql) uses row locking for its transactions by default. (http://dev.mysql.com/doc/mysql/en/innodb-transaction-model.html) 2. Transactions offer an all or nothing shot at entering data into [multiple] tables. If the update/insert fails at any time before the commit is issued, the entire set of sql calls are rolled back so that no changes are made. They are not 'required' for data management, but they ensure a level of safety when using mutliple statements to create one record. hth Bastien

From: Phil <phil.cyc@msa.hinet.net> To: Bastien Koert <bastien_k@hotmail.com> CC: php-db@lists.php.net Subject: Re: [PHP-DB] Get Confused : Use Transaction or not Date: Wed, 27 Apr 2005 02:05:57 +0800 Thanks for your suggestion,Bastien !! You guide me a new way to thought how to design it. Before receiving your response,there are many question marks in my mind.In my opinion,lock and transaction are the key points I focused on.Privilege of locking table can be granted to database owner in MySQL.However,there are two things I doubted as follows: 1.Can database owner who has been given lock privilege lock row?? 2.I am not sure that using transaction as queries in PHP script is the correct way to keep data's unity. Regards, Phil Bastien Koert wrote: 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@msa.hinet.net> To: php-db@lists.php.net Subject: [PHP-DB] 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