8.2. Monitoring Locking and Resolving Lock Conflicts

In any database that has more than one user, you will eventually have to deal with locking conflicts when two or more users try to change the same row in the database.

In this section, we present an overview of how locking works in the Oracle database, how users are queued for a particular resource once it is locked, and how Oracle classifies lock types in the database.

At the end of this section, we show you a number of ways to detect and resolve locking issues; we also cover a special type of lock situation: the deadlock.

8.2.1. Understanding Locks and Transactions

Locks prevent multiple users from changing the same data at the same time. Before one or more rows in a table can be changed, the user executing the DML statement must obtain a lock on the row or rows: a lock gives the user exclusive control over the data until the user has committed or rolled back the transaction that is changing the data.

In Oracle 10g, a transaction can lock one row, multiple rows, or an entire table. Although you can manually lock rows, Oracle can automatically lock the rows needed at the lowest possible level to ensure data integrity and minimize conflicts with other transactions that may need to access other rows in the table.

In Table 8.1, both updates to the EMPLOYEES table return to the command prompt immediately after the UPDATE because the locks are on different rows in the EMPLOYEES table and neither session is waiting for the other lock to be released.

update employees set salary = salary
* 1.2 where employee_id = 102;

update employees set manager = 100
where employee_id = 109;

commit;

commit;

Queries never require a lock. Even if another transaction has locked several rows or an entire table, a query always succeeds, using the pre-lock image of the data stored in the undo tablespace.

If multiple users require a lock on a row or rows in a table, the first user to request the lock obtains it, and the remaining users are enqueued using a first-in, first-out (FIFO) method. At a SQL> command prompt, a DML statement (INSERT, UPDATE, DELETE, or MERGE) that is waiting for a lock on a resource appears to hang, unless the NOWAIT keyword is used in a LOCK statement.

At the end of a transaction, when either a COMMIT or a ROLLBACK is issued (either explicitly by the user or implicitly when the session terminates normally or abnormally), all locks are released.

8.2.2. Maximizing Data Concurrency

Rows of a table are locked either explicitly by the user at the beginning of a transaction or implicitly by Oracle, usually at the row level, depending on the operation. If a table must be locked for performance reasons (which is rare), you can use the LOCK TABLE command, specifying the level at which the table should be locked.

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.

8.2.2.1. 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.

You can explicitly obtain locks on individual rows by using the SELECT ... FOR UPDATE statement, 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 followed if the requested resource is not yet available. NOWAIT can also be used in the SELECT ... FOR UPDATE statement.

8.2.3. 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 conflicts occur and how to detect lock conflicts and discuss a more specific and serious type of lock conflict: a deadlock.

8.2.3.1. 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 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 sidebar "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.

8.2.3.2. 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 locking 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

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 Session ID column.

8.2.3.3. 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.

A more serious type of lock conflict is a deadlock. A deadlock is a special type of lock conflict 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 message 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.

update employees set salary = salary *
1.2 where employee_id = 102;

update employees set manager = 100
where employee_id = 109;

update employees set salary = salary *
1.2 where employee_id = 109;

update employees set manager = 100
where employee_id = 102;

ORA-00060: Deadlock detected while
waiting for resource

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 reissue the second UPDATE.