Re: Lock Escalation

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.