CHAPTER 5
Tuning Oracle Locking

While most commercial database products provide mechanisms for locking and concurrency control, new issues emerge when using a relational database such as Oracle. This problem is especially prevalent when a single Oracle task updates multiple remote servers, which is becoming increasingly common with distributed Oracle systems. The real nightmare begins when the remote servers are of differing architectures—for example, a distributed update to a relational and a network database. In these cases, the concurrency control is generally turned off for each database and is provided for inside the application program, since neither of the databases can be expected to manage the internals of the other database.

In addition to the technical issues, the popularity of client/server interfaces has also changed the ways in which concurrency is handled. Many client/server systems disable their Oracle database locking and rely upon procedural tricks to maintain data integrity at the server level. Oracle is one of these products, and we will discuss how to avoid contention by disabling Oracle locks.

This chapter explains the basic features of Oracle access control and describes the issues involved when concurrency and database integrity must be maintained in a distributed Oracle server environment. Topics include:

•  The problem of cohesive updating

•  Database locking and granularity

•  Oracle locking

•  Database deadlocks

•  Escalation of locks with non-Oracle servers

•  Alternative locking mechanisms for Oracle

•  Locking and distributed databases

•  Understanding the two-phase commit

The Problem Of Cohesive Updating

To ensure that an Oracle transaction properly retrieves and updates information, it is important to understand the differences between two processing modes: conversational and pseudoconversational. In a conversational scenario, the unit of work is extended to include the entire wall-clock time that the user spends in the transaction. In pseudocon-versational mode, the unit of work is partitioned. The duration of the task begins when the user requests a database service, and ends when the database delivers the response to the user. The system then stands idle, releasing any locks that may have been held by the previous transaction. (See Figure 5.1.)

Figure 5.1  Locking and pseudoconversational processing.

As a point of illustration, consider a user who displays a customer’s information, waits five minutes, and then issues an update of the customer information. If we process in fully conversational mode, a problem of database corruption never arises because “exclusive” record locks are held for the entire duration of the terminal session. However, the resources required to hold the locks may cause a burden on the database, and the lock will impede other transactions that desire to retrieve (and possibly update) the information that is being held by the session.

The solution to the locking problem is to create your application structure in the pseudoconversational mode, releasing the row locks after the screen has mapped out to the user. In fact, for Oracle client/server, we highly recommend this approach. However, this processing mode can lead to a variety of problems. Releasing locks in the client/server environment can be very desirable from a performance perspective, but there are many side effects that must be addressed by the application.

Dirty Reads

A dirty read is a situation where a row has been retrieved while the row is held by another transaction with intent to update the row. Assume that a transaction begins, grabbing customer ABC with intent to update. The information for ABC is displayed on the screen, and the user changes the value of the CUSTOMER_STATUS field. At this time, the change has been written to the database, but the information is not made permanent until either a COMMIT or ABORT statement is issued. During the original transaction, the change may be nullified with an ABORT statement. Unfortunately, a transaction may have read the value of CUSTOMER_STATUS for ABC before the transaction was aborted, thereby reading incorrect information.

Nonreproducible Results

This situation most commonly occurs when a report is being run against an Oracle database that is actively being updated. The report may be run in “local mode,” a processing mode using read-only tablespaces that bypasses the services of the database manager and ignores all database locks, reading the database files directly from the disk. The local mode report sweeps the database pages to obtain the requested information, but it also reports on information that is in the process of being changed. This can result in “phantom rows,” since rows are read that are in the process of being deleted or added.

Database Corruption (Bad Foreign Keys)

This situation can occur when local mode reports are run against a system that is being updated. The report attempts to retrieve a row (usually via an index) while the index nodes are readjusting after an insert or delete. This results in the report terminating with a message that indicates a bad pointer. Many DBAs have gone into a panic over this scenario until they realize that the “bad” pointer was not really corrupt and their database is intact. Because Oracle maintains read consistency, this is not an issue. Oracle will read any changed data from its rollback segments, guaranteeing that the report receives a picture of the database as it existed at the time that the report started execution.

Conversational processing is usually associated with a pessimistic locking scheme—that is, the transaction manager assumes that the transaction is going to be “interfered with” during the duration of the session, and the locks are held for the entire time that the record is being viewed. Pseudo-conversational processing mode is associated with an optimistic locking scheme, whereby the system hopes that the transaction will remain isolated.

Database Locking And Granularity

Database locking can take place at many levels. For some relational databases, the locks can be set for the entire database: a tablespace within the database, a table within the tablespace, or a row within the table, as shown in Figure 5.2. Some relational databases also offer “page”-level locking. A database page is a unit of storage, usually associated with a physical block, upon which rows are stored. For example, a page in the Oracle database defaults to 4 K, and the CA-IDMS database allows page sizes to range from 2 K up to 32 K, depending on the size of the records.

Figure 5.2  The different levels of database locking.