Re: Deferrable FKs, any locking issues?

Thanks for your answers.

I feel I should clarify a little.

We are planning on using the mechanism for rewriting the implemention of update of primary keys. It's not often we have to do this, but it does happen. This will involve 4 base tables, such as dept, and maybe 100 tables referencing these tables directly or indirectly. So we are thinking, let's just make all of the fks deferrable.

We already have 6 deferrable FK's, 2 self-referencing and 4 of the header-detail-type. They are initially immediate and are set to deferred in the session only when needed. We have not experienced any problems with these, but there are not much dml against those tables.

Prior to my posting I searched on the web, and this was the only issue with deferrable constraints versus non-deferrable constraints that I could find:

http://www.platinum.com/products/wp/wp_or8.htm Oracle8 Features and Improvements:
A PLATINUM Perspective

• Parallel DML can not execute against tables containing deferrable integrity constraints

I guess that restriction is implemented by a kind of a lock some mysterious place, but I think we can live with that.

So, my question now is 2 questions:

when doing non-parallell insert, update or delete, does Oracle use a different locking-mechanism when the fk's are 'deferrable initially immediate' compared to when the fk's are non-deferrable, given that no constraints has been set to deferred in any session?

Anybody know any other technical reason why it could be unwise to declare fk's as 'deferrable initially immediate' (please don't comment the data model)?

Tia,
Njål A. Ekern Received on Wed Oct 31 2001 - 05:31:14 CST