Last updated on August 24th, 2016 at 06:39 pm
The oracle server uses different type of locks(Enqueue) to prevent concurrent access to the data and to prevent destructive interference between the uses
Following types of locks are available in Oracle Server
a. Data Manipulation Locks: its protect the data.Table locks protects the entire table while the row lock protect the selected row
b.Data Doctionary locks-Its protect the schema definition of the tables ,views ,procedure etc
c.Internal locks and latches:Its protect the internal database structure such as datafiles,tablespaces
d. PCM locks:These locks protect the data in Real Application Clusters
These all locks are implemented with the help of enqueue and latch service in Oracle server
Enqueue are the locking mechanism used for managing access to shared resources like table,redo thread,transactions.These are local locks to seralize the access to the shared resource.
it can be requested in different levels/mode: null, row share, row exclusive, share, share row exclusive or exclusive. If a session holds an enqueue in share mode, other sessions can then also take the enqueue in share mode (for the same resource). If a session holds it in exclusive mode, other sessions that wants to get it – independently in which level – they have to wait.
When access is required by a session, a lock structure is obtained and a request is made to acquire access to the resource at a specific level (mode) is made. The lock structure is placed on one of three linked lists which hang off of the resource,called the OWNER (if the enqueue could be acquired), WAITER (if the session is waiting to acquiring the enqueue) and CONVERTER (the session is holding the enqueue in one level but wants to convert it to another) lists.
An enqueue is uniquely defined by it’s TYPE, ID1 and ID2 (which are columns in the V$LOCK view). For example can there only be one enqueue for user SCOTT’s EMP table (identified by TYPE=TM, ID1=object ID, ID2=0).
Common types of enqueues
JQ – Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running, it is protected by a JQ enqueue (which means that only one SNP-process can run the job).
ST – Space management Transaction. The ST enqueue is need to be held every time the session is allocating/deallocating extents (which means wants to change the UET$ and FET$ dictionary tables), like coalescing, drop/truncate segments and disk -sorting. If the session gets a timeout when requesting the ST enqueue, “ORA-1575 timeout waiting for space management” is returned.
TM – DML (Table) enqueue Every time a session wants to lock a table, a TM enq is requested. If a session deletes a row in the parent-table (DEPT) and a referential constraint (foreign key) is created without
an index on the child-table (EMP), or if the session is updating the column(s) that the foreign key references to then a share lock (level 4) is taken on the child table. If another session tries to do changes to the child-table they have to wait (because they want the enqueue in row exclusive mode, and that is not compatible with the share mode). If an index is created on the child-table?s foreign key-column, then no share- lock is required on the child-table.
TX – Transaction. As soon as a transaction is started a TX enqueue is needed. A transaction is uniquely defined by the rollback segment number, the slot number in the rollback segment?s transaction table and the slot
number?s sequence number. A session can be waiting on a TX enqueue for several reasons:
1) Another session is locking the requested row.
2) When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT), then the last session is waiting for the first one to COMMIT or ROLLBACK.
3) There are no free ITL (Interested Transaction List) in the block header (increase INI_TRANS och PCT_FREE
for the segment).
UL – User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST function.
– When a session is waiting on enqueue ,it can be found to be waiting on enqueue wait event in v$session_wait
SELECT * FROM v$session_wait WHERE event=’enqueue’;
-To see if the instance has had locking problems since startup, execute the
-“enqueue timeouts” shows the total number of enqueue operations (get and convert) that timed out before they could complete.
-“enqueue waits” shows how many times a session had to wait for an enqueue. This column is updated first after the wait is finished.
– “enqueue deadlocks” is how many times a deadlock situation has occured (every time the client receives an ORA-60 and a trace file will be created). This value should be zero, else an investigation should be made and the trace files should be checked.
– “enqueue requests” minus “enqueue releases” shows how many locks that are held just now (which is equal to the number of rows in V$LOCK).
-“enqueue conversions” is how many times an session is holding a lock in one mode and then wants to change the mode (for example, first the session is doing an SELECT * FROM emp FOR UPDATE and then a UPDATE emp
SET sal=9876 WHERE empno=7839).
-“enqueue releases” shows how many times it was released (freed).
-v$lock contains a list of all the locks in the system
LMODE column :shows in which mode the session is holding the lock.
0=None, 1=NULL, 2=Row share (SS), 3=Row exclusive (SX), 4=Share (S),
5=Share row exclusive (SSX), 6=Exclusive (X)
REQUEST column: LOck mode that is requested if RRQUEST > 0 the some lock is requested
LMODE > 0 and REQUEST=0 then it is owner of the resource
LMODE=0 and Request >0 then it is waiting for the resource
If a session has values greater than 0 in both LMODE and REQUEST, then the
session is a Converter.
For the lock type TM (table lock), column ID1 is the object_id (or the data_object_id) for that table (could be joined with DBA_OBJECTS). For a lock of type TX (transaction lock), TRUNC( ID1 / 65536 ) shows which rollback segment that is used for the transaction and ID1 – ( rollbacksegment# * 65536 ) shows which slot number in the transaction table that this sessions is using (or want to used).
A lots of view have been created across to take a locking issues.
Following could be used for that purpose
To see waiters from dba_waiters view
set linesize 1000
column waiting_session heading ‘WAITING|SESSION’
column holding_session heading ‘HOLDING|SESSION’
column lock_type format a15
column mode_held format a15
column mode_requested format a15
This script gives information about the user sessions locking a particular object
set linesize 1000
column program format a15
column object format a15
select substr(username||'(‘|| se0.sid||’)’,1,5) “User Session”,
substr(owner,1,5) “Object Owner”,
logon_time “Logon Time”,
process “Unix Process”
from v$access ac, v$session se0
where ac.sid = se0.sid
and Object = ‘&PACKAGE’
order by logon_time,”Object Owner”,”Object”
To find locks in a RAC instance
SELECT inst_id,DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0)
ORDER BY id1, request