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