The SET TRANSACTION statement dictates whether or not a transaction will be READ ONLY. Exclusive use of the database instance can also be established with this command. (Consult Other Topics: Transactions for further information on transactions).
Required
Privileges
DBA privileges are required to execute the ISOLATION LEVEL EXCLUSIVE clause. Otherwise the SET TRANSACTION command requires no special privileges.
Syntax
Note:
No clause can be specified more than once in the SET TRANSACTION statement.
READ WRITE
SAND CDBMS SQL Transactions are read-write by default. Any SQL statement may
be executed in a read-write transaction.
READ ONLY
READ ONLY makes a transaction read-only. Only SELECT statements may be executed
in a read-only transaction. Any SQL statement executed within a read-only
transaction that alters the database will result in an error.
ISOLATION LEVEL EXCLUSIVE
When ISOLATION LEVEL EXCLUSIVE is specified, no other client connections to
the database server may be made until the end of the transaction. The current
user must have the only client connection to the database server when this
command is executed.
ISOLATION LEVEL EXCLUSIVE IMMEDIATE
When the IMMEDIATE keyword is appended to the ISOLATION LEVEL
EXCLUSIVE clause, all concurrently running queries are terminated immediately
and all other clients are disconnected from the database server. No other
client connections can be established with the database server until the end
of the transaction.
TIMEOUT
The TIMEOUT clause specifies the amount of time (in seconds) the database
server will wait before cancelling a running SQL query, which is useful for
preventing "runaway" queries from tying up server resources indefinitely.
The value specified must be an integer between 0 and 2,147,483,647
inclusive. By default, there is no limit on the length of time a query may
execute.
Description
SET TRANSACTION must be the first statement in a transaction
if it is to be used at all. A new transaction is always read-write by default,
unless its first statement is SET TRANSACTION READ ONLY.
A COMMIT or a ROLLBACK statement is used to end a transaction. A SET TRANSACTION statement can also be used to end one transaction with an implicit ROLLBACK, and to start a new one. Use only ROLLBACK to conclude read-only transactions.
A user with DBA privileges can execute the SET TRANSACTION...ISOLATION LEVEL EXCLUSIVE command to gain exclusive access to the running database instance. In order for the SET TRANSACTION statement to execute successfully, the user issuing the command must have the only connection to the database. If other users have concurrent sessions, the command will fail.
Note that read-only transactions demand less overhead for concurrency control. Use of read-only transactions in decision-support applications provides increased transaction throughput and better CPU utilization.
Example
SET TRANSACTION READ ONLY;
DELETE FROM state;
Error state 25006. Execution failure - the transaction is read only
SET TRANSACTION READ WRITE;
DELETE FROM state;
4 rows affected
In this example, a transaction is set to READ ONLY. An attempted delete is refused by the system. In order to successfully execute the DELETE statement, the first transaction must be terminated, and a new one started in read-write mode. Note that the second SET TRANSACTION statement ends the first transaction with an implicit ROLLBACK before starting a new transaction in read-write mode.