Re: Locking problem in INFORMIX ONLINE 7.20. UC2

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. >What I want to say is that IT LOCKS MORE THAN ONE ROW ( all rows after the >locked row). Is this O.K. or an bug or am I doing some mistake in >understanding it. > > Please throw some light if U can. > >I know about 'set lock mode to wait' but what I am confused is why is it >locking more than one row & how to prevent it. > >Thanks > >Bharat Shah >INFODATA Ltd. >P.O.Box 24114, Sharjah, U.A.E. >Email: infodsh@emirates.net.ae >Tel.: 971 6 599848, Fax: 971 6 592618 > -- David Williams