locks and transaction isolation
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 fromanother 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