Re: Locks/Committed Read Problem

I want to thank all those who have responded and offered suggestions. To Weimin Yang I tried indexes on the tables and got the same result. Oh well, it was worth a shot. . . Then Sujit Pal provided a clearer explanation of "committed read" Sujit Pal wrote: > > Mark > > This is because you are trying to do a committed read. When you start a = > transaction, some UPDATE or INSERT processes have already grabbed an = > exclusive lock on some of the rows in the affected tables which will not = > be released till the transaction is either rolled back or committed. = Yes, I saw exclusive locks in place using tbstat -uk. > When dbaccess or other ESQL/C program is trying to do a committed read = > on the same tables, it scrolls through until it reaches the row that the = > transaction has an exclusive lock on. It then tries to grab a shared = > lock on the row, which it cannot because there is already an exclusive = > lock on that row. This will lead to a deadlock and Informix will = > timeout. <SNIP> > Hope this helps > > Sujit Pal > DBA, CSC Intellicom > This IS helpful, but it distressing nonetheless. So when isolation mode is set to committed read and I try to read the entries in the table, the server can NOT **skip** those rows which have a lock on them (i.e. not committed yet). In other words, I cannot get the committed rows (99.99% of the table's contents) because there is a single row which is part of a transaction which hasn't been committed yet. The server will wait until the lock is released or the fetch statement exceeds the number of seconds to wait and I get the errors that I have been getting. How depressing. I was hoping that I forgot something when I switched the logging mode of the database. The INFORMIX manuals don't clearly state this as you have done (Tutorial 5.1, pg 7-25). . . . Many thanks, mgo