Oracle Essentials Oracle Database 11g (39 page)

6. The HR clerk commits the transaction.

7. Log Writer (LGWR) writes the redo information for the entire transaction, including the SCN that marks the time the transaction was committed, from the redo log buffer to the current redo log file on disk. When the operating system confirms that the write to the redo log file has successfully completed, the transaction is considered committed.

8. The server process sends a message to the client confirming the commit.

Oracle Database 10
g
Release 2 introduced the ability to have the server process return control to the client without waiting for all the redo information to be written. The plus side of this enhancement is that high-volume OLTP applications may benefit from improved performance. The downside of this feature is that it opens a
194

|

Chapter 8: Oracle Multiuser Concurrency

window of vulnerability—the database could crash after a transaction had been committed, but before the redo was written, which would make it impossible to recover the committed transaction, so this feature should be used with caution.

A Conflicting Write Operation

The write operation previously described is a little different if there are two users, Client A and Client B, who are trying to modify the same row of data at the same time. The steps are as follows:

1. Client A modifies the employee name on the screen. Client A sends a SQL

UPDATE statement over the network to the server process.

2. The server process obtains an SCN for the statement and reads the data block containing the target row.

3. The server records row lock information in the data block.

4. The server process writes the changes to the redo log buffer.

5. The server process copies the old image of the employee data about to be changed to a rollback segment. Once the server process has completed this work, the process modifies the employee data, which includes writing the SCN

to the ORA_ROWSCN pseudocolumn in Oracle Database 10
g
or newer database releases.

6. Client B modifies the employee name on the screen and sends a SQL UPDATE

statement to the server.

7. The server process obtains an SCN and reads the data block containing the target row.

8. The server process sees that there is a lock on the target row from the information in the header of the data block, so it takes one of two actions. If the isolation level on Client B’s transaction is READ COMMITTED, the server process waits for the blocking transaction to complete. If the isolation level for Client B’s transaction is SERIALIZABLE, an error is returned to the client.

9. Client A commits the transaction, the server process takes the appropriate action, and the server sends a message to Client A confirming the commit.

10. If Client B executed the SQL statement with the READ COMMITTED isolation level, the SQL statement then proceeds through its normal operation.

The previous example illustrates the default behavior of Oracle when it detects a problem caused by a potential lost update. Because the SERIALIZABLE isolation level has a more drastic effect when it detects a write conflict than the READ COMMITTED isolation level, many developers prefer the latter level. They can avoid some of the potential conflicts by either checking for changes prior to issuing an update (by comparing values in a row or using the Oracle Database 10
g
or later row SCN) or using the SELECT FOR UPDATE syntax in their SQL to avoid the problem altogether.

How Oracle Handles Locking

|

195

A Read Operation

Youcan really appreciate the beauty of Oracle’s read consistency model by looking at the more common scenario of one user reading data and one user writing to the same row of data. In this scenario, Client A is reading a series of rows from the EMP

table, while Client B modifies a row before it is read by Client A, but after Client A begins her transaction:

1. Client A sends a SQL SELECT statement over the network to the server process.

2. The server process obtains an SCN for the statement and begins to read the requested data for the query. For each data block that it reads, it compares the SCN of the SELECT statement with the SCNs for any transactions for the relevant rows of the data block. If the server finds a transaction with a later SCN

than the current SELECT statement, the server process uses data in the rollback segments to create a “consistent read” version of the data block, current as of the time the SELECT was issued. This is what provides the multiversion read consistency (MVRC) and avoids the need for Oracle to use read locks on data. If a row has been updated since the transaction started, Oracle simply gets the earlier version of the data for a consistent view.

3. Client B sends a SQL UPDATE statement for a row in the EMP table that has not yet been read by Client A’s SELECT statement. The server process gets an SCN for the statement and begins the operation.

4. Client B commits his changes. The server process completes the operation, which includes recording information in the data block that contained the modified row that allows Oracle to determine the SCN for the update transaction.

5. The server process for Client A’s read operation comes to the newly modified block. It sees that the data block contains changes made by a transaction that has an SCN that is later than the SCN of the SELECT statement. The server process looks in the data block header, which has a pointer to the rollback segment that contains the data as it existed when Client A’s transaction started. The rollback segment uses the old version of the data to create a version of the block as it existed when the SELECT statement started. Client A’s SELECT statement reads the desired rows from this consistent version of the data block.

Figure 8-2 illustrates the process of reading with multiversion read consistency.

We explained how MVRC works with two users for the sake of clarity. But imagine a database supporting one or more enterprise applications, with hundreds of simultaneous users. Oracle’s concurrency handling could avoid an enormous amount of contention and performance degradation in a heavy use scenario—in fact, the greater the workload, the greater the benefits of MVRC.

196

|

Chapter 8: Oracle Multiuser Concurrency

Client A

(SCN 112)

Alpha

111

Beta

111

Client B

(SCN 113)

Carol

111

Darryl

111

113

Edward

111

Frank

111

Greenie

111

When Client B reads the rows, the changes made

to the "Edward" row with a later SCN are skipped.

Value

SCN

Figure 8-2. Multiversion read consistency

