Sybex OCA Oracle 10g Administration I Study Guide phần 8 pdf

418 Chapter 8 Managing Consistency and Concurrency

In the following example, you lock the EMPLOYEES and DEPARTMENTS tables at the highest

possible level, EXCLUSIVE:

SQL> lock table hr.employees, hr.departments

2 in exclusive mode;

Table(s) Locked.

Until the transaction with the LOCK statement either commits or rolls back, only queries are

allowed on the EMPLOYEES or DEPARTMENTS tables.

In the sections that follow, we will review the lock modes, as well as show you how to

avoid the lock enqueue process and terminate the command if the requested resource is

already locked.

Lock Modes

Lock modes provide a way for you to specify how much and what kinds of access other users

have on tables that you are using in DML commands. In Table 8.2, you can see the types of

locks that can be obtained at the table level.

Manual lock requests wait in the same queue as implicit locks and are satisfied in a first in,

first out (FIFO) manner as each request releases the lock with either an implicit or explicit

COMMIT or ROLLBACK.

TABLE 8.2 Table Lock Modes

Table Lock Mode Description

ROW SHARE Permits concurrent access to the locked table, but prohibits other

users from locking the entire table for exclusive access.

ROW EXCLUSIVE Same as ROW SHARE, but also prohibits locking in SHARE mode. This type

of lock is obtained automatically with standard DML commands such

as UPDATE, INSERT, or DELETE.

SHARE Permits concurrent queries but prohibits updates to the table; this

mode is required to create an index on a table and is automatically

obtained when using the CREATE INDEX statement.

SHARE ROW EXCLUSIVE Used to query a whole table and to allow other users to query the

table, but to prevent other users from locking the table in SHARE mode

or updating rows.

EXCLUSIVE The most restrictive locking mode; permits queries on the locked table

but prohibits any DML by any other users. This mode is required to

drop the table and is automatically obtained when using the DROP

TABLE statement.

4367.book Page 418 Monday, October 4, 2004 2:19 PM

Monitoring Locking and Resolving Lock Conflicts 419

You can explicitly obtain locks on individual rows by using the SELECT … FOR UPDATE state￾ment, as you can see in the following example:

SQL> select * from hr.employees

2 where manager_id = 100

3 for update;

This query not only shows the rows that satisfy the query conditions, it also locks the selected

rows and prevents other transactions from locking or updating these rows until a COMMIT or a

ROLLBACK occurs.

NOWAIT Mode

Using NOWAIT in a LOCK TABLE statement returns control to the user immediately if any locks

already exist on the requested resource, as you can see in the following example:

SQL> lock table hr.employees

2 in share row exclusive mode

3 nowait;

lock table hr.employees

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

SQL>

This is especially useful in a PL/SQL application if an alternate execution path can be fol￾lowed if the requested resource is not yet available. NOWAIT can also be used in the SELECT … FOR

UPDATE statement.

Detecting and Resolving Lock Conflicts

Although locks are a common and sometimes unavoidable occurrence in many databases, they

are usually resolved by waiting in the queue. In some cases, you may need to resolve the lock

problem manually (for example, if a user makes an update at 4:59 P.M. and does not perform

a COMMIT before leaving for the day).

In the next few sections, we will describe in more detail some of the reasons that lock con￾flicts occur and how to detect lock conflicts and discuss a more specific and serious type of lock

conflict: a deadlock.

Understanding Lock Conflicts

In addition to the proverbial user who makes a change at 4:59 P.M. and forgets to perform a COMMIT

before leaving for the day, other more typical lock conflicts are caused by long-running transactions

that perform hundreds, thousands, or even hundreds of thousands of DML commands in the

4367.book Page 419 Monday, October 4, 2004 2:19 PM

420 Chapter 8 Managing Consistency and Concurrency

overnight batch run but are not finished updating the tables when the normal business day starts.

The uncommitted transactions from the overnight batch jobs may lock tables that need to be

updated by clerical staff during the business day, causing a lock conflict.

Another typical cause of lock conflicts is using unnecessarily high locking levels. In the side￾bar “Packaged Applications and Locking” earlier in this chapter, we described a third-party

application that routinely locked resources at the table level instead of at the row level to be

compatible with every SQL-based database on the market. Developers may unnecessarily code

updates to tables with higher locking levels than required by Oracle 10g.

Detecting Lock Conflicts

Detecting locks in Oracle 10g using the EM Database Control makes your job easy; no need to

query against V$SESSION, V$TRANSACTION, V$LOCK, and V$LOCKED_OBJECT to see who is lock￾ing what resource. In Figure 8.5, you can see the tables locked by the user SCOTT after executing

the following statement:

SQL> lock table hr.employees, hr.departments

2 in exclusive mode;

Table(s) Locked.

FIGURE 8.5 The Database Locks screen in EM Database Control

4367.book Page 420 Monday, October 4, 2004 2:19 PM

Monitoring Locking and Resolving Lock Conflicts 421

SCOTT has an EXCLUSIVE lock on both the EMPLOYEES and DEPARTMENTS table. You can drill

down on the locked object by clicking one of the links in the Object Name column; similarly,

you can review other information about SCOTT’s session by clicking one of the links in the Ses￾sion ID column.

Understanding and Resolving Deadlocks

Resolving a lock conflict, the user can either COMMIT or ROLLBACK the current transaction. If you

cannot contact the user and it is an emergency, you can select the session holding the lock, and

click the Kill Session button in the Database Locks screen of the EM Database Control (refer to

Figure 8.5, earlier in this chapter). The next time the user whose session has been killed tries

to execute a command, the error message ORA-00028: Your session has been killed is

returned. Again, this is an option of last resort: all the statements executed in the session since the

