Re: problems with repeatable read - whole table is locked

From: Rudy Fernandes <rferdy@americasm01.nt.com> > >Obnoxio The Clown wrote: > > > From: harald_wehr@my-deja.com > > >... > > >set isolation to repeatable read;> > >begin work; > > >select * from kunde where nr < 4;> > > > > >After that we expect informix to lock only these selected rows. But >when > > >we open another connection to the database and we execute next > > >statement: > > > > > >update kunde set name="mueller" where nr = 30> > > > > >we get this error messages: > > > > > > 243: Could not position within a table (u13210.kunde). > > > 113: ISAM error: the file is locked. > > >...> > > Informix will lock all rows it has to _look at_ to satisfy the query, >not > > just the rows that actually satisfy the query. > >Surely, this can not be right. I didn't say I liked it, but he said "repeatable read", and according to the manual, when you do a repeatable read query, every row that gets examined, gets locked. Perf Guide, Page 5-11: "Because even examined rows are locked, if the database server reads the table sequentially, a large number of rows unrelated to the query result can be locked. For this reason, use Repeatable Read isolation for tables when the database server can use an index to access a table. If an index exists and the optimizer chooses a sequential scan instead, you can use directives to force use of the index. However, forcing a change in the query path might negatively affect query performance." >I can imagine the reverse, though, a situation as follows : > >The SELECT locks rows where nr < 4 (examine the session's "onstat -u" row >to >determine the number of locks taken). > >The UPDATE does a sequential scan (stats not updated) to update the row >that >matches "nr = 30". It runs into the 243 error because it can not look up >the >locked rows to determine whether they qualify to be updated. > >I agree with your solution, though - Update statistics before trying again. Which is not to say that your suggestion might not be true, either. :-) ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

Obnoxio The Clown wrote: > From: Rudy Fernandes <rferdy@americasm01.nt.com> > > > >Obnoxio The Clown wrote: > > > > ... > > > > > > > > 243: Could not position within a table (u13210.kunde). > > > > 113: ISAM error: the file is locked. > > > >...> > > > > Informix will lock all rows it has to _look at_ to satisfy the query, > >not > > > just the rows that actually satisfy the query. > > > >Surely, this can not be right. > > I didn't say I liked it, but he said "repeatable read", and according to the > manual, when you do a repeatable read query, every row that gets examined, > gets locked. > This is true. I stand corrected (and wiser!). Rudy