Re: Table locking in Informix table..

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