Previous | Table of Contents | Next
Page 621
Integrity Management
In this chapter
- Introduction622
- Implementing Locks622
- Analyzing v$lock627
- Monitoring Locks on the System631
- Avoiding Locks: Possible Solutions635
- Implementing Locks with Latches638
Page 622
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.
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.
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:
- A user is modifying data in a table, and at the same time another user is trying to
drop the same table. Oracle prevents this from happening by acquiring a table-level lock
for the first user. The second user, who wants to drop the table, waits for the table lock to
be released by the first user, after which he or she can drop the table.
- User A is trying to read some data within a transaction, which was modified and
committed by User B, after User A's transaction started. User A reads the committed data
of User B. This means that the data read within the same transaction is not consistent to
a point of time. Oracle ensures data read within a transaction is consistent with the point
of time when the transaction started. Oracle implements transaction-level read
consistency using the system change number (SCN) which is explained in a later section.
- One user modifies the data, and another user modifies the same row before the first
user has committed the transaction; therefore, the changes made by the first user are lost.
Page 623
- Oracle prevents this from happening by acquiring a row-level exclusive lock on the table's row and causing the second user to wait.
- One user reads data off another user's data, which is not yet committed; that is, User
A reads a row that User B is changing before User B's changes are committed.
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.
Oracle will automatically decidedepending on the contextwhat 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:
- If a user is trying to read data from a row, another user should be allowed to write to
the same row.
- If a user is updating the data of a row, other users should be able to read data of the
same row at the same time.
- If two users are updating the same table, they should be prevented only when they
are trying to access the same row.
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
|