Re: Locking problem in INFORMIX ONLINE 7.20. UC2

Infodata Ltd. (Sharjah) U.A.E. wrote: > > 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. > > 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 ; > > 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. > > 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 First, what is the purpose of the ALTER TABLE before you to do the update. Second, committed read does fail when it encounters a row that is locked. So, there is only one row that is locked but the read is what is failing, because it is a committed read. Set Isolation to Dirty Read and you can see it work. Correct me if I am wrong. Hari Email : hkmtsr1@airmail.net Organization: TeleServices Resources