Thursday, December 17, 2009

Some hige-level concepts about transaction processing

I once read in a forum someone proposed an interview question for Oracle DBA. The question was like 'What kind of things Oracle do when issuing a 'COMMIT' ?'. I was very vague at that time. Now I am feeling better. I went through a chapter called "Transaction Processing" in an old Oracle University guide - Oracle 9i database: Space and Transaction Management (Student Guide). Here are some high-level concepts about transaction process, including something Oracle will do when it commits a transaction.

Sytem commit number (SCN)
- identifies the committed version of a database

Change vector
- contains the information necessary to change one block from one consistent state to another consistent state

Begin transaction
  1. Undo segment binding
  2. Slot allocation in the transaction table
  3. Undo block allocation
Transaction Commit

  1.  Finds an SCN value
  2.  Updates the transaction table
  3.  Puts the current undo block into the free block pool (under some condition)
  4.  Creates a commit record in the redo log buffer
  5.  Flushes the redo log buffer to disk (for durability)
  6.  Releases locks held on rows and tables
[Dec 26,2009 Ed.]
Tom Kyte wrote a section "What Does a COMMIT Do?" in his book "Expert Oracle Database Architecture" (pp 292-98). There are two points I would like to add based on his book.

1. When Oracle issues a COMMIT, LGWR writes all of the remaining redo log entries to disk.  Considering commit after inserting one row into a table compared to after inserting 1M rows, the time for commit in these two cases is almost same, why? Because redo info is being written to the disk during the inserting process batch by batch. At COMMIT, the remaining redo entries  be flushed is thus small. The cost of COMMIT is not a function of the transaction size.

2. When COMMIT, some of the blocks the transaction modified will be visited and "cleaned out" in a fast mode if they are still in the buffer cache. Block cleanout refers to the cleaning out the transaction information stored in the database block header.

Modifying a data block

  1.  Find an interested transaction list (ITL) available in the block
  2.  Lock the row you are modifying
  3.  Generate the redo part of data describing the changes to the data block
  4.  Generate the undo part of data describing
  •  The inverse operation of data block user changes
  •  The undo block changes in the undo segment
  •  The undo segment block changes (if necessary)
    5.   Create the redo record and apply the changes to the blocks.