SET TRANSACTION - Define transaction attributes

You can use SET TRANSACTION to set the isolation or consistency level and transaction mode for the subsequent SQL transaction.

The isolation or consistency level of a transaction specifies to what degree read operations on rows in the transaction are affected by simultaneous write accesses in a concurrent transaction.

The transaction mode allows you to specify whether table rows can only be read or can also be updated in the subsequent transaction.

CAUTION!If you define an isolation or consistency level, you also influence the degree of concurrency and thus performance: the fewer phenomena you permit, the lesser the degree of concurrency.

The settings made by SET TRANSACTION are only valid for the SQL statements of the immediately following transaction. After the transaction has ended or has been rolled back, the default values remain valid (see "Default values"). The default settings also continue to apply after the end of the transaction when the transaction which follows SET TRANSACTION only contains CALL DML statements, i.e. no SQL statements.

The SET TRANSACTION statement does not initiate a transaction and can only be used outside an SQL transaction.

---

SET TRANSACTION { level [[,] transaction_mode ] | transaction_mode [[,] level ] }

transaction_mode ::= { READ ONLY | READ WRITE }

level ::= { ISOLATION LEVEL isolation-level | CONSISTENCY LEVEL consistency_level }

isolation-level ::=

{
   READ UNCOMMITTED |
   READ COMMITTED |
   REPEATABLE READ |
   SERIALIZABLE
}

---

You can omit the comma between the two specifications. If, however, you want your application to be portable, you must include the comma.

ISOLATION LEVEL

Sets the isolation level.

If several transactions work with the same tables simultaneously, the following phenomena can occur in which the read accesses in one transaction are affected by the simultaneous write access of another transaction. By specifying an isolation level, you determine which of these phenomena you want to permit in the subsequent SQL transaction.

The following phenomena are of importance:

• • dirty read:
    A transaction updates a row or inserts a new row. A second transaction reads this row before the first transaction has committed the update. If the first transaction is rolled back, the second transaction has read a row that was never committed.

  • non-repeatable read:
    A transaction reads a row. Before this transaction is terminated, a second transaction updates or deletes this row and commits the update. If the first transaction then tries to read this row again, either different values will be returned, or an error occurs because the row has been deleted in the meantime. In other words, the result of the second read operation is different to the result of the first.

  • phantom:
    A transaction reads rows that satisfy a certain search condition. A second transaction subsequently inserts rows that also satisfy this search condition. If the first transaction repeats the query, the derived table includes the new rows.

READ UNCOMMITTED

Isolation level that offers the least protection against concurrent transactions. All the above-mentioned phenomena are possible. In the subsequent SQL transaction, rows can be read that have not yet been committed and these rows can be updated after they have been read.

You cannot specify READ UNCOMMITTED if, at the same time, you specify the transaction mode READ WRITE.

READ COMMITTED

The phenomena “non-repeatable read” and “phantom” can occur. In the subsequent SQL transaction, rows that have been read can be updated by other transaction after they have been read. No rows are read that have not yet been committed.

REPEATABLE READ

The phenomenon “phantom” can occur. The phenomena “non-repeatable read” and “dirty read” are not possible.

SERIALIZABLE

Complete protection against concurrent transactions is ensured. The phenomena dirty read, non-repeatable read,and phantom cannot occur. The subsequent transaction is unaware of the existence of concurrent transactions.

CONSISTENCY LEVEL

For reasons of upward compatibility with earlier versions, SESAM/SQL provides the clause CONSISTENCY LEVEL as an alternative to isolation level. This means that you define a consistency level which, like the isolation level, determines whether the phenomena “dirty read”, “non-repeatable read” and “phantom” can occur.

consistency_level

Unsigned integer, where 0 <= consistency_level <= 4.

Level	Locks set	Rows read
0	Rows read are not locked against updating by other transactions	All rows including those locked against updating by other transactions
1	Rows read are locked against updating by other transactions (until the end of the transaction) unless they are already locked	like 0
2	like 0	Only the rows that other transaction have not locked against updating
3	Rows read are locked against updating by other transactions (until the end of the transaction)	like 2
4	Rows read are locked just as for level 3. The lock against updating by other transactions for nonexistent rows ensures that rows cannot be inserted by other transactions.	like 2

Table 53: Consistency levels

The following table indicates the correlation between isolation and consistency level and which phenomena can occur at the different consistency and isolation levels.

Isolation level	Consistency level	dirty read	nonrepeatable read	phantom
READ UNCOMMITTED	0	x	x	x
-	1	x	x 1	x
READ COMMITTED	2	-	x	x
REPEATABLE READ	3	-	-	x
SERIALIZABLE	4	-	-	-

Table 54: Correlation between isolation level, consistency level and phenomena

¹The phenomenon non-repeatable read can only occur for rows which were previously read with dirty read.

READ ONLY

Sets the transaction mode READ ONLY.

Only read database accesses are permitted within the transaction. READ ONLY is the default value for the isolation level READ UNCOMMITTED and the consistency levels 0 and 1.

READ WRITE

Sets the transaction mode READ WRITE.

Only read and write database accesses are possible in the transaction. READ WRITE is the default value for the isolation levels READ COMMITTED, REPEATABLE READ and SERIALIZABLE and for the consistency levels 2, 3 and 4.

You cannot specify READ WRITE if you specify the isolation level READ UNCOMMITED.

Default values

If a connection module entry exists for the isolation or consistency level in the user-specific configuration file (see the “ Core manual”), this value is used as the default. If this is not the case, the isolation level SERIALIZABLE, the consistency level 4 and the transaction mode READ WRITE are the default values.

You can use the MAX-ISOLATION-LEVEL operand of the DBH option TRANSACTION-SECURITY to set the isolation level REPEATABLE READ for a DBH. If your SQL statement works with a DBH set in this way, one of the following constraints must be fulfilled:

• • the configuration file must contain the connection module parameter ISOL-LEVEL=REPEATABLE-READ (or a lower isolation level)
    or

  • you must limit the isolation level to REPEATABLE READ using the SQL statement SET TRANSACTION prior to each transaction.

Scope of validity under openUTM

In a UTM application, the statement SET TRANSACTION is no longer valid once the current UTM transaction terminates. Since only one database transaction can run in a UTM transaction, SET TRANSACTION and the associated SQL transaction must be performed in the same UTM transaction.