SET TRANSACTION versus SET ISOLATION
The SET TRANSACTION statement complies with ANSI SQL-92. This statement is similar to the HCL® OneDB® SET ISOLATION statement; however, the SET ISOLATION statement is not ANSI compliant and does not provide access modes.
| SET TRANSACTION correlates with | SET ISOLATION |
|---|---|
| Read Uncommitted | Dirty Read |
| Read Committed | Committed Read |
| Not Supported | Cursor Stability |
| (ANSI) Repeatable Read Serializable |
(HCL
OneDB)
Repeatable Read (HCL OneDB) 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.