CASCADE for delete and update

#1

Hello, I'm using an Oracle database where there are no ON DELETE
CASCADE in the DDL. As a result, when you need to delete something,
you have to calculate all the child tables and delete everything
related there and then finally delete the parent rows.

As a result, I have started to think that ON DELETE CASCADE is a good
idea. Are there any drawbacks to using this when designing a database?

#2

In article <828ffbe3-14fe-49c1-a81f-5132095be443
@i31g2000yqm.googlegroups.com>, metaperl (AT) gmail (DOT) com says...

Quote:

Hello, I'm using an Oracle database where there are no ON DELETE
CASCADE in the DDL. As a result, when you need to delete something,
you have to calculate all the child tables and delete everything
related there and then finally delete the parent rows.

As a result, I have started to think that ON DELETE CASCADE is a good
idea. Are there any drawbacks to using this when designing a database?

If you've got a lot of rows in each of several tables, it can be a
pretty big load on the server, and may block for quite a while while it
does them all at once. If you delete from each table on its own, it's
not such a performance hit.

Also, NOT having deletes cascade, but still having them use a foreign
key, pretty much eliminates the accidental deletion of a parent row (and
thus all its thousands of child rows), because you'll get integrity
violations if you try.

D

#3

On Aug 5, 4:44*pm, metaperl <metap... (AT) gmail (DOT) com> wrote:

Quote:

Hello, I'm using an Oracle database where there are no ON DELETE
CASCADE in the DDL. As a result, when you need to delete something,
you have to calculate all the child tables and delete everything
related there and then finally delete the parent rows.

As a result, I have started to think that ON DELETE CASCADE is a good
idea. Are there any drawbacks to using this when designing a database?

As with anything, there are drawbacks to using ON DELETE CASCADE,
especially if used exclusively. A DBA should carefully analyze the
data being dealt with with and consider whether ON DELETE CASCADE or
ON DELETE SET NULL might be preferable. In some cases the data might
even dictate NOT using an ON DELETE clause.

Here's a (very) brief summary:
- Omitting the ON DELETE clause effectively locks out accidental
deletions. Oracle will not allow deletion of parent rows that are
currently being referenced in the child table. For example, a specific
part in the PARTS table cannot be deleted if it's on any INVOICES.
- ON DELETE CASCADE will delete any ROWS in the child table that
reference the deleted row in the parent table. Using the same example,
deleting a row from the PART table will also delete ALL rows in the
INVOICE table that reference that part (ie. the part will be removed
from all invoices).
- ON DELETE SET NULL will change the foreign key value to NULL in any
parent rows. Again with the parts/invoices... All invoices with that
part will get 'blank' references. Note: this can be followed up with
an UPDATE statement that resets all the NULL values to a different key
value.

Again, the nature of the data will dictate which is best.

Christopher J Smith
President
BeWise Consulting Group

#4

On 08/05/2010 11:12 PM, David Kerber wrote:

Quote:

In article<828ffbe3-14fe-49c1-a81f-5132095be443
@i31g2000yqm.googlegroups.com>, metaperl (AT) gmail (DOT) com says...

Hello, I'm using an Oracle database where there are no ON DELETE
CASCADE in the DDL. As a result, when you need to delete something,
you have to calculate all the child tables and delete everything
related there and then finally delete the parent rows.

As a result, I have started to think that ON DELETE CASCADE is a good
idea. Are there any drawbacks to using this when designing a database?

If you've got a lot of rows in each of several tables, it can be a
pretty big load on the server, and may block for quite a while while it
does them all at once. If you delete from each table on its own, it's
not such a performance hit.

Why do you call this a "performance hit"? The single DELETE on the
parent table will take longer but in sum you delete the same amount of
data either way plus you have more round trips between client and DB
server when doing it manually.

I would rather follow Christopfer's advice to make usage of ON DELETE
CASCADE dependent on the nature of the data. If there is a real whole -
parts relationship and the parts must go if the whole is removed then I
would use ON DELETE CASCADE because it is more robust, may be faster and
also has the benefit of documenting the nature of the data.

Quote:

Also, NOT having deletes cascade, but still having them use a foreign
key, pretty much eliminates the accidental deletion of a parent row (and
thus all its thousands of child rows), because you'll get integrity
violations if you try.

Yes, and IMHO that is a more important aspect than performance.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#5

In article <8ckh2eF4bbU1 (AT) mid (DOT) individual.net>, shortcutter (AT) googlemail (DOT) com
says...

....

Quote:

Why do you call this a "performance hit"? The single DELETE on the
parent table will take longer but in sum you delete the same amount of
data either way plus you have more round trips between client and DB
server when doing it manually.

I'm referring to a hit on other users of the db, not the one doing the
delete. If there are a lot of records to delete from many tables, it
can sometimes block other users until the deletes are done. Spreading
out the deletes and only working on a single table at a time
dramatically reduces the chance of blocking, though it certainly does
increase the time required for the deletes, as you point out.

Quote:

I would rather follow Christopfer's advice to make usage of ON DELETE
CASCADE dependent on the nature of the data. If there is a real whole -
parts relationship and the parts must go if the whole is removed then I
would use ON DELETE CASCADE because it is more robust, may be faster and
also has the benefit of documenting the nature of the data.

Agreed, but there are times when that aspect of the decision is not
clearcut, and you need to consider other factors before making a
decision.

Quote:

Also, NOT having deletes cascade, but still having them use a foreign
key, pretty much eliminates the accidental deletion of a parent row (and
thus all its thousands of child rows), because you'll get integrity
violations if you try.

Yes, and IMHO that is a more important aspect than performance.

Agreed.

Quote:

Kind regards

robert

#6

