Re: locks and transaction isolation

SET LOCK MODE TO WAIT would work. Could you put a SET EXPLAIN ON for allinstances and run your test? I am suspecting that your queries may be doing Dynamic Hash Join and thus reading more rows that it should and encountering locks. As a test, you can try settingthe value of OPTCOMPIND to 0 and try it. Please keep me posted. Thanks. Idiot >Hi, >my company has an DB-bases product which lives of many and huge >transactions >(transactions which have an duration of 1 minute). We have an ONLINE DB >7.23 UC1, >customers of us use INFORMIX ONLINE 7.22 - 7.30. All versions of the >ONLINE >DB have the same problem(?) which can be explained by the following >example: >one task (transaction) inserts a row into a table with some kind of an >index. >Another task wants to delete another row in that table having another id >(->no row >conflict). On some DB versions it works on some not (depends on the >query-optimizer if >the delete's statements WHERE clause uses an index!?). If the delete >statment uses an >index, the operation works, if not we are getting error -244 (...yes we >have row >locking...) (you can easily produce that error on a table without >index). To this point >informix might be strange but probably one must have indexes for all >cases which could >happen!?!? > >The second problem is the ISOLATION level COMMITED READ which should >only >return rows which where commited. With the example above, just try an >select from>another task using no where clause or an where clause which could >receive the >uncommited insert of the other task -> ERROR -244 on all informix >versions we could >test! I think informix should write in the documentation: In COMMITED >READ only >commited rows are gotten and if a row is not commited jet, no row is >gotton and a error >occurs... (The real funny thing is: on a dirty read standard engine >everything is working >allot better (no problems here)). > >Are we the only people using transactions under online or does everybody >use SET >LOCK MODE TO WAIT (works fine but the performance of an muli user system >is the >performance of an serial single user system...); or are all our (and our >customers) online >installations crap??? > >If someone could help us please mail to cl@daidalos.com (or this >address) (informix >support is also welcome). If no one could help us we have to go to >ORACLE where the >needed features above are working very well(but others?) >Thanks >CL >