Locks on oracle database & Oracle applications Processing.

In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multiuser database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multiuser database.
* 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.
In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.
How do we find locks?
DBA_DML_LOCKS
Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.

col "O/SUser" FOR a10
col OraUser FOR a20
col Blocking FOR a12
col ObjLocked FOR a15
col sid FOR 99999999
col pid FOR a8
SELECT s.osuser "O/SUser", s.username "OraUser", s.sid "SID",
s.serial# "Serial", s.process "PID", s.status "Status",l.name "ObjLocked",
l.mode_held "Lock Held" FROM V$SESSION s,DBA_DML_LOCKS l,V$PROCESS p
WHERE l.session_id = s.sid AND p.addr = s.paddr;

Just another way to find the locks

SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;

Just to find a raw sql on a table lock!

SELECT * FROM dba_dml_locks WHERE name LIKE ‘%RA_CUSTOMER_TRX_ALL%’ SESSION_ID    OWNER     NAME                           MODE_HELD     MODE_REQUESTED LAST_CONVERT    BLOCKING_OTHERS                        
365           AR        RA_CUSTOMER_TRX_ALL            Row-X (SX)    None           5081            NOT Blocking                            
365           AR        RA_CUST_TRX_LINE_SALESREPS_ALL Row-X (SX)    None           5081            NOT Blocking                            
365           AR        RA_CUST_TRX_LINE_GL_DIST_ALL   Row-X (SX)    None           5081            NOT Blocking

Another simple scripts to find sesion, serial for locks. locks.sql displays all sessions holding a lock on a table or row.
Knowing the session/serial#, you can kill some sessions unwanted for you!

SET term ON;
SET LINES 130;
COLUMN sid_ser format a12 heading ’session,’serial#’;
COLUMN username format a12 heading ‘os user/’db user‘;
column process format a9 heading ‘os‘process’;
COLUMN spid format a7 heading ‘trace’number‘;
column owner_object format a35 heading ‘owner.object‘;
column locked_mode format a13 heading ‘locked‘mode’;
COLUMN STATUS format a8 heading ’status’;
spool locks.lst;

