SAP HANA Reference
SET TRANSACTION

Syntax

 SET TRANSACTION <isolation_level> | <transaction_access_mode>

Syntax Elements

 isolation_level ::= ISOLATION LEVEL <level>

The isolation level sets the statement level read consistency of the data in the database. If <isolation_level> is ommited the default is READ COMMITTED

 level ::= READ COMMITTED | REPEATABLE READ | SERIALIZABLE

READ COMMITTED
The READ COMMITTED isolation level provides statement level read consistency during a transaction. Each statement in a transaction sees commited state of the data in the database as the execution of the statement begins. This means that each statement in the same transaction may see varying snapshots of the data in the database as they are executed as data can be commited during the transaction.

REPEATABLE READ/SERIALIZABLE
The REPEATABLE READ/SERIALIZABLE isolation level provides transaction level snapshot isolation. All statements of a transaction see the same snapshot of the database data. This snapshot contains all changes that were committed at the time the transaction started along with the changes made by the transaction itself.

 transaction_access_mode ::= READ ONLY | READ WRITE

The SQL-transaction access mode controls if a transaction can modify data during execution. If transaction_access_mode is ommited the default is READ ONLY.

READ ONLY
When READ ONLY access mode is set, then only read operations with SELECT statements are allowed. An exception will be thrown if update or insert operations are attempted whilst in this mode.

READ WRITE
When READ WRITE access mode is set, statements within a transaction can freely read or make changes to the database data as required.

Description

The SAP HANA database uses multi-version concurrency control (MVCC) to ensure consistent read operations. Concurrent read operations see a consistent view of the database data without blocking concurrent write operations. Updates are implemented by inserting new versions of data and not by overwriting existing records.

The isolation level specified determines the lock operation type that will be used. The system supports both statement level snapshot isolation and transaction level snapshot isolation.

During a transaction when rows are inserted, updated or deleted, the system sets exclusive locks on the affected rows for the duration of the transaction. The system also sets shared locks on the affected tables for the duration of the transaction. This guarantees that the table is not dropped or altered while rows of the table are being updated. The database releases these locks at the end of the transaction.

Note:
Reading a row does not set any locks on either tables or rows within the database regardless of the isolation level used.

Data Definition Language and Transaction Isolation

Data Definition Language (DDL) statements (CREATE TABLE, DROP TABLE, CREATE VIEW, etc) always take an instantaneous effect on following SQL statements regardless of the transaction isolation level being used. For an example of this behaviour please consider the following sequence:

  1. A long running SERIALIZABLE isolation transaction begins operating on Table C.
  2. From outside the transaction some DDL is executed which adds a new column to Table C.
  3. From within the SERIALIZABLE isolation transaction the new column will be accessible as soon as the DDL statement completes. This access occurs regardless of the isolation level of the transaction.

Example

You set the transaction isolation level to READ COMMITTED to provide statement level read consistency during the current transaction.

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;