Less_17Monitoring and Resolving Lock Conflicts.ppt

Monitoring and Resolving Lock Conflicts Objectives After completing this lesson you should be able to do the following: Detect and resolve lock conflicts Manage deadlocks Locks Prevent multiple sessions from changing the same data at the same time Automatically obtained at the lowest possible level for a given statement Locking Mechanism High level of data concurrency Row-level locks for inserts, updates, and deletes No locks required for queries Automatic queue management Locks held until transaction ends (with commit or rollback operation) Data Concurrency Data ConcurrencyFull Notes Page DML Locks Transaction 1 Enqueue Mechanism The enqueue mechanism keeps track of: Sessions waiting for locks The requested lock mode The order in which sessions requested the lock Lock Conflicts Possible Causes of Lock Conflicts Uncommitted changes Long-running transactions Unnecessarily high locking levels Detecting Lock Conflicts Select Blocking Sessions from the Performance page. Resolving Lock Conflicts To resolve a lock conflict: Have the session holding the lock commit or roll back. Terminate the session holding the lock as a last resort. Deadlocks Summary In this lesson you should have learned how to: Detect and resolve lock conflicts Manage deadlocks Practice 17: Locks in the Oracle Database This practice covers common administrative tasks relating to locks in Oracle Database 10g, including: Detecting which session is causing the locking conflict Resolving locking conflicts Practice Lesson 17Full Notes * Copyright ? 2004, Oracle. All rights reserved. 17 Copyright ? 2004, Oracle. All rights reserved. Transaction 1 SQL UPDATE hr.employees 2 SET salary=salary*1.1 3 WHERE employee_id=100; SQL UPDATE hr.employees 2 SET salary=salary+100 3 WHERE employee_id=100; Transaction 2 Transaction 1 SQL UPDATE hr.employees 2 SET salary=salary*1.1 3 WHERE employee_id=101; SQL UPDATE hr.employees 2 SET salary=salary+100 3 WHERE employee_id=100; Transaction 2