SET TRANSACTION sets the characteristics of all subsequent transactions on the system.

Note: When using ODBC, it is recommended that SET TRANSACTION not be used. To ensure full ODBC compatibility use the ODBC-specific functions to manage transactions.
SET TRANSACTION transaction_action [, transaction_action] 

   transaction_action ::=
      ISOLATION LEVEL isolation_level
      | access_mode 

   isolation_level ::=

   access_mode ::=

Isolation Level (syntax part)

Higher transaction isolation levels require more locking, more disk I/O, and more calculations, but provide higher levels of data consistency and reliability. Several types of data consistency issues can occur. These issues are prevented or allowed to occur depending on the isolation level specified in the SET TRANSACTION statement.

The data consistency issues you may encounter are described here:

  • Dirty Read. Process "B" reads a row previously written by process "A," and process "B" changes the row, making the old value read by process "A" dirty.

  • Non-repeatable Read. A process reads a row twice within a transaction and gets two different values.

  • Phantom Problem.  A set of rows read by a process may change during a transaction.

The isolation level settings below can be specified in SET TRANSACTION.

Setting Description
READ UNCOMMITTED Using this level, it is possible to get a dirty read, a non-repeatable read, and a phantom problem. No locks are set by SQL retrieval operations, and UPDATE, INSERT and DELETE are not permitted.

D3 SQL using this level as a default when processing queries in auto-commit mode. If a higher isolation level is required, the user must bracket the query within a manually initiated transaction.

READ COMMITTED Using this level, dirty reads are prevented, but it is possible to have a non-repeatable read and a phantom problem. The retrieval of each row sets a lock while examining the row, then releases it when the next row is encountered. Updates to a row leave a lock on that row until the transaction completes.

D3 SQL uses this level as a default when in manual commit mode, or when an UPDATE, DELETE or INSERT operation is performed.

REPEATABLE READ Using this level, only a phantom problem can exist. The locking is identical to READ COMMITTED, except that retrieval locks are held until transaction completion.
SERIALIZABLE This level exhibits none of the above problems. Locks set for REPEATABLE READ are used, and predicate locks are used to lock entire tables when sequential scans must be processed. Note that this mode essentially locks every table accessed throughout the transaction and therefore severely limits the multiprocessing abilities of the machine.

SERIALIZABLE is the only isolation level that guarantees the full consistency of transactions. Serializable transactions are flushed atomically to the disk drive at commit time. If this operation is interrupted by a power outage or other machine failure, the transaction is recommitted automatically when the database is restarted. This is known as an ACID transaction.

Additional Isolation Level Issues

Higher levels of isolation ensure greater degrees of multi-user data consistency, but related data must also be protected if a single user’s connection or an entire server is halted unexpectedly. The potential problems below must be considered in selecting an appropriate isolation level.

Single Row Inconsistency

In this situation, a single row is corrupted so that the data it contains is unreadable or invalid. The D3 database engine protects against this possibility across all transaction isolation levels, even if the server is completely terminated by an external event, such as a power outage. This protection extends only to tables stored on D3 servers. The D3 database engine cannot protect externally referenced tables that are not stored within a local or remote D3 server file space.

Multirow Inconsistency

In this situation, a transaction has completed, and although each row involved contains valid data, some of the rows may not be in their final updated states. In other words, some of the rows may reflect the pre-transaction state, while others may reflect their post-transaction state. The D3 database engine protects against this possibility across all transaction isolation levels, except when the server is completely terminated by an external event, such as a power outage, during a commit operation. To obtain full protection in the event of a complete server halt, the SERIALIZABLE isolation level must be used.

Access Mode (syntax part)

SET TRANSACTION also allows you to set the access mode to read-only or read/write. Any UPDATE, INSERT or DELETE statements attempted while in read-only mode will fail.