SET TRANSACTION sets the characteristics of all subsequent transactions on the system.
SET TRANSACTION transaction_action [, transaction_action]
transaction_action ::=
ISOLATION LEVEL isolation_level
| access_mode
isolation_level ::=
READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE
access_mode ::=
READ ONLY | READ WRITE
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. |
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.