Re: locking and transaction isolation level

In article <6pqe7v$5p8$1@news01.btx.dtag.de>, Christian Lang <cl.daidalos@t-online.de> writes >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!?!? > Correct, uniue indexes as well so only one index key only affects one data row... >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)). > Do SET ISOLATION TO DIRTY READ then!! >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 > > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care