SET TRANSACTION

SET TRANSACTION

Syntax

set_transaction ::=

Description

The SET TRANSACTION statement is used to set the current transaction as read-only or read/write, or to set its isolation level.

Setting the isolation level to READ COMMITTED or SERIALIZABLE combines a row-level access method with a method of maintaining multiple versions of records, thereby realizing excellent data consistency, concurrency, and performance.

The changes made using the SET TRANSACTION statement affect only the current transaction, not other users or other transactions.

The isolation level can be set to any of the three levels described below.

READ COMMITTED

This isolation level allows data within a table that has been changed by a committed transaction to be read, while also allowing other transactions to read the previous version of data that will be changed by a transaction that has not been committed. READ COMMITTED is the default Altibase transaction isolation level.

REPEATABLE READ

Because a read transaction maintains a shared lock on the data it retrieves until the transaction is complete, other transactions are prevented from changing these data. Locking records in this way guarantees that when a value is repeatedly retrieved, it will always be the same as the first time it was read. However, it is possible that other transactions will generate new records that satisfy the search conditions while such a lock is held. These records will be found on subsequent searches by the read transaction holding the lock, even though they were not found on the original search. This phenomenon is known as "Phantom Reads".

SERIALIZABLE

This is the highest isolation level. This isolation level avoids locking all data when some records are retrieved using a SELECT statement with a ranged WHERE clause, but rather locks all data having key values that fall within the range of the data being read. This has the effect of preventing the "phantom reads" phenomenon and guaranteeing transaction isolation.

Considerations

This statement cannot be used when the current mode is AUTOCOMMIT mode.

This statement cannot be used if there are any active transactions.

Examples

iSQL> AUTOCOMMIT OFF;
Set autocommit off success.

iSQL> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Command execute success.

iSQL> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Command execute success.

Transaction A	Time Point	Transaction B
iSQL> AUTOCOMMIT OFF; Set autocommit off success.		iSQL> AUTOCOMMIT OFF; Set autocommit off success.
iSQL> SET TRANSACTION READ ONLY; Command execute success.	1
iSQL> SELECT e_last name FROM employees WHERE eno = 20; E_LASTNAME ------------------------ Blake 1 row selected.	2
	3	iSQL> UPDATE employees SET e_lastname = 'Jung' WHERE eno = 20; 1 row updated.
iSQL> SELECT e_lastname FROM employees WHERE eno = 20; E_LASTNAME ------------------------ Blake 1 row selected.	4
	5	iSQL> commit; Commit success.
iSQL> SELECT e_lastname FROM employees WHERE eno = 20; E_LASTNAME ------------------------ Jung 1 row selected.	6