SET TRANSACTION

Use the SET TRANSACTION command to set the access mode for the current transaction. The change does not affect any subsequent transactions. If you want to change the access mode for all the transactions of a session, use the SET SESSION command.

Syntax

The SET TRANSACTION command has the following syntax:
SET TRANSACTION { READ WRITE | READ ONLY | ISOLATION LEVEL { SERIALIZABLE
                  READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ } }

Inputs

The SET TRANSACTION command takes the following inputs:
Table 1. SET TRANSACTION inputs
Input Description
READ WRITE Allows a user to read and update a database. This is the default access mode.
READ ONLY During the session, a user can read from a database but not write to it. If the user attempts to write to the database, the system returns an error code. The user can create and write to temporary tables.
ISOLATION LEVEL The isolation level of a transaction determines what data that transaction can see when other transactions are running concurrently. When the isolation level is serializable, the current transaction can see only rows that are committed before the first query or data-modification statement is started in this transaction. Consequently, two concurrent transactions leave the database in the same state that they would if the two transactions ran one after the other in either order.
The isolation level of a transaction is always SERIALIZABLE. You can specify any of the following isolation levels, but regardless of which you specify, SERIALIZABLE is used:
  • SERIALIZABLE
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ

Output

The SET TRANSACTION command has the following output:
Table 2. SET TRANSACTION output
Output Description
SET VARIABLE The command was successful.
Note: If you specify one of the following isolation levels, this output is shown even though the specified level is ignored and SERIALIZABLE is used instead:
  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ

Privileges

You must be an administrator, or you must be able to connect to the database and schema.

Usage

The following provides sample usage.
  • Set the transaction isolation level:
    MYDB.SCH1(USER)=> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   SET VARIABLE