Re: Table locking in Informix table..

In <57kin0$4a5@nntp.idgonline.no> Nils.Myklebust@idg.no (Nils Myklebust) writes: >:>You need to set your isolation level to repeatable read. >:> This will allow the first process to gain a lock on the rows for >:> which 'dept no' is 10 and the second process will be able to get >:> a shared lock on these rows, but not get an exclusive lock. >:I think this only answers part of the problem. Ravi wants users to not "see" >:records which are locked. I think this means each record the first process >:has must have an exclusive lock placed. The second process then has to have >:at least COMMITTED READ isolation, so any exclusively locked rows are skipped >:on reading. >:The problem then, is how to get the first process to exclusively lock all >:rows it is interested in. One way might be to update all these rows inside >:a transaction (creating and holding the exclusive locks). The update could >:be a "dummy" update, if the first process really only wants to look at the >:records. >:This sounds like a clunky way to go about things. Anybody got a more >:informed view to impart? >But doesn't this solution give another problem? I think the second >process will hang on a lock wait when trying to read the locked rows. You're right, of course. But if LOCK MODE is set to NO WAIT, and good error handling is enforced...... Still, it does seem to be a round about way of getting the wanted result. Perhaps the best bet is just to select the unqiue key of the table into an intermediate (physical) table, and all selects against the first table would be doubled (an insert and a read) and could be of the form [ insert into intermediate_table ] select fields from table where stuff and key not in (select key from intermediate_table) Of course, the program would then have to delete its own additions from the intermediate table when it's finished......Yuck. God knows what the result would be if the database crashed though. >If Ravi realy don't want the second process to see the locked rows, >the only way I know of doing it is to delete them and commit the >delete. This may of course violate some referencial integrity but that >is another problem. >To mee it sounds like Ravi intend to implement a system where locks >can be held over some time. There is no (good) support for that in >Informix databases. Generaly locks should be held for as short a time >as possible to avoid deadlocks and several other problems. If this is >consistently done other processes can simply wait for the locks to be >released. >If you realy want to "lock" something for some time I believe you need >to implement some other sceem. >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