Oracle supports 3 transaction isolation levels
1. Read committed (default)
A query will only be able access committed data. However, the transaction may be affected by changes made by other transactions.
This can be set by:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED; (session level)
2. Serializable transactions
A query can only access data that was committed at the start of the transaction. Modification from DML operations performed from the same transaction will be visible.
The Serializable transaction isolation level is not supported with distributed transactions.
This can be set by:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE; (session level)
3. Read only
A query can only access data that was committed at the start of the transaction. Modification to the data is not allowed.
This can be set by:
SET TRANSACTION ISOLATION LEVEL READONLY; (transaction level)
ALTER SESSION SET ISOLATION_LEVEL READONLY; (session level)
3 comments:
Hi. I tried to find how to check (not set) with which isolation level i'm currently working in my session/transaction, but without any result... Do You know how to check this?
Hi,
Just check the alter session command syntax, There should be "=" when you set value.
ruzkow,
I found this statement handy to identify the current isolation level. Unfortunately the flag field is not well documented.
SELECT s.sid, s.serial#,
CASE BITAND(t.flag, POWER(2, 28))
WHEN 0 THEN 'READ COMMITTED'
ELSE 'SERIALIZABLE'
END AS isolation_level
FROM v$transaction t
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');
Post a Comment