OCP 1Z0 052 178

178. All the database users are presently connected to the database instance and working. The HR user 
has opened three database sessions and executed the following command in one of his sessions: 
SQL> UPDATE persons SET ccode='U031' WHERE ccode='U029'; 
123 rows updated. 
SQL> DELETE FROM persons WHERE exp='Y'; 
3 rows deleted. 
The SYS user opens a new session after HR executed the above commands. Which sessions can see 
the effect of the UPDATE and DELETE commands? 
A.all sessions of the HR user only  
B.all sessions of the HR user and the SYS user 
C.the session of the HR user that executed the commands 
D.all the sessions for which the database users have access privilege to the PERSONS table 
Answer: C

未提交数据，只有当前会话可见。

Transaction Interaction and Isolation Level

The ANSI/ISO SQL standard defines three kinds of transaction interaction:

Transaction InteractionDefinitionDirty readTransaction A reads uncommitted changes made by transaction B.Unrepeatable readTransaction A reads data, transaction B changes the data and commits the changes, and transaction A rereads the data and sees the changes.Phantom readTransaction A runs a query, transaction B inserts new rows and commits the change, and transaction A repeats the query and sees the new rows.

The kinds of interactions that a transaction can have is determined by its isolation level. The ANSI/ISO SQL standard defines four transaction isolation levels.Table 2-5 shows what kind of interactions are possible at each isolation level.

Table 2-5 ANSI/ISO SQL Isolation Levels and Possible Transaction Interactions

Isolation LevelDirty ReadUnrepeatable ReadPhantom Read

READ UNCOMMITTED

Possible

Possible

Possible

READ COMMITTED

Not possible

Possible

Possible

REPEATABLE READ

Not possible

Not possible

Possible

SERIALIZABLE

Not possible

Not possible

Not possible

Table 2-6 shows which ANSI/ISO SQL transaction isolation levels Oracle Database provides.

Table 2-6 ANSI/ISO SQL Isolation Levels Provided by Oracle Database

Isolation LevelProvided by Oracle Database

READ UNCOMMITTED

No. Oracle Database never permits "dirty reads." Some other database products use this undesirable technique to improve thoughput, but it is not required for high throughput with Oracle Database.

READ COMMITTED

Yes, by default. In fact, because an Oracle Database query sees only data that was committed at the beginning of the query (the snapshot time), Oracle Database offers more consistency than the ANSI/ISO SQL standard for READ COMMITTEDisolation requires.

REPEATABLE READ

Yes, if you set the transaction isolation level to SERIALIZABLE.

SERIALIZABLE

Yes, if you set the transaction isolation level to SERIALIZABLE.