How Transactions Work?

To give you a better idea how Oracle operates, this section analyzes a sample transaction. Throughout this book, the term transaction is used to describe a logical group of work that can consist of one or many SQL statements and must end with a commit or a rollback. Because this example is of a client/server application, SQL*Net is necessary. The following steps are executed to complete the transaction:

1. The application processes the user input and creates a connection to the server via SQL*Net.

2. The server picks up the connection request and creates a server process on behalf of the user.

3. The user executes a SQL statement or statements. In this example, the user changes the value of a row in a table.

4. The server process checks the shared pool to see whether there is a shared SQL area that has this identical SQL statement. If it finds an identical shared SQL area, the server process checks whether the user has access privileges to the data. If so, the server process uses the shared SQL area to process the request. If a shared SQL area is not found, a new shared SQL area is allocated, and the statement is parsed and executed.

5. The server process finds the data in the SGA (if it is present there) or reads the data from the datafile into the SGA.

6. The server process modifies the data in the SGA. Remember that the server processes can read only from the datafiles. At some later time, the DBWR process writes the modified blocks to permanent storage.

7. The user executes either the COMMIT or ROLLBACK statement. A COMMIT will finalize the transaction; a ROLLBACK will undo the changes. If the transaction is being committed, the LGWR process immediately records the transaction in the redo log file.

8. If the transaction is successful, a completion code is returned across the network to the client process. If a failure has occurred, an error message is returned.

NOTE: A transaction is not considered committed until the write to the redo log file is complete. This arrangement ensures that in the event of a system failure, a committed transaction can be recovered. If a transaction has been committed, it is set in stone.

While transactions occur, the Oracle background processes do their jobs, keeping the system running smoothly. While this process occurs, hundreds of other users might be performing similar tasks. Oracle's job is to keep the system in a consistent state, to manage contention and locking, and to perform at the necessary rate.

This overview is intended to give you an understanding of the complexity and amount of interaction involved in the Oracle RDBMS. As you look in detail at the tuning of the server processes and applications later in this book, you can use this overview as a reference to the basics of how the Oracle RDBMS operates. Because of the differences in operating systems, minor variances in different environments will be discussed individually.