Re: Locking problem in INFORMIX ONLINE 7.20. UC2

In article <2XgIpJAeKtwzEwi5@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > > In article <5ptfiu$qlv@cssun.mathcs.emory.edu>, "Infodata Ltd. (Sharjah) > U.A.E." <infodsh@emirates.net.ae> writes > >Dear friends, > > > >My problem is as follows : > >Example --> > > > > I have a table tab1 with one field a (char(2)). > > There are 10 records in it. > > The value in the field is '1' ,'2','3' .... to '10' for each of the > >10 records. > > > OK. > > >Now I am opening one session and locking a particular record as follows : > > > > alter table tab1 lock mode (row);> > begin work; > > update tab1 > > set a = 20 > > where a = 2 ; > > > OK. > > >Now I am opening another session and selecting rows from that table as follows : > > > > set isolation to committed read; > > select * from tab1 > > where a > = '1' and a <= '9' ;> > > >This reads only first record and the second record and will give an SQL > >error for record lock. > >This is quiet understandable since there will be an exclusive lock for that row. > > > >But does the row level lock prevent all the rows after it also to be locked. > >I have tried this in different ways > >but everytime it comes to a locked row it will stop & give error and will > >not show the remaining rows. > > > This is because every query you ware doing tries to do a 'sequential > scan' of the table. This means it reads every row in the table to check > if is is one of the rows that mathes the where clause. Therefore all > queries searching on the table will try to read the locked row even if > it is does not match the where clause. > > Try adding an index on column a and you shuld see the difference. > This time it uses the index and so does not try to read every row in > the table and so should not try to read the locked row. > You're right that adding an index can avoid locks, but I don't think it will help this particular case because a='2' is being updated to a='20' and the select statement of the second session includes a where clause of a>='1'. The select statement will again fail. The locks can be avoided by using dirty read in which case a='2' will always be avoided but a='20' may be selected which is an uncommitted value. I don't see how sql by itself can give a result set of all rows except locked ones (kind of a strange and incomplete request), but a procedural language like I4gl or maybe even a stored procedure should. With I4gl you can set dirty read, select the rows in a foreach loop and in the loop set committed read, reread the current row and if it fails then "continue foreach". ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.rl.is/~john/pow4gl.html -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet