Previous | Table of Contents | Next

Page 621

CHAPTER 25

Integrity Management

In this chapter

Page 622

Introduction

As the size of the database and the number of users on the system increases, complexity in terms of internal resource contention also increases thus leading to performance problems. The general symptoms of such internal contention are: there is enough free memory, the CPU is not very busy, but still there are performance problems on the system. Such symptoms would make a novice administrator wonder what is happening on the system. The aim of this chapter is to empower the administrator to identify such contention issues. The resources that are addressed in this chapter are locks and latches.

Implementing Locks

Locks may never be required for a single user database, but when you work in a multiuser environment, it is important to have a mechanism in place that will automatically cater to data concurrency, consistency, and integrity issues. Oracle automatically caters to these issues by acquiring different types of locks on behalf of the user to allow or prevent simultaneous access to the same resource by different users and ensuring that data integrity is not violated. Resource here means user objects such as tables and rows. While implementing locks, one of the key issues to keep in mind is that it should not be a bottleneck on the system, preventing concurrent access to data. Oracle will therefore automatically acquire locks at different levels, depending on the database operation being performed to ensure maximum concurrency. For example, when a user is reading data of a row, other users should be allowed to write to the same row. No user should be allowed to drop the table, however. These issues, relating to which transaction should be allowed what level of access to an object or data, are implemented using the different locking levels, which we will be covering in the next section.

Need for Locking

Before we actually get into the intricacies of locking, let's examine the various scenarios under which locking becomes really important. The following are a few of the instances that explain why data consistency and concurrency are so important:

Page 623

Oracle prevents this from happening by allowing User A to read the old data, that is the data before User B modified it. Oracle does this by obtaining the data from the rollback segments.

Locking Concepts

Oracle will automatically decide—depending on the context—what lock needs to be applied for the given situation to provide maximum level of data concurrency using the lowest level of restrictiveness. Oracle has two levels of locking: share and exclusive.

Share locks are implemented with higher concurrency of data access in mind. If a shared lock
is acquired, other users can share the same resource. A number of transactions can acquire a shared lock on the same resource, but this is not true in the case of exclusive locks. For example, multiple users can read the same data at the same time.

Exclusive locks prevent simultaneous sharing of the same resource. For example, if one transaction acquires an exclusive lock on a resource, no other transaction can alter or modify that resource until the time the lock is released. Here again, sharing the resource is allowed. For example, if a table is locked in exclusive mode, it will not prevent other users from selecting from the same table.

You can use the levels of locking to enable concurrent access and, at the same time, ensure data integrity. Oracle achieves this goal by applying the lowest level of restrictiveness. Oracle will automatically lock either the individual row or the entire table, depending on what is needed. Oracle uses the following general guidelines to implement the level of locking it needs to apply:

One of the most important functionality issues that is implemented using locking mechanisms is data consistency. Oracle ensures data consistency at two levels: statement level and transaction level.

Statement-level read consistency means any statement that starts executing will see committed data that was available just before the statement started executing. All changes, committed or uncommitted, made to data while the statement was in execution are invisible. Oracle provides statement-level read consistency by ensuring that only committed data before the SCN observed at the time when the statement started executing is available.

Previous | Table of Contents | Next