How can i ensure that one record is not updated by more than one user at the same time. e.g. suppose i have a product table with fields product ID, product name, rate. One user has taken up record having product id = 5 for updating the rate. Another user has taken up the same record with product id = 5 for updating the product name. How can I ensure that the commit for change of product rate is not overwritten by the commit for change of product name ?
Depends on how you are doing the update. Synchronization may not help. User 1 is browsing the row and making changes to one field. User 2 is making changes to another field. When you save the row, you save the wohole row not just the changed fields, Last in wins. What you can do is save the last update date/time as part of the record that you are showing to the user. If the last update date/time has changed since you showed the row to he user, then you know that someone else has changed the row. So then you can abort the update and show the user the changed row.
Thanks for your replies folks ! I have found a solution to the problem. The one mentioned by Thomas Paul will work. But, there's a slight catch in this one. There's always a chance that one user may save without making changes. In that case, the update date/time will be changed & another user who has actually changed the values will get a message that the record is changed & will have to requery the record only to find that there are no changes to it & his work of having made changes is futile. The solution i have in mind is - before commiting the changes to the table, compare the current contents of that record with the ones sent by user. If there is even one change, then flash a message that the record has been changed & requery to see latest one. The advantage of this that the earlier problem of incorrect message of the record being changed is not displayed & genuine changes are committed & secondly, if at all user insists on getting a list of fields changed, well, it can be generated. So folks, thanks once again for your answers. I hope my reply has also given you some more ideas about this thing. In case you have any more ideas about this one, please post your replies. I am looking for an efficient method. If one exists, it can be implemented.
Joined: May 05, 2000
There's always a chance that one user may save without making changes.
The answer, of course, is to not update the row if there are no changes. [This message has been edited by Thomas Paul (edited December 08, 2000).]