Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments, but we might still sneak in a few Office Hours sessions! Thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Ainara.

Asked: September 25, 2024 - 12:35 pm UTC

Last updated: October 04, 2024 - 4:54 am UTC

Version: oracle 19c

Viewed 100+ times

You Asked

Hello, Tom!

My colleague and I were discussing a scenario in Oracle 19c, and we had differing opinions, so we'd appreciate your insight.


In an Oracle 19c database, consider the following scenario:

User A performs data modifications and commits the changes.
User B performs data modifications but does not commit the changes.
Shortly after, the database experiences a crash.
Upon recovery of the database, will the uncommitted changes made by User B be recoverable, or will they be lost? Could you explain the behavior of committed and uncommitted transactions in this context, and how Oracle handles these during the recovery process?

Thank you in advance!

Sincerely,
Ainara

and Connor said...

An uncommitted transaction can be thought of as a transaction that never existed.... so on database resumption, it will be as if the transaction never started.

Of course, in reality, its likely that many database blocks (even on disk) were changed as part of that transaction, so when the database comes back online we need to sort out that mess.

*Every* change in the database, even the changes to the undo area to allow a rollback of a transaction are captured in the redo logs. Thus when the database starts, using the redo logs we can put the *undo* area back to the same state it was the moment the database crashed.

Inside that undo area is the information we need to undo any uncommitted transactions, so we go ahead and do that to make sure no mess is left lying around in the database.

Hope that helps.

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Quick Confirmation on Transaction Recovery in Oracle 19c

Ainara, October 01, 2024 - 12:53 pm UTC

Hi Conor,

thanks for your previous insights regarding the scenario in Oracle 19c. I wanted to confirm your explanation: after a database crash, any uncommitted changes made by User B will indeed be lost and not recoverable, as it will be as if the transaction never occurred even redo log has the changes. Could you please confirm this understanding? Thanks again for your help! Best, Ainara
Connor McDonald
October 04, 2024 - 4:54 am UTC

That is correct.

An uncommitted transaction is invisible to anyone except the session that currently has it active. If that session disappears (eg database crash, or even just session crash) then those changes never happened.

(Behind the scenes we must do work to sort out the mess, but from the observers perspective, it never happened)