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).
DBA privileges are required to execute the ISOLATION LEVEL EXCLUSIVE clause. Otherwise the SET TRANSACTION command requires no special privileges.
No clause can be specified more than once in the SET TRANSACTION statement.
SAND CDBMS SQL Transactions are read-write by default. Any SQL statement may be executed in a read-write transaction.
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.
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.
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.
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.