15.13 How do I…Detect and resolve lock contention?

Problem

The users of my database are complaining about poor response times, but my database monitoring does not reveal any problems. I suspect that competing object locks may be the cause of the perceived performance problems. How can I determine if my database is suffering from locking problems and, if it is, how can I eliminate these difficulties?

Technique

Note-The UTLESTAT report does not address this topic.

Except for deadlocks, which this How-To briefly addresses in the comments section, lock wait requests in an Oracle database will never time out. Consequently, lock waits can make application performance look appalling to an end-user. The Oracle data dictionary captures more lock data than most people would ever want to know and in fact, so much locking information is in dynamic performance views, like V$SESSION and V$LOCK and in DBA_LOCKS, that it’s difficult to know where to start. Stepping back for a moment though, it seems likely that DBAs and developers really want to answer one simple locking question when users are screaming that “the database is slow:” is anyone holding a lock that is delaying someone else?

Fortunately, Oracle provides a script, UTLLOCKT.SQL, that greatly simplifies the process of answering this question. In order to see it in action, suppose that the following events have occurred:

In response to SCOTT’s query, Oracle8 will issue a Row Share lock that involves an exclusive lock on all the rows (in this case, all the rows in the table) returned by SCOTT’s query. WAITE’s query needs a Row Exclusive DML lock on EMP also, but SCOTT is in the way. The important fact here is that SCOTT is blocking WAITE.

Steps

1. Run SQL*Plus, connect as the SYS user, and run the script CHP15_23.SQL. This script creates the database objects that the script in the next step requires. You need only run the CHP15_23.SQL script once.

2. Run the script CHP15_24.SQL. The script’s output appears in Figure 15.19.

How It Works

The script in Step 1 creates a number of database objects needed by the UTLLOCKT script. Step 2 runs a modified version of the UTLLOCKT script to show locking contention. The first part of the output from the CHP15_24.SQL script in Step 2 shows that session 13 is waiting for session 9. The second section indicates that session 9 belongs to the user SCOTT and that session 13 belongs to the user WAITE.

Comments

What happened to the UTLLOCKT script, where did the script in Step 1 come from, and what is its function?

The script in Step 1, CHP15_23.SQL, is a modification of the CATBLOCK.SQL script that Oracle supplies with the Oracle8 release. It is intended to configure the data dictionary for the use of the UTLLOCKT script. Under Windows NT, CATBLOCK.SQL resides in the \ORANT\RDBMS80\ADMIN subdirectory.

Unfortunately, in the Oracle8 beta release, an in-line comment on the last line of the statement that creates the DBA_LOCK view prevents the script from running correctly. Without this view, the UTLLOCKT script will not run either. CHP15_23 is a corrected version of the original CATBLOCK.SQL script.

The script in Step 2, CHP15_24.SQL, is a modified version of the UTLLOCKT script that generates more legible output. Under Windows NT, the original version of the script resides in the\ORANT\RDBMS80\ADMIN subdirectory.

Once you have detected a locking problem, what should you do about it? There are essentially two options. The first is to find the user who holds the blocking lock, SCOTT in the example above, and ask him to rollback or commit his transaction. If SCOTT is unavailable, he may be very unpopular. You can restore some of his damaged reputation by issuing the alter system kill session command from server manager to rollback all of his transactions, release any locks that he holds, and terminate his connection to the database.

Deadlocks occur when two sessions are hung up waiting for each other. This is the only sort of lock that Oracle will resolve on its own. To do so, it will pick one session and rollback all of its transactions, releasing the locks that the session holds.

Oracle’s locking mechanisms are usually fast and effective. Be careful if you choose to exceed Oracle’s default locking levels in your applications.