Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jim.

Asked: February 28, 2018 - 3:11 pm UTC

Last updated: September 24, 2019 - 10:58 am UTC

Version: 12.2

Viewed 10K+ times! This question is

You Asked

For the period when DDL runs to add FK constraint (on un-indexedcolumn), both child and parent tables are unavailable to be read by 2nd session.

Is this behaviour expected / documented?
Is there anyway to avoid issue (which manifests in application being unavailable to users).

NB test case in livesql did not run to completion (quotas) and I did not know how to start 2nd session to attempt to read tables in 1st session.


with LiveSQL Test Case:

and Chris said...

Yes.

Adding a foreign key takes out an exclusive lock on the parent and child tables. You can see this by querying dba_ddl_locks while creating the FK:

select * from dba_ddl_locks
where  session_id = 108
and    ( mode_held <> 'Null' or mode_requested <> 'None' );

SESSION_ID   OWNER    NAME    TYPE                   MODE_HELD   MODE_REQUESTED   
         108 CHRIS    PT      Table/Procedure/Type   Exclusive   None             
         108 CHRIS    CT      Table/Procedure/Type   Exclusive   None             
         108 <null>   CHRIS   73                     Share       None


And as the docs say:

An exclusive DDL lock prevents other sessions from obtaining a DDL or DML lock.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/data-concurrency-and-consistency.html#GUID-5015CC53-4059-4CD6-B892-F211E8BDE2F9

But there is a workaround!

Create the FK novalidated.

This is an "instant" operation. You can then validate it at your leisure - this no longer needs an exclusive lock on parent or child. So DML can continue while this runs.

alter table ct add constraint fk_pt foreign key (p_fk) references pt (p_pk) novalidate;
alter table ct modify constraint fk_pt validate;

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DML lock definition?

Jim Dickson, February 28, 2018 - 7:51 pm UTC

Thanks for the speedy response.
I saw that statement in docs.

But in my scenario, I will grant READ (not SELECT) to app_user.

My understanding is that app_user will not (and cannot because of lack of privileges) take any lock on table.

Have I mis-understood what a DML lock is?

Jim
Chris Saxon
March 01, 2018 - 10:44 am UTC

Nothing will stop you READING another table. The locks relate to WRITING to the parent or child. Even if you issue:

lock table t in exclusive mode;


Other sessions can still query it.

DDL does not block reader?

Jim Dickson, March 02, 2018 - 8:41 am UTC

Chris
Thanks.

I too believe that 2nd session should be able to read pt/ct tables for duration of 1st session's DDL.

But on my system, that is not the case.

I cannot fully supply test case on livesql (as explained in initial post).

Can you run test case with 2 sessions and confirm (or not) my findings.

Thanks
Jim

"alter table modify column not null" DDL blocks readers

Jim Dickson, March 09, 2018 - 2:55 pm UTC

Just realised that "alter table modify column not null" DDL also blocks readers.

This too was unexpected.

Is there a definitive list of which DDL blocks readers?

thanks
NB There is a workaround available but not necessarily equivalent.
Connor McDonald
March 10, 2018 - 5:20 am UTC

Readers aren't blocked. You'll need to give a test case to justify your assertion.

Here is ours:

SQL> create table t nologging tablespace largets
  2  as select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 400 );

Table created.

SQL>
SQL>
SQL> select count(*) from t;

  COUNT(*)
----------
  31540400


So we have a 30million row table. In session 1, I am going start a query, and in session 2 I'll modify a column to not null. I'm using "set time on" so you can see the overlap. I've added some cpu cycles to the query via 'sqrt' so that we know it will run *longer* than the the not null operation.

--
-- session 1
--
13:14:16 SQL> set time on
13:14:16 SQL> select max(owner), max(object_id) from t
13:14:16   2  where sqrt(object_id) != 0;

MAX(OWNER)
--------------------------------------------------------
MAX(OBJECT_ID)
--------------
XDB
        219941


13:14:33 SQL>

--
-- session 2
--
SQL> set time on
13:14:20 SQL> alter table t modify owner not null;

Table altered.

13:14:30 SQL>


So you can see - the query run before, during and after the modify to not null. And if we look at the session waits for session 1:

13:15:11 SQL> select event, time_waited
13:15:15   2  from v$session_event
13:15:19   3  where sid = sys_context('USERENV','SID');

EVENT                                                            TIME_WAITED
---------------------------------------------------------------- -----------
Disk file operations I/O                                                   0
log file sync                                                              1
db file sequential read                                                    1
direct path read                                                           8
SQL*Net message to client                                                  0
SQL*Net message from client                                            13858
events in waitclass Other                                                  0


there is nothing suggesting a wait on access to the table.

adding FK blocks reader - testcase

Jim Dickson, March 13, 2018 - 11:38 am UTC

My original question had a test case in live sql.
Here I post output from my machine.

I believe it demonstrates that whilst DDL to add FK is running, both child and parent are blocked for READS.

Since I have a lot of DDL to run into a Production DB, I want to be able to identify/predict which will block readers.

