Re: Locks/Committed Read Problem
Mark Oberfield wrote:
}
} 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 = ~~~~~~~~~~~~~~~
If you build index properly, it should not "scrolls through" the whole table. That
is your problem.
Query optimizer will search based on the index(if you use indexed column as
selection criterion. Try set explain on and run your query to see if it uses your
index) instead of sequential scan. So you can get to 99.99% of the data. But if
what you try to select is the locked rows(incidentally), you will get locked msg,
which is normal.
This works perfect with Online 7.11, with 5.xx I am not sure.
} > 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.
I don't think this will lead to a deadlock. Because there is only on lock,
committed read doesn't try to lock anything.