Re: Table locking in Informix table..

batonnet@zeta.org.au (Bryan Tonnet) wrote: :In <57gum5$dh5@cssun.mathcs.emory.edu> shanson@ams-co.nl.DHL.COM (Scott Hanson) writes: :>Operating System: HP-UX B.10.01 A :>X-Mailer: Elm [revision: 111.1] :>} :>} Hi, :>} :>} I have the follwing question on table locking feature of Informix table. Please :>} let me know if you have answer to this. :>} :>} We want to implement table locking. Suppose, there is a table "my_table", which :>} has the following fields. :>} :>} dept_no integer (NOT UNIQUE) :>} dept_name char[20] :>} no_of_people integer :>} :>} There are following rows in the table. :>} Dept. NoDept. Nameno_of_people. :>} 10 STS 10 :>} 10 STS1 20 :>} 20 HRD1 5 :>} 20 HRD2 4 :>} 30 Security15 :>} 30 Security23 :>} :>} Can I have multiplerecord level locking to the above table ? i.e. If a process :>} locks the records for which "dept no" is "10",(i.e. 1st and 2nd record, in the :>} above table) the second process must not be allowed to see either of 1st or 2nd :>} record. However the 2nd process should be able to see records other than record :>} 1 and record 2. :>} :>} If the record level locking is possible, please advise how do I implement ? :>} :>} Informix version I am using is INFORMIX Online 7.1 :>} :>} Ravi Hubbly :>} Ravi.Hubbly@autozone.com :>} :>} --------------------------------------------------------- :>} Get Your *Web-Based* Free Email at http://www.hotmail.com :>} --------------------------------------------------------- :>} :>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? :Bryan Tonnet :batonnet@zeta.org.au : 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. 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