RE: update impossible in spite of row locking
Posted in 2009
Topics: Performance & Tuning, Error Codes & Troubleshooting, Server Administration, Transactions, Locking & Isolation
Please post query plans for both sessions
Regards
Colin
There are 10 types of people in the world, those that understand binary and those that don't
> Date: Tue, 23 Jun 2009 12:14:31 +0200
> From: tilleul17@web.de
> To: RHabichtsberg@arz-emmendingen.de
> Subject: Re: update impossible in spite of row locking
> CC: informix-list@iiug.org
>
> Habichtsberg, Reinhard wrote:
> > Hi all,
> >
> > we have a strange behaviour of IDS while trying to update rows. IDS Version
> > 11.50.FC3 and 9.40.FC9.
> >
> > The table has row locking, unique index and primary key. The database is in
> > logging mode buffered.
> >
> > We open two sessions: In the first session we open a transaction and update
> > a certain row selected by the primary key. The transaction is not yet
> > commited.
> >
> > In the second session we try to update certain other rows as well
> > selected by the primary key. Some rows could be updated. But with some rows
> > it appears following error:
> > 244: Could not do a physical-order read to fetch next row.
> > 107: ISAM error: record is locked.> >
> > Why does this happen? We assumed that row locking would only lock the one
> > row of session 1. But obviously the update statement of session 2 is blocked
> > by a lock.
> >
> > How can we avoid such a situation (it simulates that a user session is in a
> > transaction while the user isn't able to finish it).
> >
> > May be we are understanding sonething wrong, but our software developer
> > affirm that many of our programs depend on the ability of DBMS to update
> > several rows of a table simultaneously even though a transaction on the
> > table hangs.
> >
> > Can anybody help? It's rather urgend.
> >
> > TIA,
> > Reinhard.
>
> Is the PK fully qualified for the rwos selected by the second session?
> If the second session updates a range of rows , it might well be, that
> the row updated by the first sessions needs to be evaluated.
>
> -244 sounds as if the second session might be doing a full table scan
> (physical-order read).
> In case Session 2 does a full table scan, try an update statistics +
> distributions for key columns, to see whether it then takes the PK index
> to access its rows.
>
> You could set Lock wait mode to wait indefinitely , but the down side is
> of course that wait situations and deadlocks may occurr.
>
> You might also set a trap for -244 ('onmode -I 244' ) and look at the
> generated af file (use 'onmode -I' to unset the trap)
> (preferrably you do this in a test environment, not production)
>
> With kind regards
> Tilman Model-Bosch
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
With Windows Live, you can organise, edit, and share your photos.
http://clk.atdmt.com/UKM/go/134665338/direct/01/