[Info-ingres] [Info-Ingres] Ingres 9.2 to 10.1 Migration

[Roy Hann]

nikosv wrote:

> are there any steps that should be taken for switching application code 
> from using ANSI isolation to MVCC?

MVCC gives you SERIALIZABLE isolation and something like (at
least) REPEATABLE READ so MVCC doesn't contradict ANSI isolation.

Unless you are actually relying on seeing the anomalies that the more
relaxed isolation levels allow then you should be OK. I can't think of a
case where I have ever *wanted* anomalies. But then again I never
write code that relies on dirty reads.

It may be that you will have to explicitly protect some transactions by
setting the isolation level to SERIALIZABLE instead of counting on a
blocking read-lock to make transactions take turns.

You use the SET TRANSACTION/SESSION ISOLATION statements in the same way
with MVCC as with pessimistic locking it's just that you will tend to
get better isolation and consistency than you ask for.

Using MVCC you can still get serialization failures but they may happen
in different places than under pessimistic locking.  You should always
be ready to detect and handle serialization failures though, even
without MVCC (e.g. deadlocks).

All this is a round about way of saying it's probably easy to change
the code as required but it's not easy to know where you have to make
changes.

You need to know what your application does, in detail. How many places
you have to make a change to implement MVCC depends on the whether it
was written with isolation levels in mind or whether it
implicitly exploited knowledge of how pessimistic locking works. I think
most older Ingres application code assumes pessimistic locking simply
because a lot of it was written before ANSI isolation levels were
defined.

Roy