massive deadlocks after upgrade from 7.3 to 9.4
After upgrading from 7.3 to 9.4, an app using a FOR UPDATE cursor followed by a separate "UPDATE ... WHERE id = :value" started hitting -244/ISAM -143 deadlocks under heavy concurrent load, though the same code ran fine on 7.3. Changing the update to WHERE CURRENT OF cursor fixed it. Art Kagel confirmed this is the correct and faster approach: it updates via the row's actual location rather than re-traversing the index, where another session's key lock caused contention. No explanation was found for the version difference; others reported the same after 9.21->9.30, and suggestions to check OPTCOMPIND, row-level locking, update statistics and index usage went unresolved.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
we are using Informix 7.3 and upgrade to 9.4.
Our database access layer generator creates code like this:
EXEC SQL DECLARE C_Table CURSOR FOR
SELECT * FROM table
WHERE id = :ID_23 FOR UPDATE;
EXEC SQL FETCH C_Table INTO :V23_1,:
...
EXEC SQL UPDATE table SET
...
WHERE id = :V23_1;
This works fine under heavy load (many simulanous updates) under 7.3,
but generates under 9.4 many error like this (same env and test script):
Error -244: Could not do a physical-order read to fetch next row.
ISAM-Error -143: ISAM error: deadlock detected
If I change
WHERE id = :V23_1;
to
WHERE CURRENT OF C_Table;
then it works under 9.4 fine, too.
Is this the right solution and why it does work?
Thanks!
--
Klaus Warnke
Software Developer
--
Condat Informationssysteme AG
Alt-Moabit 91d | 10557 Berlin | Germany
Tel: +49.30.39-49-1286 | Fax: +49.30.39-49-222-1286
kwa@condat.de | http://www.condat.de
On Thu, 24 Jul 2003 09:04:39 -0400, kwa wrote:
> Hello,
>
> we are using Informix 7.3 and upgrade to 9.4. Our database access layer
> generator creates code like this:
>
> EXEC SQL DECLARE C_Table CURSOR FOR
> SELECT * FROM table
> WHERE id = :ID_23 FOR UPDATE;>
> EXEC SQL FETCH C_Table INTO :V23_1,:
> ...
>
> EXEC SQL UPDATE table SET
> ...
> WHERE id = :V23_1;
>
> This works fine under heavy load (many simulanous updates) under 7.3,
> but generates under 9.4 many error like this (same env and test script):
>
> Error -244: Could not do a physical-order read to fetch next row.
> ISAM-Error -143: ISAM error: deadlock detected
>
> If I change
>
> WHERE id = :V23_1;
>
> to
>
> WHERE CURRENT OF C_Table;
>
> then it works under 9.4 fine, too.
>
> Is this the right solution and why it does work? Thanks!
Yes this is the correct solution and a more efficient way to perform the
update. I do not know why you are seeing different behavior between the
two versions I would imagine you would have had concurrency issues under
7.3x as well. The problem with the original update is that it needs to
access the index on the 'id' column in order to find the row to update
and another key has been locked by another application (or another
instance of this one) and you are being locked out. I'd have to think
about it and likely examine other apps and this one in detail to
determine why the problem appears to cause a deadlock.
Anyway, why does it work? Because the WHERE CURRENT OF CURSOR clause
uses the actual row location to facilitate the update and does not
require accessing any indexes, which is why it is also faster than the
original code.
Art S. Kagel
kwa@condat.de wrote: > > we are using Informix 7.3 and upgrade to 9.4. > > This works fine under heavy load (many simulanous updates) under 7.3, > but generates under 9.4 many error like this (same env and test > script): > > Error -244: Could not do a physical-order read to fetch next row. > ISAM-Error -143: ISAM error: deadlock detected Quite a few complaints seem to be coming across about this. What's the setting of your OPTCOMPIND parameter in the $ONCONFIG file? If that's encouraging it to use more hash joins (and maybe the new version also encourages itself even further) then it's probably responsible for increasing the lock contention.
On Fri, 25 Jul 2003 11:26:08 +1000, Andrew Hamm <ahamm@mail.com> wrote: > kwa@condat.de wrote: >> >> we are using Informix 7.3 and upgrade to 9.4. >> >> This works fine under heavy load (many simulanous updates) under 7.3, >> but generates under 9.4 many error like this (same env and test >> script): >> >> Error -244: Could not do a physical-order read to fetch next row. >> ISAM-Error -143: ISAM error: deadlock detected > > Quite a few complaints seem to be coming across about this. > > What's the setting of your OPTCOMPIND parameter in the $ONCONFIG file? If > that's encouraging it to use more hash joins (and maybe the new version > also > encourages itself even further) then it's probably responsible for > increasing the lock contention. > > > I am havning the same problem after having gone from 9.21 to 9.30. -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
My OPTCOMPIND is 0. "Andrew Hamm" <ahamm@mail.com> wrote in message news:bfq134$fdvlr$1@ID-79573.news.uni-berlin.de... > kwa@condat.de wrote: > > > > we are using Informix 7.3 and upgrade to 9.4. > > > > This works fine under heavy load (many simulanous updates) under 7.3, > > but generates under 9.4 many error like this (same env and test > > script): > > > > Error -244: Could not do a physical-order read to fetch next row. > > ISAM-Error -143: ISAM error: deadlock detected > > Quite a few complaints seem to be coming across about this. > > What's the setting of your OPTCOMPIND parameter in the $ONCONFIG file? If > that's encouraging it to use more hash joins (and maybe the new version also > encourages itself even further) then it's probably responsible for > increasing the lock contention. > >
"Jason" <jasonperrone@pricechopper.com> wrote in message
news:XV9Ua.1390$vX1.9146@news.uswest.net...
> My OPTCOMPIND is 0.
>
All tables are row level locking?
Update stats done correctly?
SET EXPLAIN ON shows all sql is using the correct index and that index is
unique?