How to Check Current Isolation Level in Oracle


Even when multiple users are modifying records in a database, Oracle databases keep the information consistent by transaction isolation. Queries at the "Read Committed" level of isolation can't access data someone is in the middle of writing or changing – information is only available when it's committed to the database. The Serialization level treats transactions if they happen in sequence, not simultaneously: Queries in a single transaction can't access data changes after the transaction begins except those made as part of the transaction. The "Read Only" level serializes queries but doesn't allow any changes.

  • Enter the command "V$SESSION" into Oracle. This produces the details of any current database sessions. A session is a single, continuous connection to the database that provides answers to data requests sent by a single computer application.

  • Review the database session and look for the "ALTER SESSION" command. This command changes the database configuration for a particular session and can set a new isolation level that lasts for the duration of the session. If you find ALTER SESSION, see if the alterations include "ISOLATION LEVEL" and determine the level at which the session was set.

  • Look for "SET TRANSACTION ISOLATION LEVEL" to determine if a current transaction has set a different isolation level for that transaction only. If neither the transaction nor the session has changed the isolation level, it's almost certainly at "Read Committed," according to Oracle executive and author Tom Kyte. Kyte states online that "Read Committed" is the default setting, and it's rare to find an Oracle database that doesn't use that.

Tips & Warnings

  • Serialization eliminates errors that can occur at the "Read Committed" setting, such as repeating a query and getting a different answer because the data has been rewritten between the two queries. Serialization can compromise database efficiency, however, by blocking one user from changing data until another user finishes reading it. That's one of the reasons "Read Committed" is the default setting.

Related Searches


  • Photo Credit Thinkstock Images/Comstock/Getty Images
Promoted By Zergnet



Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

Is DIY in your DNA? Become part of our maker community.
Submit Your Work!