Same test case as livesql above.
--session1
SQL> set time on
11:30:13 SQL> -- in SESSION2, run select(s)
11:30:13 SQL> alter table ct add constraint fk_pt foreign key (p_fk) references pt (p_pk) ;

Table altered.

11:30:27 SQL>
11:30:27 SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production



-- SESSION2
SQL> set time on
11:30:15 SQL> SELECT COUNT(*) FROM PT ;

COUNT(*)
----------
100

11:30:27 SQL> SELECT COUNT(*) FROM CT ;

COUNT(*)
----------
30000000

11:30:29 SQL>
11:30:29 SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Chris Saxon
March 14, 2018 - 2:29 pm UTC

OK, I was able to reproduce the FK issue. The query was waiting for a "library cache lock".

If this is causing an issue, you can get around it by creating a novalidate FK. Then validating it separately:

alter table ct add constraint fk_PT foreign key (p_fk) references pt (p_pk) novalidate;

alter table ct modify constraint fk_PT validate;


Adding a novalidate FK is an "instant" operation. And you can validate the FK while querying the table in other sessions.

Most DDL statements have an online option nowadays. So if you're finding this or other DDL are blocking other sessions - in particular readers, take this up with support.

library cache lock

Jim Dickson, March 15, 2018 - 11:02 am UTC

Chris

For my own understanding, are you able to explain why "library cache lock" prevents reads against both PT and CT?

The alter table DDL is against CT only.
Why does that prevent parsing of select against PT (which is not being modified, only referenced)

Jim

Oracle Support

Jim Dickson, March 20, 2018 - 5:51 pm UTC

Chris
When you said "So if you're finding this or other DDL are blocking other sessions - in particular readers, take this up with support."

Did you mean ask Oracle Support to clarify whether this is:
a) a product bug or feature
b) a documentation bug or omission

In your opinion, is a) or b) or is there a c) that I am missing?

I have 2 SR with Oracle Support but basically they simply show me the library cache lock, explain parsing and then claim it is how Oracle is designed to work.

I appreciate the workaround (which I was already aware of) but my main point is trying to understand how Oracle works so I can best design my systems.

And for the "alter column not null" ddl issue (I know I owe you a testcase), the workaround is insufficient and causes problems with MV query rewrite.

Oracle Support are proving very frustrating (and I suspect they will say same about me).

Chris Saxon
March 26, 2018 - 10:41 am UTC

Most DDL commands include an online option these days. So if reads are blocked when doing this, there's definitely a problem. Sadly adding an FK doesn't.

So I'm not sure exactly where the issue is with the FK. But adding it novalidated, then validating it avoids the problem.

You'll have to expand on the not null case. I don't understand what the issue is here.

novalidate workaround cannot be validated

A reader, April 16, 2019 - 6:42 pm UTC

Unfortunately the suggested workaround of using novalidate does not work. Tested on Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

In one session:
lock table x in exclusive mode;


And in another:
alter table y add constraint fk_x foreign key (z)
references x(z) novalidate;


Still, this returns the error:
[61000][54] ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Does this work strictly in enterprise edition?
Chris Saxon
April 17, 2019 - 1:19 pm UTC

Why are you locking the table first?

Anyway, supplemental logging downgrades novalidate to be a blocking operation:

List of Nonblocking DDLs Added in 11.2 that Downgrade to Blocking During Supplemental Logging
...
alter table add constraint enable novalidate


https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-and-Manual-Locking-Mechanisms-During-SQL-Operations.html#GUID-1B08DE66-5ED8-4BEF-893B-B887E3A82D50

MODIFY CONSTRAINT VALIDATE throws ORA-04022

Stefan.Daxenbichler, September 24, 2019 - 8:08 am UTC

Hello Tom,

we have been using the trick with creating a constraint "
ENABLE NOVALIDATE
" and then "
MODIFY CONSTRAINT VALIDATE
" successfully for a long time.
However, we noticed on very busy systems that running the VALIDATE part thows an error: "ORA-04022: nowait requested, but had to wait to lock dictionary object". We have set
DDL_LOCK_TIMEOUT 
to 600 seconds, but the error appears much earlier, say after 50 seconds. Any explanation/workaround for this behaviour?

Chris Saxon
September 24, 2019 - 10:58 am UTC

Hmmm, I'm not familiar with that error. MOS note 1958874.1 has this to say about it:

The ORA-04022 message is reported if a SQL statement tries to get a resource in "nowait" mode, but it was busy and it actually had to wait. When a resource is requested NOWAIT (for example: SELECT .. FOR UPDATE NOWAIT), then the expectation is that it will raise an error rather than wait and usually the application is coded such that this is unlikely to happen.

This error is similar to, and may be accompanied by “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired” errors .

This error can often be encountered when maintenance operation are occurring at the same time as user access. In particular, DDL like ALTER command statements can cause issues (as can CREATE and DROP commands) since they require exclusive access to the specified object. NOWAIT statements in the application also increase the likelihood of ORA-04022.


So you need to look and see if anything else is holding a lock on the table while you run the validate.

MOS note 395114.1 also reports that you can get this issue if you're gathering stats on the table at the same time as the DDL.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.