Isolation Levels and Transaction Levels

In this page, I wrote the collected information on isolation levels of Informix database.

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0030.htm

1 Isolation Levels

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0030.htm

Using the Dirty Read Isolation Level:

Use the Dirty Read option to copy rows from the database whether or not there are locks on them. The program that fetches a row places no locks and it respects none. Dirty Read is the only isolation level available to databases that do not implement transaction logging.

This isolation level is most appropriate for static tables that are used for queries of tables where data is not being modified, because it provides no isolation. With Dirty Read, the program might return an uncommitted row that was inserted or modified within a transaction that has subsequently rolled back, or a phantom row that was not visible when you first read the query set, but that materializes in the query set before a subsequent read within the same transaction. (Only the Repeatable Read isolation level prevents access to phantom rows. Only Dirty Read provides access to uncommitted rows from concurrent transactions that might subsequently be rolled back.)

The optional WITH WARNING keywords instruct the database server to issue a warning when DML operations that use the Dirty Read isolation level might return an uncommitted row or a phantom row. The transaction in the following example uses this isolation level:

BEGIN WORK; SET ISOLATION TO DIRTY READ WITH WARNING; ... COMMIT WORK;   

The Dirty Read isolation level is sensitive to the current setting of the USELASTCOMMITTED configuration parameter and of the USELASTCOMMITTED session environment variable. For information about the behavior of the Dirty Read isolation level when either of these are set to DIRTY READ or to ALL, see The LAST COMMITTED Option to Committed Read.

• Using the Committed Read Isolation Level

  Use the Committed Read option to guarantee that every retrieved row is committed in the table at the time that the row is retrieved. This option does not place a lock on the fetched row. Committed Read is the default level of isolation in a database with logging that is not ANSI compliant.

Use the Committed Read option to guarantee that every retrieved row is committed in the table at the time that the row is retrieved. This option does not place a lock on the fetched row. Committed Read is the default level of isolation in a database with logging that is not ANSI compliant.

Committed Read is appropriate when each row is processed as an independent unit, without reference to other rows in the same table or in other tables.

• The LAST COMMITTED Option to Committed Read

  Use the LAST COMMITTED keyword option of the Committed Read isolation level to reduce the risk of exclusive row-level locks held by other sessions either causing applications to fail with locking errors, or preventing applications from reading a locked row until after a concurrent transaction is committed or rolled back.

Use the LAST COMMITTED keyword option of the Committed Read isolation level to reduce the risk of exclusive row-level locks held by other sessions either causing applications to fail with locking errors, or preventing applications from reading a locked row until after a concurrent transaction is committed or rolled back.

In contexts where an application attempts to read a row on which another session holds an exclusive lock, these keywords instruct the database server to return the most recently committed version of the row, rather than wait for the lock to be released.

This feature takes effect implicitly in all user sessions that use the Committed Read isolation level of the SET ISOLATION statement, or that use the Read Committed isolation level of the ANSI/ISO-compliant SET TRANSACTION statement, under any of the following circumstances:

• if the USELASTCOMMITTED configuration parameter is set to ‘COMMITTED READ’ or to ‘ALL’
• if the SET ENVIRONMENT statement sets the USELASTCOMMITTED session environment variable to ‘COMMITTED READ’ or to ‘ALL’.

This feature also takes effect implicitly in all user sessions that use the Dirty Read isolation level of the SET ISOLATION statement, or that use the Read Uncommitted isolation level of the ANSI/ISO-compliant SET TRANSACTION statement, under any of the following circumstances:

• if the USELASTCOMMITTED configuration parameter is set to ‘DIRTY READ’ or to ‘ALL’
• if the SET ENVIRONMENT statement sets the USELASTCOMMITTED session environment variable to ‘DIRTY READ’ or to ‘ALL’.

Enabling this feature cannot eliminate the possibility of locking conflicts, but they reduce the number of scenarios in which other sessions reading the same row can cause an error. The LAST COMMITTED keywords are only effective with concurrent read operations. They cannot prevent locking conflicts or errors that can occur when concurrent sessions attempt to write to the same row.

This feature has no effect on Committed Read or Dirty Read behavior in contexts where no “last committed” version of the table is available, including these:

