tailieunhanh - Monitoring and Detecting Lock Contention

After completing this lesson, you should be able to do the following: Define levels of locking Identify causes of contention Prevent locking problems Use Oracle utilities to detect lock contention Resolve contention in an emergency Resolve deadlock conditions | Monitoring and Detecting Lock Contention Objectives After completing this lesson, you should be able to do the following: Define levels of locking Identify causes of contention Prevent locking problems Use Oracle utilities to detect lock contention Resolve contention in an emergency Resolve deadlock conditions Locking Mechanism Automatic management High level of data concurrency Row-level locks for DML transactions No locks required for queries Multi-version consistency Exclusive and Share lock modes Locks held until commit or rollback operations are performed Lock Management The Oracle server automatically manages locking. The default locking mechanisms lock data at the lowest level of restriction to guarantee data consistency while allowing the highest degree of data concurrency. Note: The default mechanism can be modified by the ROW_LOCKING parameter. The default value is Always, which leads the Oracle server to always lock at the lowest and least restrictive level (the row level, not the table level) during DML statements. The other possibility is to set the value to Intent, which leads the Oracle server to lock at a more constraining level (the table level), except for a SELECT FOR UPDATE statement, for which a row-level lock is used. Data Concurrency Transaction 1 SQL> SELECT salary 2 FROM employees 3 WHERE id = 10; SALARY --------- 1000 SQL> UPDATE employees 2 SET salary=salary* 3 WHERE id= 24878; 1 row updated. SQL> UPDATE employees 2 SET salary=salary+1200; 13120 rows updated. SQL> UPDATE employees 2 SET salary=salary* 3 WHERE id= 24877; 1 row updated. Transaction 2 Data Concurrency Locks are designed to allow a high level of data concurrency; that is, many users can safely access the same data at the same time. Data Manipulation Language (DML) locking is at row level. A query holds no locks, unless the user specifies that it should. Data Consistency The Oracle server also provides multi-version consistency; that is, the user sees a static picture of | Monitoring and Detecting Lock Contention Objectives After completing this lesson, you should be able to do the following: Define levels of locking Identify causes of contention Prevent locking problems Use Oracle utilities to detect lock contention Resolve contention in an emergency Resolve deadlock conditions Locking Mechanism Automatic management High level of data concurrency Row-level locks for DML transactions No locks required for queries Multi-version consistency Exclusive and Share lock modes Locks held until commit or rollback operations are performed Lock Management The Oracle server automatically manages locking. The default locking mechanisms lock data at the lowest level of restriction to guarantee data consistency while allowing the highest degree of data concurrency. Note: The default mechanism can be modified by the ROW_LOCKING parameter. The default value is Always, which leads the Oracle server to always lock at the lowest and least restrictive level (the row level,