RE: LOCK IN ROW

The problem probably occurs because your query requires a full table scan on the table in which you have locked the record. Obviously the engine can't access the locked record in any way and hence cannot decide if it is relevant for you query. If your query uses an index it will probably work! (what about something fundamental in relational databases like 'physical data independence', which assures that queries return the same results, no matter what execution plan is chosen). Remember part of the high performance of Informix is due to the fact that updates are done directly in the database and old values are written to the log. This means that the only way other transactions can access the old values is using the log, which is not supported by the engine, hence the locking problem. Other RDBMS's (e.g. Oracle) do it the other way: updates are written to a special log segment which can be used in queries within the same transaction. Other transactions can find the old values in the normal table space and hence do not suffer from locks or slow performance. Frido ------------------------------------------------------------------------ - Frido van Orden FAA Partners BV Planetenbaan 117 3606 AK Maarssen The Netherlands Phone: +31-346-587076 Fax: +31-346-587086 Email: fridoo@faapartners.com ------------------------------------------------------------------------ - } -----Original Message----- } From: Javier Puente [SMTP:jpuente@noboagye.noboa_corp.com.ec] } Sent: Friday, October 03, 1997 2:03 AM } To: Lista de Usuarios Informix WWW } Subject: LOCK IN ROW } } Hi Folks, } } I have a problem, when updating a row in my database (defined } with buffered logging) as part of a transaction, another user } than try to make a select statement in the refered table can't do it. } } The problem is a lock placed in rows other than the one updated. } } The table used is defined with granularity level to row. } } I did the test using all the isolation levels (repeatable read, } cursor stability ,dirty read, committed read). I just could } make the select when the dirty read isolation level was set. } I am using Informix DSA 7.22 } } The question: } Why i can't make a select to rows not updated during the transaction } of another user? } There is other ways to make isolation ?? } There is an enviromment variable that i should set??? } } Any comments, ideas or help will be appreciatted. } } Thanks in advance, } } Javier Puente } Computer Engineer } Noboa Coporation