isolation level of Oracle.

Exactly what are you asking?  There isn't a parameter you can set if that is what you are asking.

Take a look in the docs:
https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/data-concurrency-and-consistency.html#GUID-2A0FDFF0-5F72-4476-BFD2-060A20EA1685

The SQL standard defines four levels of isolation in terms of the phenomena that a transaction running at a particular isolation level is permitted to experience.

hi,
tks man.

"  The Oracle default is that transactions execute with an isolation level of READ-COMMITTED unless the user (SQL) specifically changes it."

any script to verify this result ?

yeah, only oracle now.

hi,

this return nothing for me:

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');

Select allOpen in new window

so what it means ? do you mean I have to allow application to connect to it and see the isolation level in run time ?

Sara,

"- both performing a query on same commited data parallel
- performing an update by client 1 while client 2 still can read the (old) committed data
  as long as client 1 didn't commit  
- performing an update by client 1 while client 2 failed to update same data
  as long as client 1 didn't commit and client 2 didn't read the new committed data
"

I am sorry, what can I verify by doing this ?

"and not commit!"

you mean do DML statement inside set transaction and commit; ?

so once commit, the isolation level done as query all set ?

"you mean do DML statement inside set transaction and commit; ?" - I have done it this way.

what can I verify by doing this

'read-committed' level means that multiple clients will read only committed data, which were committed before the begin of the transaction.

so two clients can read - even if there are "dirty" records (means non-committed updates from a time after the own transaction has begun).

• you can make a test that two clients can perform the same query same time without problems.
• you can make a second test that the first client makes some updates after read but no commit and that second client still can read the (old) committed data.
• you can make a third test that the first client commits and the second client would fail continuing a query which has begun before commit.
• you can make a last test that the second client succeeds if it does a roll-back on fail and restarts the query.

if all the tests would have the expected result, you have verified that the default isolation level is 'read committed'.

Sara

tks.

>>this return nothing for me:

It means you don't have any active transactions.

>>do you mean I have to allow application to connect to it and see the isolation level in run time ?

Yes.  As posted above several times:  This isn't a "database" setting.  It is a connection/transaction setting.

>>so two clients can read - even if there are "dirty" records

Oracle cannot perform a dirty read.

"It means you don't have any active transactions."

yeah, as I can expect

"This isn't a "database" setting.  It is a connection/transaction setting."

yeah.

"Oracle cannot perform a dirty read."

because of the read  lock  and it is read committed ?

as long as the update made by client 1 is not committed they can read the old committed data from the rollback data. Oracle cannot read uncommitted data which are called dirty.

>>because of the read  lock  and it is read committed ?

Read up on ACID:  https://en.wikipedia.org/wiki/ACID_(computer_science)

>>as long as the update made by client 1 is not committed they can read the old committed data from the rollback data.

Clarification:  "they"  cannot read the old committed data.  ALL other connections/transactions see the previous committed data.  The transaction that performs the update sees the updated uncommitted data.  The transaction that performed the update cannot see the previous values.

sarabande,

"Oracle cannot read uncommitted data which are called dirty."

yeah, dirty !

slightwv,

'"they"  cannot read the old committed data.  ALL other connections/transactions see the previous committed data'

old = previous, right ?

>>old = previous, right ?

If that helps you, then sure.

If sessionA updates a column value from 1 to 2 and hasn't committed it.  ALL other users can ONLY EVER see the value 1.  ONLY sessionA can see the value 2.  sessionA CANNOT see 1.