last COMMIT are lost.

User Education, Locking, and Error Messages

Some of our users who updated their tables using the SQL> command prompt instead of the appli￾cation would come back from lunch, try to continue their work, and find that they had received an

ORA-00028: Your session has been killed error message, which usually initiated a heated dis￾cussion with the DBA about lost work due to their session being canceled without notice.

At first, the users thought that the DBA group was either cleaning up unused connections man￾ually or that a new automatic resource management policy was in place, because the details for

this error message did not explain why the session was cancelled:

Cause A privileged user has killed your session and you are no longer logged on to the database.

Action Log in again if you want to continue working.

As it turns out, the users were not always performing a COMMIT before they left for lunch; the other

users who were trying to finish their work could not complete their updates because the rows of

the tables were still locked in a transaction that had not yet been committed. They called the DBA,

who identified the locking sessions and canceled them, generating the ORA-0002 message for

the canceled session.

Oracle error messages are not always clear, and the detailed description of the error message

doesn’t always help, but at least it provides a starting point for investigating a problem. Make sure

that the users can access the Oracle error messages, either via the Internet at www.oracle.comor via

an internal shared directory containing all the Oracle documentation for the installation options at

your site.

4367.book Page 421 Monday, October 4, 2004 2:19 PM

422 Chapter 8 Managing Consistency and Concurrency

A more serious type of lock conflict is a deadlock. A deadlock is a special type of lock con￾flict in which two or more users are waiting for a resource locked by the other users. As a

result, neither transaction can complete without some kind of intervention: the session that

first detects a deadlock rolls back the statement waiting on the resource with the error mes￾sage ORA-00060: Deadlock detected while waiting for resource.

In Table 8.3, two sessions are attempting to update a row locked by the other session.

After the error message is issued at 11:45, the second UPDATE for Session 1 does not succeed;

however, the second UPDATE for Session 2 completes, and the user in Session 2 can now submit

another DML statement or issue a COMMIT or ROLLBACK. The user in Session 1 will have to re￾issue the second UPDATE.

Summary

In this chapter, we presented the undo tablespace and its importance for the two types of database

users: those who want to query a table and receive consistent results, and those who want to make

changes to a table and have the option to roll back the data to its state when the transaction

started. The undo tablespace provides undo information, or the value of rows in a table before

changes were made, for both classes of users. More specifically, undo data facilitates rollback

operations, read consistency, certain database recovery operations, and several types of flashback

features, some of which were introduced in Oracle9i and greatly expanded in Oracle 10g.

An undo tablespace can be configured with a handful of initialization parameters: UNDO_

MANAGEMENT to define the mode in which undo is managed, with values of either MANUAL or AUTO.

The UNDO_TABLESPACE parameter identifies the current undo tablespace, which can be switched

while the database is open to users; however, only one undo tablespace can be active at a time.

You can use the EM Database Control to both proactively monitor and resize the undo

tablespace, before you get the phone call from the user whose transactions are failing or SELECT

statements are not completing. For databases whose long-running queries have priority over

successful DML transactions, you can specify that an undo tablespace retain expired undo

information at the expense of failed transactions.

TABLE 8.3 Deadlock Scenario

Session 1 Time Session 2

update employees set salary = salary *

1.2 where employee_id = 102;

11:29 update employees set manager = 100

where employee_id = 109;

update employees set salary = salary *

1.2 where employee_id = 109;

11:44 update employees set manager = 100

where employee_id = 102;

ORA-00060: Deadlock detected while

waiting for resource

11:45 Control returns to user

4367.book Page 422 Monday, October 4, 2004 2:19 PM

Exam Essentials 423

In the second part of the chapter, we showed you how to monitor resource locks within a

transaction, both at the row level and the table level. Although Oracle usually manages locks at

the minimum level to ensure that two sessions do not try to simultaneously update the same row

in a table, you can explicitly lock a table at a number of levels. In addition, you can lock a subset

of rows in a table to prevent updates or locks from other transactions with the FOR UPDATE

clause in the SELECT statement.

Finally, we presented some reasons that lock conflicts occur and how to resolve them; a spe￾cial kind of lock conflict, called a deadlock, occurs when two users are waiting on a resource

locked by the other user. Deadlocks, unlike other types of lock conflicts, are resolved quickly

and automatically by Oracle long before any manual lock resolution is attempted.

Exam Essentials

Know the purpose of the Undo Advisor. Optimize the UNDO_RETENTION parameter as well

as the size of the undo tablespace by using Undo Advisor. Use the graph on the Undo Advisor

screen to perform what-if analyses given the undo retention requirements.

Be able to monitor locking and resolve lock conflicts. Identify the reasons for database lock

conflicts, and explain how to resolve them. Show an example of a more serious type of lock con￾flict, a deadlock.

List the features supported by undo data in an undo tablespace. Enumerate the four primary

uses for undo data: rollback, read consistency, database recovery, and flashback operations.

Show how the rollback requirements for users that perform long transactions can interfere with

read consistency required for query users. Be able to identify and use the method to preserve

expired undo at the expense of transactions.

Summarize the steps for monitoring, configuring, and administering the undo tablespace. Set the

initialization parameters required to use an undo tablespace. Be able to review the status of the undo

tablespace using EM Database Control, and use the Undo Advisor to resize the undo tablespace

when conditions warrant it. Alter the initialization parameter UNDO_RETENTION to configure how

long undo information needs to be retained for long-running queries.

List the types of lock modes available when locking a table. Identify the locks available,

from least restrictive to most restrictive. Be able to request a lock with either a LOCK or SELECT

statement and return immediately if the lock is not available.

4367.book Page 423 Monday, October 4, 2004 2:19 PM