Managing lock conflicts in Oracle 10g

Learning objective

After completing this topic, you should be able to manage lock conflicts.

1. Identifying causes of lock conflicts

Lock conflicts often occur in databases, when sessions attempting to access a row or table are locked out by another session. They are usually resolved by the enqueue mechanism, but sometimes they require DBA intervention.

There are three main causes of lock conflicts.

• long-running transactions
• uncommitted changes
• unnecessarily high locking levels

long-running transactions

When an application uses batch processing during a low-activity period to perform bulk updates or run bulk queries, the processing sometimes takes too long to run and continues into a time when higher activity occurs. This can cause transactions and batch processing to occur simultaneously, often resulting in lock conflicts.

uncommitted changes

Uncommitted changes are the most common cause of lock conflicts. A transaction that obtains a lock on a single row and fails to commit will prevent another transaction from updating the entire table, as this would require a lock on all rows.

unnecessarily high locking levels

Developers writing applications that need to run on many different databases often set artificially high locking levels to bring the Oracle database, which has row-level locking, in line with databases that lock only at page or table level.

In this example, a transaction obtains a lock on a single row of a table but neglects to commit, leaving the lock in place.

UPDATE hr.employees 

SET salary=salary+100 

WHERE employee_id=101;

1 row updated.

A short time later, another transaction attempts a transaction requiring a lock. This transaction is blocked by the previous transaction and must wait enqueue until the first transaction commits and resolves the lock conflict.

UPDATE hr.employees 

SET COMMISSION_PCT=2 

WHERE employee_id=101;

Question

Which of these are possible causes of lock conflicts?

Options:

0. Long-running transactions
0. Too many sessions requesting locks
0. Uncommitted changes
0. Unnecessarily high locking levels

Answer

Possible causes of lock conflicts include long-running transactions, uncommitted changes, and unnecessarily high locking levels.

Option 1 is correct. Lock conflicts often occur when batch processing overruns and coincides with a period of normal transactions.

Option 2 is incorrect. Lock requests are tracked by the enqueue mechanism.

Option 3 is correct. Uncommitted changes are the most common cause of lock conflicts.

Option 4 is correct. Applications developed to run on different databases often implement overly high locking levels in the Oracle database.

2. Detecting lock conflicts

As a DBA, you can use the Performance page in Enterprise Manager to detect lock conflicts.

The ADDM (Automatic Database Diagnostic Monitor) utility also detects lock conflicts automatically, and can detect inefficient locking trends.

Suppose one of the users of your database is trying to update rows in a table but cannot do so because the table is locked. You need to view the session that is blocking other sessions from obtaining the lock.

You click the Blocking Sessions link under Additional Monitoring Links.

The Blocking Sessions page opens, displaying the conflicting sessions. The top session is the one holding the lock, with sessions enqueue for the lock displayed in order in the rows below.

The table columns display each session's username, session ID, and for how many seconds the session has been waiting.

Clicking the hyperlinked session ID enables you to view the SQL statements executed or being requested by the session.

Question

Which of these utilities will help detect lock conflicts?

Options:

0. ADDM
0. Enterprise Manager
0. Data Pump

Answer

The ADDM and Enterprise Manager utilities help detect lock conflicts.

Option 1 is correct. The ADDM (Automatic Database Diagnostic Monitor) utility detects lock conflicts automatically, and can detect inefficient locking trends.

Option 2 is correct. As a DBA, you can use the Performance section in Enterprise Manager to detect lock conflicts.

Option 3 is incorrect. The Data Pump utility enables high-speed transfer of data from one database to another.

Question

You want to detect a locking session that is preventing a user from updating a table.

This task requires you to use the Performance page of the Enterpise Manager to detect the locking session. Which option allows you to do this?

Options:

0. You click the Blocking Sessions link under Additional Monitoring Links on the Performance page.
0. You click the Database Locks link under Additional Monitoring Links on the Performance page.

Answer

You click the Blocking Sessions link under Additional Monitoring Links on the Performance page.

3. Resolving lock conflicts

To resolve a lock conflict, the session holding the lock must release it. To achieve this, you should contact the user and ask them to complete their transaction by committing or rolling back.

If this is not possible you can, as a last resort, terminate the session holding the lock.

Suppose that you cannot contact the owner of session 245, the blocking session that is holding the lock. You need to terminate the session so that other transactions can proceed.

You click 245 in the first cell of the Session ID column.

The information for the blocking session is displayed in the Session Details page. Now you want to terminate the session.

You click the Kill Session button.

A confirmation page asks you to verify that you want to kill the session.

