Informix Guide to SQL: Syntax SQL Statements

Use the SET TRANSACTION statement to define isolation levels and to define the access mode of a transaction (read-only or read-write).

You can use SET TRANSACTION only in databases with logging.

You can issue a SET TRANSACTION statement from a client computer only after a database is opened.

The database isolation level affects concurrency among processes that attempt to access the same rows simultaneously from the database. The database server uses shared locks to support different levels of isolation among processes that are attempting to read data as the following list shows:

• Read Uncommitted
• Read Committed
• (ANSI) Repeatable Read
• Serializable

The update or delete process always acquires an exclusive lock on the row that is being modified. The level of isolation does not interfere with rows that you are updating or deleting; however, the access mode does affect whether you can update or delete rows. If another process attempts to update or delete rows that you are reading with an isolation level of Serializable or (ANSI) Repeatable Read, that process will be denied access to those rows.

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 the SET TRANSACTION and SET ISOLATION statements is the behavior of the isolation levels within transactions. You can issue the SET TRANSACTION statement only once for a transaction. Any cursors that are opened during that transaction are guaranteed to get 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 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

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.

Another difference between SET ISOLATION and SET TRANSACTION is the duration of isolation levels. The isolation level set 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.

The following definitions explain the critical characteristics of each isolation level, from the lowest level of isolation to the highest.

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

This isolation level is most appropriate for static tables that are used for queries, that is, tables where data is not being modified, since it provides no isolation. With Read Uncommitted, the program might return a phantom row, which is an uncommitted row that was inserted or modified within a transaction that has subsequently rolled back. No other isolation level allows access to a phantom row.

Use the Read Committed 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. Read Committed is the default level of isolation in a database with logging that is not ANSI compliant.

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

Use the Serializable 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 or 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. Serializable is the default isolation level in an ANSI-compliant database.

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

The default isolation level for a particular database is established according to database type when you create the database. The default isolation level for each database type is described in the following table.

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 transaction, the isolation level is reset to the default.

Informix database servers support access modes. Access modes affect read and write concurrency for rows within transactions. Use access modes to control data modification.

You can specify that a transaction is read-only or read-write through the SET TRANSACTION statement. By default, transactions are read-write. When you specify that a transaction is read-only, certain limitations apply. Read-only transactions cannot perform the following actions:

• Insert, delete, or update table rows
• Create, alter, or drop any database object such as schemas, tables, temporary tables, indexes, or SPL routines
• Grant or revoke 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.

You cannot set the database isolation level in a database that does not have logging. Every retrieval in such a database occurs as a Read Uncommitted.

The data that is obtained during retrieval of BYTE or TEXT data can vary, depending on the database isolation levels. Under Read Uncommitted or Read Committed isolation levels, a process is permitted to read a BYTE or TEXT column that is either deleted (if the delete is not yet committed) or in the process of being deleted. Under these isolation levels, an application can read a deleted BYTE or TEXT column when certain conditions exist. For information about these conditions, see the Administrator's Guide.

In ESQL/C, if you use a scroll cursor in a transaction, you can force consistency between your temporary table and the database table either by setting the isolation level to Serializable or by locking the entire table during the transaction.

If you use a scroll cursor with hold in a transaction, you cannot force consistency between your temporary table and the database table. A table-level lock or locks set by Serializable are released when the transaction is completed, but the scroll cursor with hold remains open beyond the end of the transaction. You can modify released rows as soon as the transaction ends, so the retrieved data in the temporary table might be inconsistent with the actual data.

Related statements: CREATE DATABASE, SET ISOLATION, and SET LOCK MODE

For a discussion of isolation levels and concurrency issues, see the Informix Guide to SQL: Tutorial.