LOCKS: Table Locking

Submitted by:Jhon Jhon

Date added:18 December, 2014

Category:PL SQL

Example code for locking table. when a user modifies data through a transaction, that data is locked by that transaction until the transaction is committed or rolled back.

Tags: lock table

Code Snippet:

LOCK TABLE <table_name> IN <lock_mode>  
MODE [NOWAIT | WAIT <seconds>];

-- example:
LOCK TABLE sales.server IN EXCLUSIVE MODE WAIT 60;

-- OR:

LOCK TABLE sales.server IN EXCLUSIVE MODE NOWAIT;

-- show active table locks:

SELECT SUBSTR(a.object,1,25) TABLE_NAME,
SUBSTR(s.user_id,1,15) USER_ID,
SUBSTR(p.pid,1,5) PID,
SUBSTR(p.spid,1,10) SYSTEM_ID,
DECODE(l.TYPE,
'RT','Redo Log Buffer',
'TD','Dictionary',
'TM','DML',
'TS','Temp Segments',
'TX','Transaction',
'UL','User',
'RW','Row Wait',
l.TYPE) LOCK_TYPE
FROM gv$access a, gv$process p, gv$session s, gv$lock l
WHERE s.sid = a.sid
AND s.paddr = p.addr
AND l.sid = p.pid
GROUP BY a.object, s.user_id, p.pid, l.TYPE, p.spid
ORDER BY a.object, s.user_id;
 
 

Comments