Re: AW: [PHP-DB] Concurrent update to database (PostgreSQL or MySQL) ??

Aha! A professional! There are pros and cons to the timestamp/checksum method, as I'm sure you know. So let's go over a few of them just so on the off chance that in the future someone with a similar question might actually look in the list archives. (I calculate that the chances of someone looking in the archives to reap the benefits of years of the list participants' expertise are exactly one in nine hundred twelve billion five hundred twenty four million four hundred sixteen thousand two hundred ninety seven--but I applaud that one!) PROS: * The timestamp and/or checksum method of determining whether or not a row has been changed requires only the timestamp and/or checksum be passed to the next script (the ACTION of the HTML FORM). Less moving data around means less network traffic and is good. * Easy to use. You simply compare the old timestamp/checksum to determine if the source record has been changed...the alternative is to compare each and every editable field. Comparing a list of fields is more time consuming (unless it's a list of one or two fields), and requires more programming maintenance when the list of editable fields on the HTML form changes. CONS: * The timestamp/checksum is only sensitive to the row level. If anything on the underlying source row changes, the timestamp/checksum check will indicate the underlying data has changed--even if it was an entirely unrelated update! If you have a table like this: stock (product_id, quantity_in_stock, quantity_spoiled_or_damaged, crc) and someone somewhere goes and updates the quantity_spoiled_or_damaged field (which you cannot even update from your screen) while you are updating the quantity_in_stock field, you can get the "record has been changed by someone else; do you want to continue?" message even though it wasn't the data you updated. * Cannot do the timestamp/checksum method if you don't have access to the database schema, since it relies on having a column or columns for the timestamp and/or crc values. * I personally build my update statements with PHP code. That means that I check to see what's been updated by the user/editor and build my UPDATE statement so it sends the shortest UPDATE query necessary to the database. Since I check each field to see if it's been changed anyway, this can nullify one of the PROs above and could become a CON because it could mean that programmers can list unnecessary fields in the UPDATE statement, which is unnecessary network traffic. There are other pros and cons, but that's enough to get anyone who is interested in this topic started. Doug At 11:23 PM 4/18/01 +0200, Thomas Lamy wrote: >Another way is to keep another unique value inside the table to be updated >and remember it. >When needed, I add a second unique column to the table (in my case a >char(64)) which is filled with the current timestamp and some md5 checksum. >I select this value before the update, pass it along with the HTML form, >and, before updating, I re-select the row to be updated and compare the >keys. If the comparision fails, the user is presented a warning message, >else I do the update (the user's data and a new generated stamp-value) with >the primary key _and_ the original stamp in the where clause. Then I check >if my new stamp made it to the table, or present another warning. ... snip ...