CA Datacom/DB SQL Preprocessors › SQL Plan Options Special Topics › Mixing Isolation Levels

Mixing Isolation Levels

You can not mix different isolation levels in the same unit of recovery. For example, if a program which uses isolation level C calls another program preprocessed with isolation level U, an SQL -144 return code (INVALID TRANSACTION ISOLEVEL) will be returned whenever a statement is executed from the second program unless the first program executes a COMMIT WORK statement to end the unit of recovery before calling the second program.

Note: The both parameter of the SQLOPTION MUF startup option controls whether you are allowed to mix the use of isolation level U and C plans under a single logical unit of work (LUW) for those LUWs not running any SQLMODE ANSI or FIPS plans. Specifying YES for the both option of SQLOPTION indicates that mixing is allowed. It is your responsibility to verify proper locking is performed and the database state is not updated incorrectly from "dirty read" data that was read from an isolation level U plan.