Home » RDBMS Server » Server Administration » Is my understanding regarding transactions correct ?
Is my understanding regarding transactions correct ? [message #245153] Fri, 15 June 2007 05:53 Go to next message
Lavjeet
Messages: 5
Registered: June 2007
Junior Member
I'm struggling to understand how transactions, redo and rollback all fit together.

My understanding of the process is as follows:


I issue an insert/update/delete, basically some DML. This grabs a slot in the transaction table slot in a rollback segment header which is initially flagged as ACTIVE (i.e. uncommitted) so the undo shouldn't be overwritten.

The "before" image of the blocks that are changed by the DML statement are written into blocks in the rollback segment.

Because these constitute changes to the rollback segment blocks these change vectors are written to the redo log buffer.

An ITL entry is created in the headers of the data blocks that are changed by the DML. This ITL contains a pointer to the transaction table slot in the rollback segment.

These changes to the data blocks are written to the redo log buffer.


Let's now say the user has not yet committed these changes, and LGWR now writes the redo log buffer contents to the online redo log files.

At this point I think I am OK. If the instance crashed then, on recovery SMON would firstly reconstruct the undo from the change vectors to the rollback block and reconstruct the changes to the data blocks but would then rollback the changes because the transaction table slot in the rollback segment header shows that this transaction is not comitted.

Is this correct?


OK moving on - what exactly happens when the user issues a COMMIT? The 9i concepts states that it writes the SCN to online redo log files? How exactly is this achieved - is it just a "line" in the redo log file and not a change vector?

My guess is that this is what happens:


When the user issues a commit, the transaction table slot in the rollback segment is changed from ACTIVE (uncommitted) to INACTIVE (committed). This constitutes a change to the block containing the rollback segment header. And this change is written away to the redo log buffers.

So when rolling forward using the online redo log files, for this transaction we have:

1) rollback segment header(uncommitted)
2) change vectors to reconstruct "before image" undo blocks
3) change vectors to change data blocks
3) rollback segment header(committed)


I'm sure this is an over simplified (and probably wrong) version. Can someone please clarify this for me?

My other major area of confusion in all of this is where do SCN's fit in, when are they allocated?
Re: Is my understanding regarding transactions correct ? [message #245155 is a reply to message #245153] Fri, 15 June 2007 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
At this point I think I am OK. If the instance crashed then, on recovery SMON would firstly reconstruct the undo from the change vectors to the rollback block and reconstruct the changes to the data blocks but would then rollback the changes because the transaction table slot in the rollback segment header shows that this transaction is not comitted.

Is this correct?

Yes.

Quote:
what exactly happens when the user issues a COMMIT? The 9i concepts states that it writes the SCN to online redo log files? How exactly is this achieved - is it just a "line" in the redo log file and not a change vector?

This is a kind of change vector.

Quote:
When the user issues a commit, the transaction table slot in the rollback segment is changed from ACTIVE (uncommitted) to INACTIVE (committed). This constitutes a change to the block containing the rollback segment header. And this change is written away to the redo log buffers.

This is also true but there is also a record for the commit.

I don't understand your last sentence .

Regards
Michel
Re: Is my understanding regarding transactions correct ? [message #245158 is a reply to message #245155] Fri, 15 June 2007 06:06 Go to previous messageGo to next message
Lavjeet
Messages: 5
Registered: June 2007
Junior Member
Hi Michel,

Thanks for the quick reply .

I am not sure where do SCN's fit in this scenario and how do they affect it ?

Thanks and Regards,
Lavjeet
Re: Is my understanding regarding transactions correct ? [message #245195 is a reply to message #245158] Fri, 15 June 2007 07:34 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I still don't understand the sentence (sorry surely lack in my english).
What I can tell is that SCN is incremented each time there is something like a commit (user, internal...) when the instance has to note that something new has been permanently recorded in the db.

Regards
Michel
Previous Topic: how to drop datafile i've allready droped the tablespace in which my datafile reside
Next Topic: Switching UNDO tablespace
Goto Forum:
  


Current Time: Sat Dec 10 18:26:50 CST 2016

Total time taken to generate the page: 0.08483 seconds