Concurrent Access and Performance

When youread through all the steps involved in the above processes, youmight think that Oracle would be a very slow database. This is not at all true. Oracle has consistently turned in benchmarks that make it one of the fastest databases, if not the fastest, on the market today.

Oracle provides good performance while implementing multiversion read consistency by minimizing and deferring unnecessary I/O operations. To assure the integrity of the data in a database, the database must be able to recover in the event of a system failure. This means that there must be a way to ensure that the data in the database accurately reflects the state of the committed data at the time of the crash. Oracle can do this by writing changed data to the database whenever a transaction commits. However, the redo log contains much less information than the entire data block for the changed data, so it’s much “cheaper” to write to disk. Oracle writes the redo information to disk as soon as a transaction commits and defers writing the changed data blocks to the database until several sets of changed blocks can be written together. Oracle can restore the database using the redo logs, and these procedures cut down on time-consuming I/O operations.

Concurrent Access and Performance

|

197

However, when you’re considering the performance of a database, you have to think about more than simple I/O operations. It doesn’t really matter how fast your database runs if your transaction is waiting for another transaction to release a lock. A faster database may complete the blocking transaction faster, but your transaction is still at a dead stop until the blocking transaction completes.

Because most databases perform a mixture of reading and writing, and because Oracle is one of the only databases on the market that doesn’t use read locks, Oracle will essentially always deliver the lowest amount of database contention. Less contention equals greater throughput for a mixed application load.

There is also more than one type of performance. Performance for database operations is measured in milliseconds; performance for application developers is measured in months. Because Oracle provides much less contention with its read consistency model, developers have to spend less time adding workarounds to their applications to handle the results of contention.

It’s not as though Oracle is the only database to give you a concurrency solution you can use to implement applications that provide adequate data integrity. But the multiversion read consistency model makes it easy for youto get a consistent view of data without excessive contention and without having to write workarounds in your application. If it sounds as if we’re big fans of Oracle’s locking scheme, well—we are.

Workspaces

Oracle9
i
introduced a new feature that relates to concurrency, Workspace Manager.

A
workspace
is a way to isolate data from changes in the general database environment. Workspace Manager accomplishes this by creating workspace-specific versions of data. When youcreate a workspace, youessentially create a snapshot of the data in the workspace at a specific point in time. Further changes to that data from outside the workspace do not affect the view of the data in the workspace, and changes made to data within the workspace are not seen by users outside the workspace. And changes to data within a workspace are visible only to other workspace users.

Workspaces allow youto essentially create separate data environments for specialized usage. You can capture data at a certain point in time for historical analysis and can also perform various types of “what-if” analysis, testing to see how changes would affect the overall composition of the data without disturbing the main production database. Both of these options would normally require you to create a duplicate database, so workspaces can save you time and resources.

198

|

Chapter 8: Oracle Multiuser Concurrency

Workspace Implementation

The key to workspaces is the support of multiple versions of the same data. To use workspaces to version data in a table, youmust first enable the table for versioning.

Workspace Manager can version-enable one or more user tables in the database. The unit of versioning is a row. When a table is version-enabled, all rows in the table can support multiple versions of the data. Versioned rows are stored in the same table as the original rows. The versioning infrastructure is not visible to the users of the database, and application SQL statements to select, insert, modify, and delete data continue to work in the usual way with version-enabled tables. Workspace Manager version-enables a table by renaming the table, adding a few columns to the table to store versioning metadata, creating a view on the version-enabled table using the original table name, and defining INSTEAD OF triggers on the view for SQL DML

operations.

The workspace keeps changes to the data only to minimize the size of the workspace data and avoid data duplication.

Youcan have a hierarchy of workspaces, and a workspace can have more than one parent. All workspace operations, described in the next sections, affect a workspace and its parent workspaces. Multiple levels of workspaces can give you finer granularity on the isolation of changes for workspace-enabled tables.

Oracle implements workspaces by adding metadata to the rows of a table. This metadata can include a timestamp as to when a change was made, which can help in analysis of workspace activity. This option works with savepoints to provide a history of changes made to each row version created by a savepoint. The timestamp allows users in a workspace to go back to any point in time and view the database from the perspective of changes made in that workspace up to another point in time. You can think of this as a type of Flashback (described in
Chapter 3)
for a limited set of tables.

In addition, youcan specify that a particular version of data in a workspace is valid only for a specific time period. For instance, you could make a change to data that would be visible to workspace users for the next 24 hours and that would then disappear.

Workspaces have their own locking mechanisms that apply only to other workspace users. You can exclusively lock a row of data in a workspace, but this lock prevents access only to that row for other workspace users. The underlying data could still be accessed or changed by users who are not part of the workspace. This additional locking makes sense, since both locks and workspaces are meant to isolate data from changes. A workspace exists outside the boundaries of the standard database, so workspace locks and standard database locks do not directly interact.

Workspaces

|

199

Workspace Operations

There are three basic operations that apply to workspaces:

Rollback

Youcan roll back changes to a workspace to return the workspace to the point in time when the workspace was created. Youcan also designate savepoints, which allow you to roll back the changes in a workspace to a subsequent point in time.