dml by multiple users/commit scenarios in oracle

Question

Hi, How does oracle treats dml statements executed by multiple users on the same data object?. Suppose,

If there is a empty table named EMP(empname varchar2(30)) and user 'A' makes an entry into it

using,

insert into emp values('A');

but hasn't committed it yet.

If another user 'B' logged into the same database commits, would he/she sees the dml operations performed by 'A'(i.e, 'A' in the EMP table)?

Other scenarios/links related to this topic would be much appreciated. Thanks in advance.

Answer 1

Transactions are not seen outside of your session until you commit. If another user logs in, then they will see the table as it was stored in the database at its last committed state. That is the reason for undo space, redo logs, etc. In your scenario, User B would not see A's transaction even though B has committed his, because A has not committed their changes.

Answer 2

For full details of Data Concurrency and Transactions in Oracle see the Oracle Concepts Guide.

Answer 3

It is possible for multiple insert dml operations to collide even though uncommitted. If the insert into emp above is inserting into a uniquely indexed column (e.g., a primary key), then until committed or rolled back user B cannot insert the same value - it will block. So, in a sense, user B "sees" the uncommitted insert by A, although user B cannot select the values inserted by A.