Question:
When in production (several users), my Web application starts getting empty cursors when querying the Informix server. I’m sure the ‘right’ results are not empty. Also, I’ve seen, using onmonitor, that accessing from the Web application I have two user threads tops. But I can’t figure out how to correct this or what is limiting the access.
Answer:
Looks like you have a concurrency/locking problem. These are most often solved by one of the following techniques:
- SET mode to WAIT 30: Tells the engine to keep trying for up to 30 seconds when a lock is encountered.
- Row level locking: Informix uses page level locking by default. If you have frequently updated tables, you can alter the lock level to row level locking. This will cut back on having to wait while an adjacent row is in use.
- Keep transactions short: If you are using transactions with a BEGIN WORK/COMMIT WORK structure, keep the time between the BEGIN and COMMIT as short as possible.