SELECT
    substr(to_char(l.session_id)”‘,’”to_char(s.serial#),1,12) sid_ser,
    substr(l.os_user_name”‘/’”l.oracle_username,1,12) username,
    l.process,
    p.spid,
    substr(o.owner”‘.’”o.object_name,1,35) owner_object,
    decode(l.locked_mode,
             1,‘No Lock’,
             2,‘Row Share’,
             3,‘Row Exclusive’,
             4,‘Share’,
             5,‘Share Row Excl’,
             6,‘Exclusive’,NULL) locked_mode,
    substr(s.STATUS,1,8) STATUS
FROM
    v$locked_object l,
    all_objects     o,
    v$session       s,
    v$process       p
WHERE
    l.object_id = o.object_id
AND l.session_id = s.sid
AND s.paddr      = p.addr
AND s.STATUS != ‘KILLED’;

spool off;

This produces an output like this

session,     os user/     os        trace                                       locked                                          
serial#      db user      process   number  owner.object                        mode          status                              
———— ———— ——— ——- ———————————– ————- ——–                            
81,47000     oadv1qtc/APP           23844   APPS.JTF_FM_RAPID_M_QTBL            Row Exclusive ACTIVE                            
239,31       oadv1qtc/APP 2455      27900   APPLSYS.FND_CONCURRENT_QUEUES       Row Exclusive INACTIVE                          
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_I  Row Exclusive ACTIVE                            
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_I  Row Exclusive ACTIVE                            
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_H  Row Exclusive ACTIVE                            
577,29592    oadv1qtc/APP           23268   APPS.JTF_FM_RAPID_RS_QTBL           Row Exclusive ACTIVE                            
498,1        oadv1qtc/APP           28452   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_T  Row Exclusive ACTIVE                            
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_I  Row Exclusive ACTIVE                            
236,27       oadv1qtc/APP           27822   APPS.JTF_FM_RAPID_B_QTBL            Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_I Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_H Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_XXIBE_CUST_CON_ROLES_ST_T Row Exclusive ACTIVE                            
179,2063     oadv1qtc/APP 11261     4449    CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                          
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_H  Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_I       Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_H       Row Exclusive ACTIVE                            
536,55348    oadv1qtc/APP           16323   APPS.JTF_FM_RAPID_RQ_QTBL           Row Exclusive ACTIVE                            
557,3240     /AQADM                 26352   AQADM.AQ$_SO_LINES_ALL11_ST_T       Row Exclusive ACTIVE                            
500,6        oadv1qtc/APP           28456   APPLSYS.AQ$_FND_CP_GSM_IPC_AQTBL_T  Row Exclusive ACTIVE                            
19,112       oadv1qtc/APP 2007      27500   APPLSYS.FND_CONCURRENT_REQUESTS     Row Exclusive INACTIVE                          
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_I    Row Exclusive ACTIVE                            
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_H  Row Exclusive ACTIVE                            
512,6        oadv1qtc/APP           28484   APPLSYS.AQ$_FND_CP_GSM_OPP_AQTBL_T  Row Exclusive ACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_H    Row Exclusive ACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_OE_CMRS_INCR_CUST_ST_T    Row Exclusive ACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_I              Row Exclusive ACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_H              Row Exclusive ACTIVE                            
106,6631     /AQADM                 26368   AQADM.AQ$_CCAXB11_ST_T              Row Exclusive ACTIVE                            
207,185      oadv1qtc/APP           27763   APPS.JTF_FM_RAPID_RQ_QTBL           Row Exclusive ACTIVE                            
549,13404    oadv1qtc/APP 21933     15264   GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                          
179,2063     oadv1qtc/APP 11261     4449    GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                          
549,13404    oadv1qtc/APP 21933     15264   CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                          
179,2063     oadv1qtc/APP 11261     4449    CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                          
549,13404    oadv1qtc/APP 21933     15264   CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                          
90,4133      oadv1qtc/APP 11987     4953    GESADM.MLOG$_XXCMF_ATP_LINES_ARCHIV Row Exclusive INACTIVE                          
90,4133      oadv1qtc/APP 11987     4953    CMF.XXCMF_ATP_HEADERS_ARCHIVE       Row Exclusive INACTIVE                          
90,4133      oadv1qtc/APP 11987     4953    CMF.XXCMF_ATP_LINES_ARCHIVE         Row Exclusive INACTIVE                             37 rows selected.

Yet another script that gives more locks information

SELECT /*+ ordered */
        –b.kaddr,
        c.sid,
        lock_waiter.waiting_session,
        lock_blocker.holding_session,
        c.program,
        c.osuser,
        c.machine,
        c.process,
        decode(u.name,
                NULL,”,
                u.name”‘.’”o.name
        ) object,
        c.username,
        decode
        (
                b.type,
                ‘BL’, ‘Buffer hash table instance lock’,
                ‘CF’, ‘Control file schema global enqueue lock’,
                ‘CI’, ‘Cross-instance function invocation instance lock’,
                ‘CU’, ‘Cursor bind lock’,
                ‘DF’, ‘Data file instance lock’,
                ‘DL’, ‘direct loader parallel index create lock’,
                ‘DM’, ‘Mount/startup db primary/secondary instance lock’,
                ‘DR’, ‘Distributed recovery process lock’,
                ‘DX’, ‘Distributed transaction entry lock’,
                ‘FS’, ‘File set lock’,
                ‘IN’, ‘Instance number lock’,
                ‘IR’, ‘Instance recovery serialization global enqueue lock’,
                ‘IS’, ‘Instance state lock’,
                ‘IV’, ‘Library cache invalidation instance lock’,
                ‘JQ’, ‘Job queue lock’,
                ‘KK’, ‘Thread kick lock’,
                ‘LA’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LB’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LC’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LD’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LE’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LF’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LG’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LH’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LI’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LJ’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LK’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LL’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LM’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LN’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LO’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘LP’,‘Library cache lock instance lock (A..P=namespace);’,
                ‘MM’, ‘Mount definition global enqueue lock’,
                ‘MR’, ‘Media recovery lock’,
                ‘NA’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NB’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NC’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘ND’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NE’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NF’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NG’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NH’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NI’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NJ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NK’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NL’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NM’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NN’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NO’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NP’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NQ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NR’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NS’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NT’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NU’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NV’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NW’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NX’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NY’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘NZ’, ‘Library cache pin instance lock (A..Z=namespace)’,
                ‘PF’, ‘Password File lock’,
                ‘PI’, ‘Parallel operation locks’,
                ‘PS’, ‘Parallel operation locks’,
                ‘PR’, ‘Process startup lock’,
                ‘QA’,‘Row cache instance lock (A..Z=cache)’,
                ‘QB’,‘Row cache instance lock (A..Z=cache)’,
                ‘QC’,‘Row cache instance lock (A..Z=cache)’,
                ‘QD’,‘Row cache instance lock (A..Z=cache)’,
                ‘QE’,‘Row cache instance lock (A..Z=cache)’,
                ‘QF’,‘Row cache instance lock (A..Z=cache)’,
                ‘QG’,‘Row cache instance lock (A..Z=cache)’,
                ‘QH’,‘Row cache instance lock (A..Z=cache)’,
                ‘QI’,‘Row cache instance lock (A..Z=cache)’,
                ‘QJ’,‘Row cache instance lock (A..Z=cache)’,
                ‘QK’,‘Row cache instance lock (A..Z=cache)’,
                ‘QL’,‘Row cache instance lock (A..Z=cache)’,
                ‘QM’,‘Row cache instance lock (A..Z=cache)’,
                ‘QN’,‘Row cache instance lock (A..Z=cache)’,
                ‘QP’,‘Row cache instance lock (A..Z=cache)’,
                ‘QQ’,‘Row cache instance lock (A..Z=cache)’,
                ‘QR’,‘Row cache instance lock (A..Z=cache)’,
                ‘QS’,‘Row cache instance lock (A..Z=cache)’,
                ‘QT’,‘Row cache instance lock (A..Z=cache)’,
                ‘QU’,‘Row cache instance lock (A..Z=cache)’,
                ‘QV’,‘Row cache instance lock (A..Z=cache)’,
                ‘QW’,‘Row cache instance lock (A..Z=cache)’,
                ‘QX’,‘Row cache instance lock (A..Z=cache)’,
                ‘QY’,‘Row cache instance lock (A..Z=cache)’,
                ‘QZ’,‘Row cache instance lock (A..Z=cache)’,
                ‘RT’, ‘Redo thread global enqueue lock’,
                ‘SC’, ‘System commit number instance lock’,
                ‘SM’, ‘SMON lock’,
                ‘SN’, ‘Sequence number instance lock’,
                ‘SQ’, ‘Sequence number enqueue lock’,
                ‘SS’, ‘Sort segment locks’,
                ‘ST’, ‘Space transaction enqueue lock’,
                ‘SV’, ‘Sequence number value lock’,
                ‘TA’, ‘Generic enqueue lock’,
                ‘TS’, ‘Temporary segment enqueue lock (ID2=0)’,
                ‘TS’, ‘New block allocation enqueue lock (ID2=1)’,
                ‘TT’, ‘Temporary table enqueue lock’,
                ‘UN’, ‘User name lock’,
                ‘US’, ‘Undo segment DDL lock’,
                ‘WL’, ‘Being-written redo log instance lock’,
                b.type
        ) lock_type,
        decode
        (
                b.lmode,
                0, ‘None’,           /* Mon Lock equivalent */
                1, ‘Null’,           /* N */
                2, ‘Row-S (SS)’,     /* L */
                3, ‘Row-X (SX)’,     /* R */
                4, ‘Share’,          /* S */
                5, ‘S/Row-X (SRX)’,  /* C */
                6, ‘Exclusive’,      /* X */
                to_char(b.lmode)
        ) mode_held,
        decode
        (
                b.request,
                0, ‘None’,           /* Mon Lock equivalent */
                1, ‘Null’,           /* N */
                2, ‘Row-S (SS)’,     /* L */
                3, ‘Row-X (SX)’,     /* R */
                4, ‘Share’,          /* S */
                5, ‘S/Row-X (SSX)’,  /* C */
                6, ‘Exclusive’,      /* X */
                to_char(b.request)
        ) mode_requested
FROM
        v$lock b
        ,v$session c
        ,sys.user$ u
        ,sys.obj$ o
        ,( SELECT * FROM sys.dba_waiters) lock_blocker
        ,( SELECT * FROM sys.dba_waiters) lock_waiter
WHERE
b.sid = c.sid
AND u.user# = c.user#
AND o.obj#(+) = b.id1
AND lock_blocker.waiting_session(+) = c.sid
AND lock_waiter.holding_session(+) = c.sid
AND c.username != ‘SYS’
ORDER BY kaddr, lockwait;