IBM® Informix® 12.10

SET TRANSACTION versus SET ISOLATION

The SET TRANSACTION statement complies with ANSI SQL-92. This statement is similar to the IBM® Informix® SET ISOLATION statement; however, the SET ISOLATION statement is not ANSI compliant and does not provide access modes.

The following table shows the relationships between the isolation levels that you set with the SET TRANSACTION and SET ISOLATION statements.
SET TRANSACTION correlates with SET ISOLATION
Read Uncommitted Dirty Read
Read Committed Committed Read
Not Supported Cursor Stability
(ANSI) Repeatable Read

Serializable

(IBM Informix) Repeatable Read

(IBM Informix) Repeatable Read

The major difference between the SET TRANSACTION and SET ISOLATION statements is the behavior of the isolation levels within transactions. The SET TRANSACTION statement can be issued only once for a transaction. Any cursors opened during that transaction are guaranteed to have that isolation level (or access mode if you are defining an access mode). With the SET ISOLATION statement, after a transaction is started, you can change the isolation level more than once within the transaction. The following examples illustrate the difference between the use of SET ISOLATION and the use of SET TRANSACTION.


Examples exchange | Troubleshooting

To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.