FW: Sequential scan and lock on small tables

Mark, The workarounds are: 1. Use 'dirty read' isolation level 2. Use '--AVOID_FULL' optimizer hint if You have proper index Always use 'set lock mode to wait..' ------------------------------------------ Alexey Sonkin > -----Original Message----- > From: Maks Romih [mailto:maksr@snt.si] > Sent: Friday, April 16, 2004 2:53 PM > To: informix-list@iiug.org > Subject: Sequential scan and lock on small tables > > I was quite surprised to see that Informix, my version is 9.40 for > Windows NT, when the table is small, a few records, searches the > single row R1 with sequential scan and if doing so it comes upon a > totally different row R2, that is exclusively locked, aborts with > lock conflict. > > Suppose we have a small table ttt, created with "CREATE TABLE ttt( a > char(10), b char(20)) LOCK MODE (ROW); CREATE UNIQUE INDEX ttt_i1 ON > ttt( a );" and populated with some ten rows. Suppose session 1 > explicitly begins transaction with "BEGIN WORK" and updates one row, > say with "UPDATE ttt SET b = 'bv3' WHERE a='av3';" and then pauses. If > now, when the transaction of session 1 is still active, some ohter > session 2 tries to make a simple "SELECT b FROM ttt WHERE a='av7'", it > gets lock violation. > > The only way I see to overcome this strange behaviour is to insert > some few hundreds of meaningless rows into ttt, than make "UPDATE > STATISTICS" and then delete the rows. Then Informix searches the > reacord with index and will only lock the record I want. > > Can anybody suggest some better solution? > > Maks Romih. sending to informix-list