Oracle Database – Resolving DDL Locks

I had this master snapshot table that has multiple slaves snapshot table linked to it. I got a user request to add another column to this master table.

SQL> alter table tablename add (seq number);

The transaction hanged immediately after I run the SQL statement. I open another session and tried to do query the table description, but the query hanged too. That was when I suspect there is a deadlock on the table (I was wondering why there wasn’t a entry in alert.log).

SQL> select * from DBA_DDL_LOCKS where OWNER='orcl';
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
457 orcl orcl 18 Null None
457 orcl OPERATION_MAP Table/Procedure/Type None Share
487 orcl orcl 18 Null None
433 orcl orcl 18 Null None
449 orcl OPERATION_MAP Table/Procedure/Type Exclusive None
449 orcl orcl_RPS Table/Procedure/Type Null None
449 orcl orcl_RPS Body Null None
449 orcl orcl Table/Procedure/Type Null None
449 orcl orcl Body Null None
449 orcl TLOG$_OPERATION_MAP Trigger Null None

SQL> select SID,SERIAL#,MACHINE,SCHEMANAME,state,SECONDS_IN_WAIT,command,program,status from v$session where SCHEMANAME='orcl';
SID SERIAL# MACHINE SCHEMANAME STATE SECONDS_IN_WAIT COMMAND PROGRAM STATUS
---------- ---------- ---------- ---------- ------------------- --------------- ---------- ------------------------------------------------ --------
457 18069 orcl1 orcl WAITING 14109 3 oracle@orcl1 (TNS V1-V3) ACTIVE
495 35458 orcl5 orcl WAITED SHORT TIME 0 3 sqlplus@orcl5 (TNS V1-V3) ACTIVE
487 34658 orcl5 orcl WAITING 1045 0 sqlplus@orcl5 (TNS V1-V3) INACTIVE
499 44075 orcl5 orcl WAITING 11365 0 oracle@orcl5 (TNS V1-V3) INACTIVE
433 5691 orcl5 orcl WAITING 3810 0 sqlplus@orcl5 (TNS V1-V3) INACTIVE
471 44737 orcl5 orcl WAITING 1480 0 sqlplus@orcl5 (TNS V1-V3) INACTIVE
449 22357 orcl5 orcl WAITED SHORT TIME 12308 15 sqlplus@orcl5 (TNS V1-V3) ACTIVE

SQL> alter system kill session '449,22357';
ORA-00031: session marked for kill
SQL> alter system kill session '457,18069';

SQL> select * from DBA_DDL_LOCKS where OWNER='orcl';
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
487 orcl orcl 18 Null None
433 orcl orcl 18 Null None

Apparently, when I run the original transaction, it acquire a exclusive lock on the table. A trigger on the table caused the slave snapshot table to refresh. It tries to select the table and acquire a shared lock. That results in a deadlock since both process are waiting for each other.

After killing the suspected session, I was able to query the table description and the query running the DDL transaction got disconnected.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s