In Oracle Sql, Why Are The Deleted (uncommitted) Rows Not Visible In Current Session But Visible In Other Sessions?

When a DELETE query is executed, from where exactly is the data deleted? Why is the data still visible in other sessions? What are the background processes that are carried out whe

Solution 1:

As well as the discussion of ACID properties to which ziesemer referred you, you should know about Multi-Version Concurrency Control or MVCC. If you want to know 'all about it', consider reading Concurrency Control and Recovery in Database Systems by Philip A. Bernstein, Vassos Hadzilacos, Nathan Goodman (available for download).

In Oracle and other similar MVCC DBMS, when you start a transaction, a note is kept of a timestamp which identifies that start time (but the timestamp value is not necessarily a simple 'seconds since the Unix Epoch' value). While that transaction is running, it will not see any data inserted with a timestamp after its start time. The system keeps previous versions of the data pages available for transactions that are still running. When the transaction writes a page, it creates a new copy with a new timestamp, but that copy is not made available to other transactions until the modifying transaction commits, and even then, it is only made available to transactions that start after the commit.

Solution 2:

You may want to familiarize yourself with ACID - specifically, Isolation. If you want it to be visible to other sessions, be sure to do a commit after the delete.

The data is still visible in the other session by design, due to the standards linked above.

Solution 3:

the row is deleted for the local session as soon as the command is issued, but not for other sessions until COMMIT.