Re: Lock Escalation

Does "SET LOCK MODE TO WAIT" affect uses performing SELECT statementes? I have not been successful in preventing users from getting errors 243, 244, 245 or 246 on SELECT statements by using "SET LOCK MODE TO WAIT". We typically check for these errors and retry the select. "SET LOCK MODE ..." defintitely does work on UPDATES. Steve Berg spb@nb.com On Tue, 9 Sep 1997, Art S. Kagel wrote: } Herb Blacker wrote: } > } > Can someone please tell me if Informix v7.x does the following? } > 1. I have a central table with row-level locking, committed read isolation. } > 2. I have 30-50 people at a time doing updates to the table at random times } > 3. I have 3-4 people doing queries to the table at random times. } > 4. The people doing queries occasionally get errors stating that the data } > they are requesting is unavailable. } > 5. The question is this: can the database *automatically* escalate locks } > from row-level to page-level to table-level, depending on the circumstances } > (i.e. a query on a large number of rows, or concurrent multiple single row } > updates)? } } No Informix does not "promote" locks. } } > 6. Assuming we agree to the possibility of reading old data, will setting } > isolation to 'dirty read' correct the problem so that queries always work? } } Yes it would. } } You did not ask the obvious next question: } } 7) Is there a better way to do this? } } You can "SET LOCK MODE TO WAIT 10;" in your applications which will } prevent the applications from being locked out unless you are FETCHing } FOR UPDATE and holding the lock while a user peruses and modifies the } row online. In this case you should find a better update strategy that } provides better concurrency or "SET LOCK MODE TO WAIT;" and risk a } query stalling for a few hours while someone goes off to a meeting } with a locked row on their screen. Either way you keep you isolation } level. } } I'm surprised that only your Query users are being locked out. Is the } nature of the update process such that only one user can be working on } a particular record? Just curious. }