Re: LOCK IN ROW

jpuente@noboagye.noboa_corp.com.ec (Javier Puente) wrote: :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. Probably not. See below. :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? You can, but the where clause have to guarantee that the row updated isn't part of the set returned by the select statement. If it could theoretically be the row (or index at least) must be checked while reading and the lock is encountered. :There is other ways to make isolation ?? No, but see below. :There is an enviromment variable that i should set??? No :Any comments, ideas or help will be appreciatted. 1. You should allways make your transactions very short, so that others will not have to wait. 2. Use set lock mode to wait some seconds. Your select will then wait if it encounters any locks. As your transactions are short it will never wait for long. 3. Sometimes you realy do want to use dirty read. Most transactions will eventually be commited. The dirty data will then be the latest data anyway. You do of course have to be very carefull about data integrity in this case. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org