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

> Doug Semig [mailto:dougslist@semig.com] wrote: > > Aha! A professional! Thanx :-) > > 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!) I completely agree :-) > > 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. Actually, I prefer to not abuse the checksum and always use an integer primary key. > > * Easy to use. You simply compare the old timestamp/checksum to > determine if the source record has been changed... > [...] > > 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. I agree. As stated in my previous posting, I only use this approach when I have to completely disable concurrent updates. And you are right: this is no milk-and-honey-machine. It's just yet another mechanism. You always have to choose the right algorithm, depending on your needs (performance- and application-wise). > > * 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. Yep. (But this is a developers list) > * 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. So do I. > 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. I prefer to update complete sets of data (as long as it's not against performance, like updating some large BLOBs with itselves). > > There are other pros and cons, but that's enough to get anyone who is > interested in this topic started. > Thanks for your thoughts! Thomas > 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 ... >