locking tables , for update and transactions

A question has come up for which the Informix documentation is not quite clear. Can one lock the records one is interested in before one does a select? Not the whole table though. Or can one use the FOR UPDATE clause in a select statement that that is not part of a curser? In other words, one is in a transaction BEGIN WORK; Select * from TABLE A FOR UPDATE INSERT INTO TABLE B UPDATE TABLE C UPDATE TABLE A COMMIT WORK Transaction processing locks inserts, updates and deletes. Can one force it to lock selects as well? Will the records I selected from table A be locked so that no one else can use them? I am told that Select * from table FOR UPDATE works with Oracle. But my experience is with cursors. Many thanks.

"Wages, Peter M" wrote: > > A question has come up for which the Informix documentation is not quite > clear. > > Can one lock the records one is interested in before one does a > select? Not the whole table though. How do you know what records you are interested in and want to lock without performing a filter, i.e. performing a select? I think that the RDBMS will not lock a record without reading it, which makes sense, after all - in order to lock the record, the RDBMS must at least find the record and mark it as locked! The duration of the lock will depend on your ISOLATION level. If you SET ISOLATION TO DIRTY READ or COMMITTED READ, no locks (not evenshared) will be generated. CURSOR STABILITY will only lock a record whilst it is the currently selected record and the cursor remains open. REPEATABLE READ will ensure that the lock placed on each record remains until the current transaction is COMMITed or ROLLedBACK. It also protects the "filter set" by preventing other transactions from inserting matching rows. So, one way to do what you want to do is :- SET ISOLATION TO REPEATABLE READ;BEGIN; SELECT ROWID FROM table_a WHERE some_field = some_condition FOR UPDATE; At this point, you will have created an exclusive lock on the rows you require. You will then need to "repeat the read" to process the rows. You can easily achieve this with a non-forward-only cursor. > > > Or can one use the FOR UPDATE clause in a select statement that that > is not part of a curser? > > In other words, one is in a transaction > > BEGIN WORK; > Select * from TABLE A > FOR UPDATE> > INSERT INTO TABLE B> > UPDATE TABLE C > > UPDATE TABLE A > > COMMIT WORK > > Transaction processing locks inserts, updates and deletes. Can > one force it to lock selects as well? Yes, as detailed above. I'm not sure whether you are trying to process the rows from table_a in a FOREACH. If you are, remember that COMMITing will release all of your locks on table_a. > > Will the records I selected from table A be locked so that no one > else can use them? Yes, provided your ISOLATION is REPEATABLE READ. > > I am told that Select * from table FOR UPDATE works with Oracle. > But my experience is with cursors. > > Many thanks. HTH Brett Randall