Menu
I am trying to find out what isolation level a particular session (not my own) has on an oracle server. Is there a v$.. view to get this?
770
To check the isolation level(s) present in the statement, the simplest way is to look at the T-SQL itself and see if any hints are present. If not, it is operating at the isolation level of the connection.
To determine the global and session transaction isolation levels at runtime, check the value of the tx_isolation system variable: SELECT @@GLOBAL. tx_isolation, @@tx_isolation; InnoDB supports each of the translation isolation levels described here using different locking strategies.
Oracle Database provides the transaction isolation levels: Read Committed Isolation Level. Serializable Isolation Level. Read-Only Isolation Level.
The default isolation level for Oracle is read committed. This degree of isolation is appropriate for environments where few transactions are likely to conflict.
You can test bit 28 in the flag column in v$transaction[1].
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, v$session s
WHERE t.addr = s.taddr
AND s.sid = :sid
AND s.serial# = :serial;
Just remember that v$transaction only lists active transactions[2]; for example, you need to issue an insert/update/delete/merge, or use "for update"[3].
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
