Re: massive deadlocks after upgrade from 7.3 to 9.4

Sorry, Did not see ur question properly. A shared lock is placed on the fetched row ( promotable to exclusive lock ) when u use update cursor. It looks like the engine is not able to identify that it is the same session which is holding the lock ( in 9.4 ) if u dont use where current of. Anyway, U should use where current of as you save one search again ( if u use where id= ? , then update has to locate that row again, whereas with current of knows already where the row is ). maybe check release notes on 9.4 ( it may be a bug in 7.3 fixed in 9.4 :) or a bug in 9.4 ) Rgds Preetinder preetinder dhaliwal wrote: > > Maybe ur tables now have page level locking ( default ) instead of row > level locking ( maybe ur migration for database did not take care of > this). > > check for the table with dbschema -d $DBNAME -t $TABNAME -ss > > To change - use alter table > > u can write a samll script where u take tabname from systables where > tabid > 99 and tabtype="T" and locklevel!="R" and then alter > > Rgds > Preetinder > > kwa@condat.de 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! > > > > -- > > 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 sending to informix-list