Re: Error 271/143 when inserting rows

J'rg Spilker wrote: > Hello, > > i'm totally confused about how a deadlock could occur when inserting > rows. My application computes some data and deletes rows from a table > t_mytable at the beginning for some key value and inserts the new > computed data later. All this in one transaction (for one key values). > When i'm starting multiple instances of the application working on > different ranges of key values i sometimes got error 271/143. I've now > idea about the reason as the instances are working on completely > independent data. Lock Level of the table is row. > > Greetings, Joerg > well, I am positive I have posted explanations to related problems before, anyway, here goes: essentially, although your datasets are disjoint, a combination of lock wait settings, isolation levels and query plans could require that one session needs to access a row on which the other has got a lock and viceversa: a typical example is that you are using repeatable read, have deleted the last row in the table, and you have a lock on the infinity slot of an index to enforce the repeatable read on the delete: in which case any insert past the last row will fail. At the same time the session that's waiting on the infinity slot lock holds a lock that the other session needs to assess whether a row satisfies a condition, possibly because of a poorly chosen path. You could run into similar scenarios with updates, when session one updates a column which is part of an index on which session two has got a lock. You can follow the lock chain from an onstat -k taken at the time of the deadlock. one way of obtaining this is onmode -I 143: this will generate an assert failure (won't bring the engine down) when error 143 is encountered. Disregard the message that says 'issue onmode -i to continue': you don't need to do that except in particular circumstances (if you don't know what AFDEBUG is, you don't need to know) you can clear the error trap with onmode -I My suggestion would be:- lower isolation levels as much as possible update statistics as per recomendationsmake sure that the engine uses key only paths specify conditions only on primary key columns of the table don't set lock mode to wait see if you can split your data over multiple tables: eg you could fragment by expression, and whenever you need to do this job, you would detach various fragments, and have one session act on each resulting table. you would reattach the fragments at the end of the whole process - but - you have to make sure that the rows you have newly inserted will not need to migrate fragments (eg you use fragmentation by expression, and a poorly chosen one at that) HTH -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm