Row locking in dynamic server

Hi General question: how can I achive row locking in a multi user environment. Seems not to work for me I experiences a problem with row locking with Dynamic Server 7.3 under Linux. Even a simple test would not run: Databse create with ANSI compliant. Created a table with LOCK MODE ROW. Oopen two dbaccess session, setting isolation to DIRTY read in each. Now selecting from the table with ... FOR UPDATE in both sessions with different rows. Received 244: Could not do a physical-order read to fetch next row. 113: ISAM error: the file is locked. Example CREATE TABLE TESTLOCK ( TL_ID INTEGER NOT NULL, TL_NAME CHAR(20) ) LOCK MODE ROW INSERT INTO TESTLOCK VALUES (1, 'ONE'); INSERT INTO TESTLOCK VALUES (1, 'ONE'); INSERT INTO TESTLOCK VALUES (2, 'TWO'); INSERT INTO TESTLOCK VALUES (3, 'THREE'); INSERT INTO TESTLOCK VALUES (4, 'FOUR');COMMIT; -- now open two different dbaccess sessions and set isolation level SET ISOLATION TO DIRTY READ;COMMIT; -- now try two selects/updates in different sessions SELECT * FROM TESTLOCK WHERE TL_ID = 1 FOR UPDATE; UPDATE TESTLOCK SET TL_NAME = 'ONE' WHERE TL_ID = 1; --and SELECT * FROM TESTLOCK WHERE TL_ID = 2 FOR UPDATE; As I unsderstood, the second select should work, because its rows are not affected by the first one. Thanks Ruediger

Rüdiger Breit wrote: > General question: how can I achive row locking in a > multi user environment. Seems not to work for me > > I experiences a problem with row locking with > Dynamic Server 7.3 under Linux. Even a simple > test would not run: > > Databse create with ANSI compliant. > Created a table with LOCK MODE ROW. > Oopen two dbaccess session, setting isolation to > DIRTY read in each. Now selecting from the table > with ... FOR UPDATE in both sessions with different > rows. Received > 244: Could not do a physical-order read to fetch next row. > 113: ISAM error: the file is locked.> > Example > > CREATE TABLE TESTLOCK ( > TL_ID INTEGER NOT NULL, > TL_NAME CHAR(20) ) > LOCK MODE ROW> > INSERT INTO TESTLOCK VALUES (1, 'ONE'); > INSERT INTO TESTLOCK VALUES (1, 'ONE'); > INSERT INTO TESTLOCK VALUES (2, 'TWO'); > INSERT INTO TESTLOCK VALUES (3, 'THREE'); > INSERT INTO TESTLOCK VALUES (4, 'FOUR');> COMMIT; > > -- now open two different dbaccess sessions and set isolation level > > SET ISOLATION TO DIRTY READ;> COMMIT; > > -- now try two selects/updates in different sessions > > SELECT * FROM TESTLOCK WHERE TL_ID = 1 FOR UPDATE; > UPDATE TESTLOCK SET TL_NAME = 'ONE' WHERE TL_ID = 1;> > --and > > SELECT * FROM TESTLOCK WHERE TL_ID = 2 FOR UPDATE;> > As I unsderstood, the second select should work, because its rows are > not affected by the first one. Put an index on the TL_ID column; your conflict will be resolved. When there is no index, the only way to process the second query is by a sequential scan, and that sequential scan runs into your lock -- hence the error. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>