David Kerber wrote on 13.08.2010 19:49:

Quote:

I'm referring to a hit on other users of the db, not the one doing the
delete. If there are a lot of records to delete from many tables, it
can sometimes block other users until the deletes are done.

A delete should never block other users (unless they are selecting exactly those rows that are being deleted, but that would block the delete not the select).

Which DBMS are you using?

Thomas

#7

On 13.08.2010 20:16, Thomas Kellerer wrote:

Quote:

David Kerber wrote on 13.08.2010 19:49:
I'm referring to a hit on other users of the db, not the one doing the
delete. If there are a lot of records to delete from many tables, it
can sometimes block other users until the deletes are done.

In Oracle the blocking would only affect users doing UPDATE, SELECT FOR
UPDATE or DELETE - and the blocking would be until the end of the
transaction, not the statement! So prolonging the TX by doing more
statements instead of a single DELETE may actually block others longer
than with a single DELETE which cascades.

Quote:

A delete should never block other users (unless they are selecting
exactly those rows that are being deleted, but that would block the
delete not the select).

In Oracle that would only block the DELETE if it was a SELECT FOR
UPDATE. And blocking also works the other way round: the first TX
blocks the second TX whichever of the two comes first.

Quote:

Which DBMS are you using?

OP is using Oracle, I don't know about David.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#8

Quote:

In Oracle the blocking would only affect users doing UPDATE, SELECT FOR
UPDATE or DELETE - and the blocking would be until the end of the
transaction, not the statement! So prolonging the TX by doing more
statements instead of a single DELETE may actually block others longer
than with a single DELETE which cascades.

But only those rows that are being deleted are (b)locked.

IMHO this would be a strange business case that someone is trying to update rows that are being deleted by someone else.
And if that really happens, (b)locking is the right way to handle the concurrent (and conflicting) access.

Quote:

A delete should never block other users (unless they are selecting
exactly those rows that are being deleted, but that would block the
delete not the select).

In Oracle that would only block the DELETE if it was a SELECT FOR
UPDATE. And blocking also works the other way round: the first TX blocks
the second TX whichever of the two comes first.

But again: only those rows that are affected by the DELETE (see above) are (b)locked.

Quote:

Which DBMS are you using?
OP is using Oracle, I don't know about David.

Oh, I didn't see that, thanks

Regards
Thomas

#9

On 14.08.2010 13:17, Thomas Kellerer wrote:

Quote:

In Oracle the blocking would only affect users doing UPDATE, SELECT FOR
UPDATE or DELETE - and the blocking would be until the end of the
transaction, not the statement! So prolonging the TX by doing more
statements instead of a single DELETE may actually block others longer
than with a single DELETE which cascades.

But only those rows that are being deleted are (b)locked.

Yes, that's true. But this does not make a difference between DELETE
CASCADE and manual cascading. The rows affected are the same in both
situations. The point is that David's assessment that manual deletion
in multiple stages leads to shorter blocking is generally not true.

For Oracle it is also important to note that there should be an index on
the referenced column because otherwise the whole parent table might be
locked. This is true at least until 10g IIRC - see here for more details:

http://jonathanlewis.wordpress.com/2...5/lock-horror/

Quote:

IMHO this would be a strange business case that someone is trying to
update rows that are being deleted by someone else.

If that business case would be nonexistent we probably would not have
RDBMS that support ACID properties of transactions. Point is, as this
*can* happen you need a system that supports transactional integrity in
some way. If not you have to deal with errors that occur from these
situations yourself (as is the case with some NoSQL databases).

Quote:

And if that really happens, (b)locking is the right way to handle the
concurrent (and conflicting) access.

Absolutely! And adding to that: Oracle does it better than some other
DB vendors which only recently improved their concurrency models (SQL
Server comes to mind; IIRC versions up to 2000 did not handle this very
gracefully).

Quote:

A delete should never block other users (unless they are selecting
exactly those rows that are being deleted, but that would block the
delete not the select).

In Oracle that would only block the DELETE if it was a SELECT FOR
UPDATE. And blocking also works the other way round: the first TX blocks
the second TX whichever of the two comes first.

But again: only those rows that are affected by the DELETE (see above)
are (b)locked.

Which DBMS are you using?
OP is using Oracle, I don't know about David.

Oh, I didn't see that, thanks

YWC. Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#10

Quote:

The point is that David's assessment that manual deletion in
multiple stages leads to shorter blocking is generally not true.

I completely agree (that manual deletion is different to a cascading delete). The only situation where the manual deletion would behave differently if it was done in auto-commit mode, essentially having one transaction per row.

Quote:

IMHO this would be a strange business case that someone is trying to
update rows that are being deleted by someone else.

If that business case would be nonexistent we probably would not have
RDBMS that support ACID properties of transactions. Point is, as this
*can* happen you need a system that supports transactional integrity in
some way. If not you have to deal with errors that occur from these
situations yourself (as is the case with some NoSQL databases).

True. But in 20 years I have rarely had applications where two different users would _delete_ and update the same set of rows concurrently (concurrent updates & selectare very common though)

Quote:

And if that really happens, (b)locking is the right way to handle the
concurrent (and conflicting) access.

Absolutely! And adding to that: Oracle does it better than some other DB
vendors which only recently improved their concurrency models (SQL
Server comes to mind; IIRC versions up to 2000 did not handle this very
gracefully).

Yes. SQL Server 2000 really had a horrible concurrency when updates and selects where happening at the same time (btw. DB2 wasn't that better as well, even though IBM claims that the concurrency model in DB2 is superior to the one in Oracle)

PostgreSQL behaves the same as Oracle (it doesn't even need the FK indexing, although that will help improve performance)

Regards
Thomas