Oracle Database Discussions

After a SQL UPDATE statement is sent to a server process from a client process, what happens in the database?

Let's assume I'm updating the status of an employee from 'ACTIVE' to 'INACTIVE'. The data block containing the row for that employee is already in the database cache buffer.

The following is my understanding:

1. The server process obtains a SCN.
2. The server process reads the data block in the database cache buffer.
3. The server process locks the target row stored in the data block.
4. The server process writes the original value 'ACTIVE' to a rollback segment.
5. The server process writes the original value 'ACTIVE' to a redo buffers.
6. The server process modifies the target row stored in the data block by updating the status to "INACTIVE" and writing the SCN to the ORA_ROWSCN pseudocolumn.

If the client process sent a SQL COMMIT statement to the server process,

7. LGWR writes the redo buffers to redo log file on disk.
8. The server process sends a message to the client process that the transaction is committed.


I have the following questions:

1. How does the target row get locked in step 3? Is there a pseudocolumn for lock in each row?
2. What other data is written to the rollback segment in step 4 besides the original value?
3. What other data is written to the redo buffers in step 5 besides the original value?
4. Does the SCN obtained in step 1 being written to anywhere else besides the data block in the database cache buffer? If yes, when and where?
5. How does the server process in step 8 know that the transaction is committed?

Edited by: pcsql on Dec 11, 2009 8:03 PM