Oracle Transaction

Now that we have looked at the Oracle architecture, we will look at a sample transaction in detail to see how everything works together. This sample transaction is shown in Figure

The following covers a basic transaction inside Oracle:

1. A user logs starts SQL*Plus and enters a username, password, and database name (referred to as a connect string). On the client side, Oracle finds the database name in tnsnames.ora. Using the specified IP address, port number, and connect string, the user process establishes a connection with the database server.

2. The Oracle listener process on the database server receives the user process connection. It looks for the databases in listener.ora and routes the user process to the specified database. At this point we are assuming a dedicated (not MTS) connection.

3. A new server process inside the database is spawned and establishes contact with the user process. At this stage the listener process is no longer involved. After password authentication, the user process is connected.

4. The user process issues a SQL statement to update a row of data in a table. This statement goes across the network to the dedicated server process.

5. Information inside the PGA for the server process is updated to reflect the new SQL statement.

6. The server process runs a quick hash algorithm on the statement and receives a value. It then scans the shared SQL area for any statements with the same hash value. For this example, it does not find a matching hash value.

7. The server process scans and finds a free area within the shared SQL area to work. It parses the user’s SQL statement and checks the syntax. Oracle verifies that the table exists in the data dictionary and verifies the user’s object-level permissions on the table being modified. Then it looks at the statement and the stored statistics it has about the table and any indexes to develop an execution plan. Oracle then issues an exclusive lock on the row of data and attempts to execute the statement. Oracle reads the current System Change Number (SCN) for the system and uses this value to maintain consistency

throughout the transaction.

8. The server process scans the database buffer cache to see if the data block is already cached. In our example the server process does not find a copy of the block in the buffer cache, so it must read in a copy.

9. A scan of the database buffer cache finds a consistent data block buffer that can be reused. The server process follows the prescribed execution plan and retrieves the block containing the row to be modified. It overwrites the buffer cache buffer with the new block. The block is marked as being at the MRU end of the Least Recently Used list. In Oracle 8i it is believed that a block will be placed in the middle of the LRU list using Mid Point Insertion, but there isn’t enough public information to confirm this.

10. A slot in the rollback segment header is briefly acquired, and space in a rollback segment extent is found. The block of this rollback segment extent is moved to the database buffer cache in a similar manner as in step 8. An UPDATE statement to undo the user’s UPDATE statement is generated and placed in the rollback segment. Undo for the update to the row’s index is also generated and placed in the rollback segment.

11. The row of data is actually modified in the database buffer cache memory. Index and rollback

segment buffers are also inside the buffer cache.

12. The server process finds space and writes the change to the redo log buffer. This includes both the modified data and the contents of the rollback segment.

13. At this stage the user who issued the statement can see the change with a SELECT statement. Any other user issuing a SELECT statement will see the row as it was before step 4. The block containing the modified row is now considered dirty because it has been modified but not yet committed. If another user attempts to issue a statement to modify the same row, that session will seem to hang because it is waiting on the first user to release the row exclusive lock acquired in step 6.

14. The user types the COMMIT command at the SQL*Plus prompt and presses Enter. This is considered an explicit commit and is a signal to Oracle to make permanent any changes made by the user. What if the user types the word EXIT at the SQL*Plus prompt and presses Enter? This is an implicit commit because the user is existing normally. The changes to the data will be made permanent.

15. The Oracle server process receives the instruction to commit the row update. A unique System Change Number is assigned to the transaction in the rollback segment transaction table and in the redo log buffer. LGWR writes everything in the redo log buffer to the active online redo log file(s). Once the data is written to the redo log file(s) and Unix has confirmed the write to be successful, Oracle considers the transaction complete and the change permanent. If a database crash were to occur, the changes to the data would still be recovered.

16. DBWR will eventually write every dirty block in the buffer cache to disk, but that may not necessarily happen yet. In fact, the modified blocks may already have been written to disk. This will occur at the normal time when DBWR writes. A user commit does not force DBWR to write. The modified blocks may still reside in the database buffer cache, but the transaction is considered complete by Oracle because LGWR successfully wrote to the online redo log.

17. The row-level lock held by the user is released. The user receives a message stating the commit was successful.

18. The other statement (in step 13) waiting to update the row will now receive an exclusive row lock, and the steps starting at step 6 may occur.

19. The first user issues an EXIT statement in SQL*Plus. This causes any new DML statements to be committed. Next the Oracle server process and the corresponding user process terminate. Memory

resources and any locks held in the user’s PGA/UGA are released back to the system.

Note that this was discussed only at the Oracle level and that we did not yet mention how the memory and disk are accessed at the Unix level. Relatively simple transactions such as this occur very frequently and involve many steps. This should underscore the need for a highly tuned system because any inefficiency could result in noticeable performance problems.