Re: Row locking in dynamic server

From: Jonathan Leffler <jleffler@earthlink.net> > >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. I dunno if I remember rightly, but I seem to recall that ANSI databases always use repeatable read, anyway. Comments, anyone? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

Obnoxio The Clown wrote: > I dunno if I remember rightly, but I seem to recall that ANSI databases > always use repeatable read, anyway. Comments, anyone? The default isolation in a MODE ANSI database is REPEATABLE READ; it can be over-ridden by the SET ISOLATION command, as in the example. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>