{"id":393,"date":"2020-12-15T13:21:50","date_gmt":"2020-12-15T03:21:50","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=393"},"modified":"2023-07-21T14:59:47","modified_gmt":"2023-07-21T04:59:47","slug":"database-locking","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2020\/12\/15\/database-locking\/","title":{"rendered":"Database Locking"},"content":{"rendered":"\n

How to identify lockers<\/strong><\/p>\n\n\n\n

This article will explain about locks on rows and on objects in ORACLE.<\/p>\n\n\n\n

Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).<\/p>\n\n\n\n

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.<\/p>\n\n\n\n

Scenario 1:<\/p>\n\n\n\n

Terminal A is locking a row and Terminal B is waiting on it:<\/p>\n\n\n\n

TERMINAL A
<\/strong>
    SQL> update map1 set col2=’MYLOCK’ where col1=300;
    1 row updated.

    SQL>
    (..no commit here..)<\/p>\n\n\n\n

TERMINAL B
<\/strong>
    SQL> update map1 set col2=’NEWVAL2′ where col1=300;
    (..waiting..)<\/p>\n\n\n\n

Now, lets create a session as a DBA User to monitor the system, this query will tell the locking and waiting SIDs.<\/p>\n\n\n\n

    SELECT vh.sid locking_sid,
     vs.status status,
     vs.program program_holding,
     vw.sid waiter_sid,
     vsw.program program_waiting
    FROM v$lock vh,
     v$lock vw,
     v$session vs,
     v$session vsw
    WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
     FROM v$lock
     WHERE request = 0
     INTERSECT
     SELECT id1, id2
     FROM v$lock
     WHERE lmode = 0)
     AND vh.id1 = vw.id1
     AND vh.id2 = vw.id2
     AND vh.request = 0
     AND vw.lmode = 0
     AND vh.sid = vs.sid
     AND vw.sid = vsw.sid;<\/p>\n\n\n\n

LOCKING_SID STATUS   PROGRAM_HOLDING                WAITER_SID PROGRAM_WAITING
———– ——– —————————— ———- ——————————
 144        ACTIVE   sqlplus@rh4_node1.fadeserver.n        131 sqlplus@rh4_node1.fadeserver.n
                     et (TNS V1-V3)                            et (TNS V1-V3)<\/p>\n\n\n\n

Here is an expanded version of the same query, it also includes jobs information.<\/p>\n\n\n\n

    SELECT vs.username,
     vs.osuser,
     vh.sid locking_sid,
     vs.status status,
     vs.module module,
     vs.program program_holding,
     jrh.job_name,
     vsw.username,
         vsw.osuser,
     vw.sid waiter_sid,
     vsw.program program_waiting,
     jrw.job_name,
     ‘alter system kill session ‘ || ””|| vh.sid || ‘,’ || vs.serial# || ”’;’  “Kill_Command”
    FROM v$lock vh,
     v$lock vw,
     v$session vs,
     v$session vsw,
     dba_scheduler_running_jobs jrh,
     dba_scheduler_running_jobs jrw
    WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
     FROM v$lock
     WHERE request = 0
     INTERSECT
     SELECT id1, id2
     FROM v$lock
     WHERE lmode = 0)
     AND vh.id1 = vw.id1
     AND vh.id2 = vw.id2
     AND vh.request = 0
     AND vw.lmode = 0
     AND vh.sid = vs.sid
     AND vw.sid = vsw.sid
     AND vh.sid = jrh.session_id(+)
     AND vw.sid = jrw.session_id(+);<\/p>\n\n\n\n

USERNAME OSUSER  LOCKING_SID STATUS   MODULE  PROGRAM_HO JOB_N USERNAME OSUSER  WAITER_SID PROGRAM_WA JOB_N Kill_
——– ——- ———– ——– ——- ———- —– ——– ——- ———- ———- —– —–
CACOSTA  oracle          144 ACTIVE   SQL*Plu sqlplus@rh       CACOSTA  oracle         131 sqlplus@rh       alter
                                      s       4_node1.fa                                   4_node1.fa        syst
                                              deserver.n                                   deserver.n       em ki
                                              et (TNS V1                                   et (TNS V1       ll se
                                              -V3)                                         -V3)             ssion
                                                                                                            ‘144
                                                                                                            ,3897
                                                                                                            3′;<\/p>\n\n\n\n

We can see that the user CACOSTA, sid 144 is locking the session 131.<\/p>\n\n\n\n

Scenario 2:
We are performing a DDL (alter somehow the object) and we get an ORA-00054 error.<\/p>\n\n\n\n

I have canceled the waiting session in the example above and now I\u2019m creating an index on the table:<\/p>\n\n\n\n

    SQL> create index ind2 on map1(col2);
    create index ind2 on map1(col2)
     *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified<\/p>\n\n\n\n

If I re-run the query fromt he previous scenario it won\u2019t return any rows, because there are no waiting sessions (I canceled the waiting update).<\/p>\n\n\n\n

First we need to find out the object ID:<\/p>\n\n\n\n

    SQL> select object_id from dba_objects
     2  where owner=’CACOSTA’
     3  and object_name=’MAP1′;

     OBJECT_ID
    ———-
     52255<\/p>\n\n\n\n

Now lets see who is blocking the object 52255<\/p>\n\n\n\n

    SELECT c.owner,
     c.object_name,
     c.object_type,
     b.sid,
     b.serial#,
     b.status,
     b.osuser,
     b.machine
    FROM v$locked_object a, v$session b, dba_objects c
    WHERE b.sid = a.session_id AND a.object_id = c.object_id
    and a.object_id=52255;<\/p>\n\n\n\n

OWNER    OBJECT_NAME   OBJECT_TYPE                SID    SERIAL# STATUS   OSUSER  MACHINE
——– ————- ——————- ———- ———- ——– ——- —————
CACOSTA  MAP1          TABLE                      144      38973 ACTIVE<\/p>\n","protected":false},"excerpt":{"rendered":"

How to identify lockers This article will explain about locks on rows and on objects in ORACLE. Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s). When there is a … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-393","post","type-post","status-publish","format-standard","hentry","category-oracle-database"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/393","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=393"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/393\/revisions"}],"predecessor-version":[{"id":394,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/393\/revisions\/394"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=393"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}