You want to proceed, so you click Yes.

The blocking session has been killed.

The lock is released, so the next enqueued session can now complete its transaction.

When you kill a session, the user whose session is killed receives notification of this fact when they next attempt to issue a SQL statement.

In addition, all work within the user's current transaction is lost. The user must log in again and redo all work since their last commit.

Question

In which of these ways can a DBA resolve a lock conflict?

Options:

0. Ask the owner of the session holding the lock to commit the transaction
0. Ask the owner of the session holding the lock to roll back the transaction
0. Terminate the session holding the lock
0. Use the ADDM

Answer

A DBA can resolve a lock conflict by asking the owner of the current session to commit or roll back the transaction, or by terminating the current session.

Option 1 is correct. Committing the transaction enables the lock to be released and passed on to the next enqueue session.

Option 2 is correct. If the transaction is not to be committed at this time, the session should roll it back and enable the lock to be passed on.

Option 3 is correct. Terminating the session is a last resort for the DBA and should only be performed if the transaction cannot be resolved by any other means.

Option 4 is incorrect. ADDM is a diagnostic monitoring tool that can detect lock conflicts and advise you of inefficient locking trends. However, it is unable to resolve lock conflicts.

Question

On the Blocking Sessions page you notice that a blocking session with an ID of 247 is preventing another session, 243, from completing a transaction. You are unable to get in touch with the user to ask them to complete the transaction.

In this exercise, you need to use Enterprise Manager to terminate the session. Which option allows you to do this?

Options:

0. You click 243 in the Session ID column. In the Session Details page you click the Kill Session button. Finally on the confirmation page you click Yes.
0. You click 247 in the Session ID column. In the Session Details page you click the Kill Session button. Finally on the confirmation page you click Yes.

Answer

You click 247 in the Session ID column. In the Session Details page you click the Kill Session button. Finally on the confirmation page you click Yes.

4. Resolving deadlocks

A deadlock is a specific type of lock conflict where two sessions waiting for data are locked by each other so that neither can complete its transaction.

In this example, two transactions are updating the same table. The first updates are successful.

UPDATE employees


SET salary = salary x 1.1

WHERE employee_id = 1000;



UPDATE employees

SET manager = 1342

WHERE employee_id = 2000;

However, when transaction 1 tries to update rows with the employee ID 2000, it encounters a deadlock because transaction 2 has locked the data. Likewise, transaction 2 cannot update rows with the ID 1000 because transaction 1 has locked them.

UPDATE employees


SET salary = salary x 1.1

WHERE employee_id = 2000;



UPDATE employees

SET manager = 1342

WHERE employee_id = 2000;

To resolve deadlocks, Oracle rolls back the transaction that discovered the deadlock.

In this case, the second update by transaction 1 is rolled back. Transaction 1 needs to resubmit this update. However, any updates previous to this remain unaffected by the rollback.

UPDATE employees

SET salary = salary x 1.1

WHERE employee_id = 1000;



UPDATE employees

SET salary = salary x 1.1

WHERE employee_id = 2000;

Question

How does Oracle resolve a deadlock between two sessions?

Options:

0. By rolling back all statements
0. By rolling back the statement that detected the deadlock
0. By terminating both sessions
0. By terminating the session that first detected the deadlock

Answer

Oracle resolves deadlocks by rolling back the statement that detected the deadlock.

Option 1 is incorrect. Only the statement that detected the deadlock is rolled back. All previous statements are unaffected.

Option 2 is correct. Once the statement that is rolled back is resubmitted, both transactions can continue.

Option 3 is incorrect. You do not need to terminate either session to resolve a deadlock.

Option 4 is incorrect. You do not need to terminate the session. Simply rolling back the statement that first detected the deadlock enables the transaction to continue.

Summary

Lock conflicts can occur when sessions attempting to access a row or table are locked out by another session. They sometimes require DBA intervention. Possible causes of lock conflicts include long-running transactions, uncommitted changes, and unusually high locking levels.

You use the Performance page in Enterprise Manager to detect lock conflicts. In the Blocking Sessions page, you can view details for the session holding the lock, and the enqueue sessions it is blocking.

To resolve a lock conflict, you should ask the user to complete their transaction by committing or rolling back. If this is not possible, you can terminate the session in Enterprise Manager so that the lock is released. If you do this, all work since the last commit is lost.

A deadlock is a specific type of lock conflict where two sessions waiting for data are locked by each other so that neither can complete its transaction. Oracle resolves deadlocks by rolling back the transaction that detected the deadlock.