Re: update impossible in spite of row locking

Habichtsberg, Reinhard wrote: > Silly question: where can I find instructions how to implement "optimistic > locking" with IDS 11.50. My search in the manuals and on the IBM-Website was > rather unsuccessful. Look for EVERCOLS. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Obnoxio The Clown wrote: > Habichtsberg, Reinhard wrote: >> Silly question: where can I find instructions how to implement "optimistic >> locking" with IDS 11.50. My search in the manuals and on the IBM-Website was >> rather unsuccessful. > > Look for EVERCOLS. > VERCOLS... IDS 11.50 introduced the ability to have updatable secondaries. The secondary servers send the update request to the primary. Since they may not be exactly in sync, it is required that the engine check that the row data you get (and try to update) on the secondary is equal to the current data in the primary. This can be done by comparing full row data, but in order to gain efficiency you can add "invisible" columns to the tables you need to update. These columns contain a "row version". So, even without secondaries you can add this columns to the tables and then do something like: select *, ifx_row_version into my_table_record.*, my_ifx_row_version from table where table_key = ?; -- do you processing like presenting the data to the user and let him decide... update table set reserved = "y" where table_key = ? and ifx_row_version = my_ifx_row_version; -- then check that the number of rows affected is 1 -- if it's 0 than the ifx_row_version changed meaning -- someone else updated the row ifx_row_version is one of the vercols. The engine changes it automatically in every update. You can do this with a custom column in any table, but you need to make sure you change it in every update you make (in all applications that access the data). So this is nothing "revolutionary" but it's a nice help if you want to implement optimistic locking. The reason for the "optimistic" is that you assume that most of the times your row will not change while the user is making decisions. If this is not the case than you probably should not use, because you'll annoy the users (if most of the times they get the message like "your requested seat was taken by another person quicker than you" ;) ) Direct links: http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.admin.doc/ids_admin_0877.htm?resultof=%22%76%65%72%63%6f%6c%73%22%20%22%76%65%72%63%6f%6c%22%20 http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids_sqs_0538.htm?resultof=%22%76%65%72%63%6f%6c%73%22%20%22%76%65%72%63%6f%6c%22%20 Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...