• The database does not support transaction logging
• The table was created with the LOCK MODE PAGE keywords, or has been altered to have a locking mode of PAGE
• The IFX_DEF_TABLE_LOCKMODE environment variable is set to ‘PAGE’
• The DEF_TABLE_LOCKMODE configuration parameter is set to ‘PAGE’
• The LOCK TABLE statement has explicitly set an exclusive lock on the table
• An uncommitted DDL statement has implicitly set an exclusive lock on the table
• The table is a system catalog table on which an uncommitted DDL statement has implicitly set an exclusive lock
• The table has columns of complex data types or of user-defined data types
• The table is a RAW table
• A DataBlade® module is accessing the table
• The table was created using the Virtual Table Interface.

User-defined access methods are not required to support the LAST COMMITTED feature.

The scope of LAST COMMITTED semantics is neither statement-based nor transaction-based. This isolation level has the same instant-in-time scope that the Committed Read isolation level has without the LAST COMMITTED option. For example, when a query is executed twice within a single transaction with LAST COMMITTED in effect, different results might be returned by the same query, if other DML transactions that were operating on the same data are committed in the interval between the two submissions of the query. This instantaneous nature of the semantics of Committed Read and of Committed Read Last Committed exactly implements the ANSI/ISO Read Committed isolation level.

The LAST COMMITTED feature does not support reading through table-level locks. If the access plan for a query that uses the LAST COMMITTED feature encounters a table-level lock in a table or index that it needs to access, the query will return the following error codes:

SQL error code:

252: Cannot get system information for table.

ISAM error code:

113: ISAM error: the file is locked.

• Using the Cursor Stability Isolation Level

  Use the Cursor Stability option to place a shared lock on the fetched row, which is released when you fetch another row or close the cursor. Another process can also place a shared lock on the same row, but no process can acquire an exclusive lock to modify data in the row. Such row stability is important when the program updates another table based on the data it reads from the row.

If you set the isolation level to Cursor Stability, but you are not using a transaction, the Cursor Stability acts like the Committed Read isolation level.

• Using the Repeatable Read Isolation Level

  Use the Repeatable Read option to place a shared lock on every row that is selected during the transaction. Another process can also place a shared lock on a selected row, but no other process can modify any selected row during your transaction, nor insert a row that meets the search criteria of your query during your transaction. If you repeat the query during the transaction, you reread the same information. The shared locks are released only when the transaction commits or rolls back. Repeatable Read is the default isolation level in an ANSI-compliant database.

Repeatable Read isolation places the largest number of locks and holds them the longest. Therefore, it is the level that reduces concurrency the most.

Using the RETAIN UPDATE LOCKS Option _

Use the RETAIN UPDATE LOCKS option to affect the behavior of the database server when it handles a SELECT … FOR UPDATE statement.

Use the RETAIN UPDATE LOCKS option to affect the behavior of the database server when it handles a SELECT … FOR UPDATE statement.

In a database with the isolation level set to Dirty Read, Committed Read, or Cursor Stability, the database server places an update lock on a fetched row of a SELECT … FOR UPDATE statement. When you turn on the RETAIN UPDATE LOCKS option, the database server retains the update lock until the end of the transaction rather than releasing it at the next subsequent FETCH or when the cursor is closed. This option prevents other users from placing an exclusive lock on the updated row before the current user reaches the end of the transaction.

You can use this option to achieve the same locking effects but avoid the overhead of dummy updates or the repeatable read isolation level.

You can turn this option on or off at any time during the current session.

You can turn the option off by resetting the isolation level without using the RETAIN UPDATE LOCKS keywords, as in the following example.

BEGIN WORK; SET ISOLATION TO     COMMITTED READ LAST COMMITTED RETAIN UPDATE LOCKS; ... COMMIT WORK; BEGIN WORK; SET ISOLATION TO COMMITTED READ LAST COMMITTED ; ... COMMIT WORK;   

2 Controlling Update Locks through the Session Environment

Another way to disable RETAIN UPDATE LOCKS behavior is to execute this SQL statement:

SET ENVIRONMENT RETAINUPDATELOCKS 'NONE';   

This disables the RETAIN UPDATE LOCKS clause for the current transaction, and for any subsequent transactions of the same session, by resetting the RETAINUPDATELOCKS session environment variable.

_The SET ENVIRONMENT RETAINUPDATELOCKS statement can also make the retention of update locks the default behavior for either the Committed Read, Cursor Stability, or Dirty Read isolation levels, or for all of these isolation levels, regardless of whether the SET ISOLATION statement includes the RETAIN UPDATE LOCKS clause.

For more information on update locks, see RETAINUPDATELOCKS session environment option and Locking Considerations.

