Oracle DBA Lesson 8 Part 2 — When an Update Becomes Permanent

A committed salary change can be durable while its table block still needs a data-file write. Oracle separates transaction durability from background table-block writes. The two paths have different purposes and destinations.

Oracle DBA Lesson 8 Part 2 — When an Update Becomes Permanent

What an update changes

Suppose employee 101 has a committed salary of 7000. An ordinary buffered update adds one:

UPDATE employees
SET salary = salary + 1
WHERE employee_id = 101;

The current working table block in the buffer cache in RAM now contains 7001. Its physical data file on disk can still contain the earlier version. That current buffer is dirty because modified contents await a data-file write. When its current contents have been written, the buffer is clean until another modification. The diagram assumes one isolated update and no later changes during the depicted write.

An update also produces:

• Undo: earlier-value information in undo blocks. These blocks are available in RAM or in physical undo data files on disk. Undo supports rollback and consistent reads.
• Redo: records describing changes to relevant database blocks, including undo changes. They enter the redo log buffer in RAM and are written to physical online redo log files. Recovery can use redo to reapply changes. A redo record describes changes rather than being the employee table's saved row.

A block can contain several rows and changes from multiple transactions. Its buffer write state and a particular transaction's outcome answer separate questions. See memory architecture.

The two writers

Writer	Input	Physical destination	Purpose
Log writer, LGWR	Redo log buffer in RAM	Online redo log files on disk	Make redo persistent, including redo needed for synchronous commit.
Database writer, DBWn	Modified buffers in the buffer cache in RAM	Data files on disk	Save changed blocks as background work, supporting buffer reuse and checkpoint progress.

See process architecture and managing the redo log.

With ordinary synchronous commit, the required remaining redo and the commit record must become durable before Oracle reports successful completion. Redo already written earlier does not need to be written again. Log writer can also write before commit, and it can combine work from multiple transactions.

COMMIT;

COMMIT ends the current transaction, makes its changes permanent, releases transaction locks, and erases its savepoints. The illustrated baseline is default WAIT IMMEDIATE behavior with unchanged commit settings. Explicit options, client behavior, or COMMIT_WAIT and COMMIT_LOGGING settings can change the wait behavior. Asynchronous commit is outside this lesson. See transactions and COMMIT.

Two possible table-block write orders

Commit first, table-block write later: required redo becomes durable, the salary transaction commits, and its buffer can still be dirty, with 7001 in RAM and the earlier 7000 in the data file. Recovery can reapply the committed change if RAM is lost. Database writer saves the block on its own schedule.

Table-block write before commit: database writer can save the changed block while the transaction is still active. Before that write, the redo associated with the block must already be on disk. This is the write-ahead rule. If necessary, database writer requests a log-writer flush and waits for it.

Both orders are valid. A file containing a changed block does not decide whether another session can see that transaction's change. Instance recovery can reapply logged changes and undo uncommitted work. Checkpoint mechanics and detailed recovery procedures are later topics. See the database instance.

What another session sees

The updating session sees its own 7001. A separate ordinary reader under READ COMMITTED sees the committed 7000 while the update is uncommitted, using undo as needed, even if database writer has saved the modified block. After commit, a new query in that reader sees 7001. A query that began earlier retains its query-start consistent view. Other isolation levels have different snapshot rules.

Transaction state	Buffer/write state that can occur
Active, uncommitted	Dirty current buffer or a block already written.
Committed	Dirty current buffer or a block already written.

Rollback uses undo to reverse the update. Applying the reversal changes buffers too, so a rollback can leave writes pending. Later modifications, including transaction metadata changes, can dirty a previously written buffer again. See data concurrency and consistency.

Visibility and restoration

Open two fresh connections to the same database context, using SQL*Plus. Use ordinary READ COMMITTED isolation and disable client auto-commit in both:

SET AUTOCOMMIT OFF
SET EXITCOMMIT OFF

These are client commands. Turning auto-commit off alone does not change SQL*Plus's default exit commit behavior. See the SQL*Plus SET command.

1. In both sessions, capture the original value:

   SELECT employee_id, salary
   FROM employees
   WHERE employee_id = 101;

2. In the updating session, set a savepoint, update exactly one row, and inspect its value:

   SAVEPOINT before_salary_demo;
   UPDATE employees
   SET salary = salary + 1
   WHERE employee_id = 101;
   SELECT employee_id, salary
   FROM employees
   WHERE employee_id = 101;

   WHERE selects the test employee. salary + 1 adds one to its current value. Expect one row affected.

3. While that update remains uncommitted, run the SELECT in the other session. It should show the original committed value, while the updating session sees its own increased value.

4. In the updating session, use the retained savepoint and then finish the transaction:

   ROLLBACK TO before_salary_demo;
   ROLLBACK;

   The first statement reverses changes after the marker and leaves the transaction active. The full rollback ends the transaction and releases the remaining transaction locks. See SAVEPOINT and ROLLBACK.

5. Repeat the SELECT in both sessions and confirm the original salary.

This exercise tests visibility and restoration, not internal buffer dirtiness or the time of a database-writer write. Avoid DDL during the transaction, because it can commit work. The COMMIT above explains the conceptual branch. Once the change is committed, it needs a separate reversal. Rolling back to the former savepoint is no longer available.

Recap

A synchronous commit makes the required redo and the commit record durable. It does not require database writer to save the employee block first. The block can remain dirty in the buffer cache, or database writer can already have saved it, after the associated redo is on disk. A separate READ COMMITTED reader still sees committed data, using undo when the current block is newer than the query needs. Two questions stay separate: whether the transaction has committed, and whether the block still requires a data-file write.

Quiz

1. What does a dirty current table-block buffer describe?

A failed transaction. Modified contents waiting for a data-file write. A damaged online redo log. A permanent inability to read the table.

2. Must database writer save the employee block before synchronous COMMIT succeeds?

No; required redo must be durable, while the table-block write can be scheduled independently. Yes; every commit writes every database block. Yes; redo becomes available only after all data files are saved. No; commit needs no persistent information.

3. What must precede a dirty table-block write?

Every reader must disconnect. The transaction must roll back. Associated redo must be written to disk. Every online log file must be archived.

4. A transaction is active, but database writer has saved its modified block. What does a separate READ COMMITTED reader see?

All uncommitted changes immediately. Whatever bytes are physically newest, regardless of transaction status. An automatic commit of the updating transaction. A committed version consistent with its query start, using undo as needed.

5. What does ROLLBACK TO before_salary_demo do?

End the transaction and erase all its savepoints. Reverse work after that savepoint while retaining the transaction. Make the update durable. Guarantee all buffers become clean.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily