fbpx

Oracle Lock Management Examples

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

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…