• Turning the Option OFF During a Transaction

  If you set the RETAIN UPDATE LOCKS option to OFF after a transaction has begun, but before the transaction has been committed or rolled back, several update locks might still exist.

If you set the RETAIN UPDATE LOCKS option to OFF after a transaction has begun, but before the transaction has been committed or rolled back, several update locks might still exist.

Switching OFF the feature does not directly release any update lock. When you turn this option off, the database server reverts to normal behavior for the three isolation levels. That is, a FETCH statement releases the update lock placed on a row by the immediately preceding FETCH statement, and a closed cursor releases the update lock on the current row.

Update locks placed by earlier FETCH statements are not released unless multiple update cursors are present within the same transaction. In this case, a subsequent FETCH could also release older update locks of other cursors.

3 Transaction vs Isolation Level

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlt.doc/ids_sqt_390.htm

The SET TRANSACTION statement complies with ANSI SQL-92. This statement is similar to the Informix® SET ISOLATION statement; however, the SET ISOLATION statement is not ANSI compliant and does not provide access modes. In fact, the isolation levels that you can set with the SET TRANSACTION statement are almost parallel to the isolation levels that you can set with the SET ISOLATION statement, as the following table shows.

Another difference between SET TRANSACTION and SET ISOLATION is the behavior of the isolation levels within transactions. You can issue SET TRANSACTION only once for a transaction. Any cursors that are opened during that transaction are guaranteed that isolation level (or access mode, if you are defining an access mode). With SET ISOLATION, after a transaction is started, you can change the isolation level more than once within the transaction.

The following examples illustrate this difference in the behavior of the SET ISOLATION and SET TRANSACTION statements:

EXEC SQL BEGIN WORK; EXEC SQL SET ISOLATION TO DIRTY READ; EXEC SQL SELECT ... ; EXEC SQL SET ISOLATION TO REPEATABLE READ; EXEC SQL INSERT ... ; EXEC SQL COMMIT WORK;    -- Executes without error   

Compare the previous example to these SET TRANSACTION statements:

EXEC SQL BEGIN WORK; EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; EXEC SQL SELECT ... ; EXEC SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- Produces error 876: Cannot issue SET TRANSACTION    -- in an active transaction.   

An additional difference between SET ISOLATION and SET TRANSACTION is the duration of isolation levels. Because SET ISOLATION supports complete-connection level settings, the isolation level specified by SET ISOLATION remains in effect until another SET ISOLATION statement is issued. The isolation level set by SET TRANSACTION only remains in effect until the transaction terminates. Then the isolation level is reset to the default for the database type.

4 ACCESS Mode

Access modes affect read and write concurrency for rows within transactions. Use access modes to control data modification. SET TRANSACTION can specify that a transaction is read-only or read-write. By default, transactions are read-write. When you specify a read-only transaction, certain limitations apply. Read-only transactions cannot perform the following actions:

• Insert, delete, or update rows of a table.
• Create, alter, or drop any database object such as schemas, tables, temporary tables, indexes, or SPL routines.
• Grant or revoke access privileges.
• Update statistics.
• Rename columns or tables.

You can execute SPL routines in a read-only transaction as long as the SPL routine does not try to perform any restricted statement.

5 Default Isolation Levels

The default isolation level is established when you create the database.

_

For Informix databases that are not ANSI-compliant, unless you explicitly set the USELASTCOMMITTED configuration parameter, the LAST COMMITTED feature is not in effect for the default isolation levels. The SET ENVIRONMENT statement or the SET ISOLATION statement can override this default and enable LAST COMMITTED for the current session.

The default isolation level remains in effect until you issue a SET TRANSACTION statement within a transaction. After a COMMIT WORK statement completes the transaction or a ROLLBACK WORK statement cancels the entire transaction, the isolation level is reset to the default.

When you use High Availability Data Replication, the database server effectively uses Dirty Read isolation on the HDR Secondary Server, regardless of the specified SET ISOLATION or SET TRANSACTION isolation level, unless the UPDATABLE_SECONDARY configuration parameter is enabled. For more information about this topic, see Isolation Levels for Secondary Data Replication Servers.

https://www.ibm.com/developerworks/data/library/techarticle/dm-0808mathada/index.html

SQL ler:

SET ISOLATION TO COMMITTED READ LAST committed

SET ISOLATION TO COMMITTED READ

SET TRANSACTION ISOLATION LEVEL READ committed

SET ISOLATION TO dirty READ