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.

1/ on the select session set the isolation level to dirty read 2/ on the select session hint the optimiser to do a index read Maks Romih <maksr@snt.si> wrote in message news:<u1xmnzrdn.fsf@snt.si>... > 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.