-- * Shared and Exclusive Locks -- * Exclusive lock: -- The first session to request the lock on the row gets it, -- Any other sessions requesting write access must wait. -- Read access is permitted, though if the row has never been updated by the locking session, -- Only one session can take an exclusive lock on a row, or a whole table, at a time. -- First session UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 100 -- Second session UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 100 -- * Shared locks can be taken on the same object by many sessions. -- The purpose of taking a shared lock on a table is to prevent another session from acquiring -- an exclusive lock on the table. -- Exclusive locks on tables are required to execute DDL statements. -- You cannot issue a statement that will modify an object (for instance, dropping a -- column of a table) if any other session already has a shared lock on the table. -- To execute DML on rows. -- First session UPDATE hr.employees SET salary = salary * 1.1 WHERE employee_id = 100 -- Second session ALTER TABLE employees ADD (test number) ; ALTER TABLE employees DROP COLUMN test ; -- Manual locking (first session) LOCK TABLE employees IN EXCLUSIVE MODE; -- For more locking modes : -- http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9015.htm -- * DML and DDL Locks ---------------------- -- DML ------ -- * All DML statements require at least two locks: -- 1. exclusive lock on each row affected, -- 2. shared lock on the table containing the row. -- The exclusive lock prevents another session from interfering with the row, -- the shared lock prevents another session from changing the table definition -- with a DDL statement. -- * These locks are requested automatically. -- * If a DML statement cannot acquire the exclusive row locks it needs, -- then it will hang until it gets them. -- DDL ------ -- * To execute DDL commands requires an exclusive lock on the object concerned. -- * This cannot be obtained until all DML transactions against the table have finished, -- thereby releasing both their exclusive row locks and their shared table locks. -- * The exclusive lock required by any DDL statement is requested automatically, but if it -- cannot be obtained (typically, because another session already has the shared lock -- granted for DML) then the statement will terminate with an error immediately. -- * Possible Causes of Lock Conflicts -- Uncommitted changes -- Long-running transactions -- Unnecessarily high locking levels -- * Data Concurrency --------------------- -- means that many users can access data at the same time. -- * Data consistency --------------------- -- means that each user sees a consistent view of the data, -- including visible changes made by the user's own transactions and transactions of other users. -- * Enqueue Mechanism ---------------------- -- * Requests for locks are automatically queued. -- As soon as the transaction holding a lock is completed, the next session in -- line receives the lock. -- first session update employees set salary = salary * 1.2 where employee_id = 100; -- second session ALTER TABLE employees DROP COLUMN test ; -- third session update employees set salary = salary * 1.5 where employee_id = 100; -- * Detecting Lock Conflicts ----------------------------- select SID, SERIAL#, USERNAME, SECONDS_IN_WAIT from V$SESSION where SID in (select BLOCKING_SESSION from V$SESSION) select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2 col username for a15 col program for a15 col machine for a15 SELECT sid, serial#, username, program, machine FROM v$session WHERE username IN ('SYS' , 'HR') SELECT o.object_name, w.seconds_in_wait, w.sid waiter_sid, w.serial# waiter_sr#, w.machine waiter_machine, w.program waiter_program, w.module waiter_module, b.sid blocker_sid, b.serial# blocker_sr#, b.machine blocker_machine, b.program blocker_program, b.module blocker_module, sw.sql_text waiter_sql, sb.sql_text blocker_last_sql FROM v$session w JOIN v$session B ON (w.blocking_session = b.sid) JOIN all_objects o ON (w.ROW_WAIT_OBJ# = o.object_id) LEFT JOIN v$sql sw ON (w.sql_id = sw.sql_id) LEFT JOIN v$sql sb ON (b.prev_sql_id = sb.sql_id) WHERE w.blocking_session IS NOT NULL; -- * Resolving Lock Conflicts ----------------------------- ALTER SYSTEM KILL SESSION 'sid,serial#'; -- or ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; -- SID: Session identifier, used to join to other columns -- SERIAL#: Counter, which is incremented each time a SID is reused by another session -- (when a session ends and another session starts and uses the same SID) -- The KILL SESSION command doesn't actually kill the session. -- It merely asks the session to kill itself. -- In some situations, like waiting for a reply from a remote database or rolling back -- transactions, the session will not kill itself immediately and will wait for the current -- operation to complete. -- In these cases the session will have a status of "marked for kill". It will then be killed as -- soon as possible. -- The KILL SESSION IMMEDIATE does not affect the work performed by the command, -- but it returns control back to the current session immediately, -- rather than waiting for confirmation of the kill. ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; -- or ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; -- * The ALTER SYSTEM DISCONNECT SESSION syntax as an alternative method for killing Oracle -- sessions. -- * Unlike the KILL SESSION command which asks the session to kill itself, -- the DISCONNECT SESSION command kills the dedicated server process -- (or virtual circuit when using Shared Sever), -- which is equivalent to killing the server process from the operating system. -- Via OEM: -- Performance => (Additional Monitoring Links) => Search Sessions / Instance Locks -- Deadlocks ------------ -- * A deadlock occurs when two or more session are waiting for data locked by each other, -- resulting in all the sessions being blocked. Oracle automatically detects and resolves -- deadlocks by rolling back the statement associated with the transaction that detects the -- deadlock -- first session select salary from employees where employee_id = 100 ; update employees set salary = 1 where employee_id = 100 ; -- second session update employees set salary = 2 where employee_id = 101 ; -- first session update employees set salary = 500 where employee_id = 101 ; -- second session update employees set salary = 200 where employee_id = 100 ; -- first session select salary from employees where employee_id = 100 ; -- SELECT FOR UPDATE -------------------- -- * The Select For Update statement allows you to lock the records in the cursor result set. -- You are not required to make changes to the records in order to use this statement. -- The record locks are released when the next commit or rollback statement is issued. -- First session SELECT last_name FROM employees WHERE employee_id = 100 FOR UPDATE ; -- second session update employees set salary = salary + 1 where employee_id = 100 ; -- WAIT / NOWAIT / SKIP LOCKED -- First session SELECT last_name FROM employees WHERE employee_id = 100 FOR UPDATE ; -- Second session SELECT last_name FROM employees WHERE employee_id = 100 FOR UPDATE NOWAIT ; -- or SELECT last_name FROM employees WHERE employee_id = 100 FOR UPDATE WAIT 3 ; -- or SELECT last_name FROM employees WHERE employee_id IN (100 , 101 , 102) FOR UPDATE SKIP LOCKED ; -- DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT ---------------------------------------------------- -- * The parameter DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements -- will wait in a DML lock queue. show parameter ddl_lock_timeout alter system set ddl_lock_timeout=3 ;
