Re: Locking problem

In article <sghofhCu76nu.2BG@netcom.com> sghofh@netcom.com (SAM) writes: >We are using informix online 5.1, and isolation level is committed read. >We are declaring a cursor for update, and unfortunately the where caluse of >select statement has not been constructed properly and engine is doing a >sequential scan, instead of using a sorted index. I am opening the cursor >and when we are fetching the first row from the cursor, set explain on is >showing it has acquired 26,000 locks, which I presume, rows has been >selected by the where clause. > >My question is, Why it is holding the lock on the first fetch from the >cursor. In case of committed read, it should lock the rows as and when I >am fetching the rows. This seems to be identical to the problem that I posted to the group on 27 May. I intended posting a summary to the group, but never got round to it. Mea culpa! David Chan (dwc@cix.compulink.co.uk) explained it thus: If a select for update does a sequential scan, it can't go past a record locked by another process. Therefore all records having a higher rowid will appear locked. The only solution is to make sure the SQL statement uses an index. -- Keith Davies Internet: keith@keithd.demon.co.uk Tel.: +44 582 833651