-- * 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 ;
