DDL LOCKS IN ORACLE

Whenever an object undergo DML operation like insert,update or delete .. , the table will be kept under row wide exclusive lock to maintain consistency between concurrent users. If an object is locked , then other session which try to modify the session cannot modify the table until the blocking session releases the lock

I update the table tabx

SQL> update tabx set order_priority='H' where order_id between 1000 and 10000000;

523789 rows updated.

Now the table is under enq: TX – row lock contention and we cannot add a column on the table as the rows are exclusively locked until the first session issue a commit. So the add column command hang until rollback or commit of update session

kish@exdbx<>alter table tabx add order_cost varchar(20);

You can see the lock mode as 3 – ROW_X (SX): Row Exclusive Table Lock

kish@exdbx<>@colfrmt
Procedure created.
Elapsed: 00:00:00.04
Procedure created.
Elapsed: 00:00:00.02
kish@exdbx<>col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_NAME format a15
col NAME format a15
col TYPE format a15
col PROCESS format 999999
col MODE_HELD format a15
col MODE_REQUESTED format a15
select s.sid,s.serial#,do.OBJECT_NAME,lo.ORACLE_USERNAME,lo.PROCESS,lo.LOCKED_MODE,ddl.OWNER,ddl.TYPE,ddl.MODE_HELD,ddl.MODE_REQUESTED,s.event
from dba_ddl_locks ddl
inner join v$locked_object lo on ddl.SESSION_ID=lo.SESSION_ID
inner join dba_objects do on lo.object_id=do.object_id
inner join v$session s on lo.SESSION_ID = s.sid
where object_name='TABX';kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>  2    3    4    5    6

   SID      SERIAL#   OBJECT_NAME     ORACLE_USERNAME         PROCESS          LOCKED_MODE                               OWNER                                                                 TYPE          MODE_HELD    MODE_REQUESTED    EVENT
---------- ---------- --------------- --------------- ------------------------ ----------- ------------------------------------------------------------------------------------------------------------------------ --------------- --------------- --------------- ----------------------------------------------------------------
        33        303 TABX            TEST            17397                              3 TEST                          18               Null            None            enq: TX - row lock contention
        64         79 TABX            TEST            18900:17096                        3 TEST                          18               Null            None            SQL*Net message from client
        33        303 TABX            TEST            17397                              3 SYS                           Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type

        33        303 TABX            TEST            17397                              3 SYS                           Body     Null            None            enq: TX - row lock contention
        64         79 TABX            TEST            18900:17096                        3 SYS                           Body     Null            None            SQL*Net message from client
        33        303 TABX            TEST            17397                              3 SYS                           Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type

        33        303 TABX            TEST            17397                              3 SYS                           Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type

        64         79 TABX            TEST            18900:17096                        3 NULL                          73               Share           None            SQL*Net message from client
        33        303 TABX            TEST            17397                              3 NULL                          73               Share           None            enq: TX - row lock contention
        33        303 TABX            TEST            17397                              3 NULL                          73               Null            None            enq: TX - row lock contention
        33        303 TABX            TEST            17397                              3 SYS                           Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type

        64         79 TABX            TEST            18900:17096                        3 SYS                           Table/Procedure Null     None            SQL*Net message from client
                                                                                                                         /Type

        33        303 TABX            TEST            17397                              3 MDSYS                         Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type

        33        303 TABX            TEST            17397                              3 SYS                           Body     Null            None            enq: TX - row lock contention
        33        303 TABX            TEST            17397                              3 MDSYS                         Body     Null            None            enq: TX - row lock contention
        33        303 TABX            TEST            17397                              3 SYS                           18               Null            None            enq: TX - row lock contention
        64         79 TABX            TEST            18900:17096                        3 SYS                           18               Null            None            SQL*Net message from client
        33        303 TABX            TEST            17397                              3 SYS                           Table/Procedure Null     None            enq: TX - row lock contention
                                                                                                                         /Type


18 rows selected.

Elapsed: 00:00:00.18

There is no other way than to kill the session which lock the object or wait until the locks are released from the object

DML statements can be filtered using kglnaobj to get the queries which access the objects

kish@exdbx<>select distinct k.kglnaobj from x$kgllk k
inner join v$session s on k.kgllkuse=s.saddr
where s.sid in (33,64) and
k.kglnaobj like '%insert%' or k.kglnaobj like '%delete%' or k.kglnaobj like '%update%';  2    3    4

KGLNAOBJ
------------------------------------------------------------
update WRI$_SCH_CONTROL    set num_votes = :votes,        la
update smon_scn_time set time_mp=:1, time_dp=:2, scn=:3, scn
update sys.col_usage$ set   equality_preds    = equality_pre
delete from WRI$_ALERT_OUTSTANDING where reason_id = :1 and
update tabx set order_priority='H' where order_id between 10
insert into sys.aud$( sessionid,entryid,statement,ntimestamp
delete from compression$ where obj#=:1
update sys.mon_mods$ set inserts = inserts + :ins, updates =

8 rows selected.

Elapsed: 00:00:00.07

Check the sid and serial# to kill

kish@exdbx<>select 'alter system kill session '||sid||','||serial#||';' from v$session where username='TEST';

'ALTERSYSTEMKILLSESSION'||SID||','||SERIAL#||';'
------------------------------------------------------------------------------------------------------------
alter system kill session 33,303;
alter system kill session 64,79;

Kill the blocker session

kish@exdbx<>select
   (select username from v$session where sid=a.sid) blocker,
   a.sid,
   ' is blocking ',
   (select username from v$session where sid=b.sid) blockee,
   b.sid
from
   v$lock a,
   v$lock b
where
   a.block = 1
and
   b.request > 0
and
   a.id1 = b.id1
and
   a.id2 = b.id2;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17

           BLOCKER                SID     'ISBLOCKING'  BLOCKEE                           SID
------------------------------ ---------- ------------- ------------------------------ ----------
TEST                                   64  is blocking  TEST                                   33

Kill the session

kish@exdbx<>alter system kill session '64,79';

Column addition is successful

kish@exdbx<>alter table tabx add order_cost varchar(20);

Table altered.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s