26.oracleocp题库dba经典辅助学习

1、Monitoring and Resolving Lock ConflictsObjectivesAfter completing this lesson you should be able to do the following:Detect and resolve lock conflictsManage deadlocksLocksPrevent multiple sessions from changing the same data at the same timeAutomatically obtained at the lowest possible level for a g

2、iven statementTransaction 1SQL 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 2Locking MechanismHigh level of data concurrencyRow-level locks for inserts, updates, and deletesNo locks required fo

3、r queriesAutomatic queue managementLocks held until transaction ends (with commit or rollback operation)Transaction 1SQL 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 2Data ConcurrencyTime:09:00

4、:00Transaction 1UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100;Transaction 2UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101;Transaction 3UPDATE hr.employees SET salary=salary+100 WHERE employee_id=102;.Transaction xUPDATE hr.employees SET salary=salary+100 WHERE empl

5、oyee_id=xxx;Data ConcurrencyFull Notes PageDML LocksTransaction 1SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 106;1 row updated.SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107;1 row updated.Transaction 2Each DML transaction must acquire two locks:Row-exclus

6、ive lock for the row or rows being updatedShared table-level lock for the table containing the rowsEnqueue MechanismThe enqueue mechanism keeps track of:Sessions waiting for locksThe requested lock modeThe order in which sessions requested the lockLock ConflictsUPDATE hr.employees SET salary=salary+

7、100 WHERE employee_id=100;1 row updated.9:00:00UPDATE hr.employees SET salary=salary+100 WHERE employee_id=101;1 row updated.UPDATE hr.employees SETCOMMISION_PCT=2 WHERE employee_id=101;Session waits enqueue due to lock conflict.9:00:05SELECT sum(salary) FROM hr.employees;SUM(SALARY) 692634Session s

8、till waiting!16:30:00Many selects, inserts, updates, and deletes during the last 7.5 hours, but no commits or rollbacks!1 row updated.Session continues.16:30:01commit;Transaction 1Transaction 2TimePossible Causes of Lock Conflicts mitted changesLong-running transactions Unnecessarily high locking le

9、velsDetecting Lock ConflictsSelect Blocking Sessions from the Performance page.Click the Session ID link to view information about the locking session, including the actual SQL statement.Resolving Lock ConflictsTo resolve a lock conflict:Have the session holding the lock commit or roll back.Terminat

10、e the session holding the lock as a last resort.DeadlocksTransaction 2Transaction 1UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 1000;UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 2000;ORA-00060:Deadlock detected while waiting for resourceUPDATE employeesSET manager = 1342WHERE employee_id = 2000;UPDATE employeesSET manager = 1342WHERE employee_id = 1000;9:009:159:16SummaryIn this lesson you should have learned how to:Detect and resolve lock conflictsManage deadlocksPractice 17: Locks in the Oracle DatabaseThis practice covers common administrative t