ISOLATION and LOCK MODE

Hi All, Just a newbie question. Am I correct to assume that: ISOLATION is to control the reading ~ Prevent two threads from reading the same row. and LOCK MODE is to control the writing ~ Prevent two threads from updating the same records? Thanks and regards Kwan.

Kwan wrote: > Just a newbie question. Am I correct to assume that: > > ISOLATION is to control the reading ~ Prevent two threads from reading > the same row. > > and > > LOCK MODE is to control the writing ~ Prevent two threads from > updating the same records? No; sorry, it isn't that simple. ISOLATION is used to control the extent to which the database can change while your transaction (or statement) is running. A higher level of isolation prevents people from modifying more of the data you are looking at. However, two processes both running at repeatable read isolation can read each others data as long as neither inserts or modifies a record the other would have seen (so there's no problem if they are both read-only transactions). In general, LOCK MODE is used to specify the action that should be taken when the process encounters a lock from another process. The options are NO WAIT (generate an error immediately), WAIT indefinitely, and WAIT for a limited time before generating an error. This is the SET LOCK MODE TO [NO] WAIT [n] statement. The alternative statement you might be talking about is LOCK TABLE x IN [SHARED | EXCLUSIVE ] MODE. The shared lock mode here allows other processes to read the table but prohibits them from modifying it at all; the exclusive lock mode prevents any other process from reading the table at all (let alone modifying it) - unless the other process is running at DIRTY READ isolation, in which case it can read it after all, but still can't write to it. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/