Re: Delete cascade?
Re: Delete cascade?
- Subject: Re: Delete cascade?
- From: Chuck Hill <email@hidden>
- Date: Tue, 20 Mar 2007 10:43:43 -0700
Hi Ian,
On Mar 19, 2007, at 5:47 PM, Ian Joyner wrote:
On 20/03/2007, at 10:48 AM, Chuck Hill wrote:
On Mar 19, 2007, at 4:20 PM, Ian Joyner wrote:
On 20/03/2007, at 2:57 AM, Chuck Hill wrote:
On Mar 18, 2007, at 10:11 PM, Ian Joyner wrote:
What you say makes sense. Maybe, because my dependent entity
does not have any further cascades, I'm not seeing anything
else being brought into memory (maybe it's already there).
I think what I would expect is to see is:
SELECT * FROM <owned_table> WHERE OWNER_KEY = <PRIMARY_KEY of
owner>
<processing of any cascades from those records>
DELETE FROM <owned_table> WHERE OWNER_KEY = <PRIMARY_KEY of owner>
That is not safe. What if records were added to <owned_table>
with an OWNER_KEY = <PRIMARY_KEY of owner> while you were
processing the cascades?
My unease is growing here, but then again I suspect it must be
right because it has been in operation for such a long time.
I suspect you might be misunderstanding the sequence of events.
Well, I'll have to look at a lot more examples to discern what EOF
is doing (I made sure I said EOF this time, rather than lumping it
under the WO term...)
Grin.
Generically, I hope it's doing <begin_transaction> <read and lock
any affected records> <mid_transaction> <process updates>
<end_transaction>
May be a little more in a distributed two-phase commit environment
(if I could remember more of the two weeks I spent at Oracle
learning their internals 10 years ago).
I am pretty sure that is not what it is doing (assuming that I
understand your meaning). I am pretty sure this is what it does
after you call save changes (totally from memory, may be slightly out
of order):
- locks the object store co-ordinator which puts EOF into "single
threaded mode" for everything but local modifications in other ECs
- calls processRecentChanges() to make object graph consistent
- finds deleted objects
- processes delete rules on objects
- if the target of a cascade or nullify rule is not in memory, it is
faulted in the same as when any object is touched. This is where you
are seeing the selects. Each happens in its own DB transaction. I
think, but am not 100% sure, that if you have batch faulting set up
the objects to be deleted will be batch fetched. This can speed
things up.
- targets of nullify rules are added to updatedObjects
- targets of delete rules are fed back into the process so that their
delete rules can be processed
- this continues until no more changes are caused
- EOF generates SQL for all pending insert, update, delete operations
including those caused by delete rules
- EOF starts a DB transaction
- EOF executes previously prepared SQL
- EOF commits DB transaction
- if commit fails, EOF rolls back its changes from the delete rules,
unlocks object store co-ordinator, ends save
- if commit succeeds, EOF updates snapshots and broadcasts changes
for other editing contexts to integrate into their local object store
- unlocks object store co-ordinator, ends save
Note that EOF does not lock anything at the database level, this is
all optimistically locked.
I would guess that WO is handling the cascades due to underlying
DBs handling them in different ways.
No. EOF is handling them that way as it needs to be able to call
validateForDelete on the objects and may need information they
possess to process other delete rules.
OK, but I think that delete validation would be done before the
<mid_transaction>.
I think records could be added to the <owned_table> by another
transaction from another user in any situation, unless maybe the
whole table is locked, or at least the parent record is locked in
such a way that owned records cannot be added. So you want the
DELETE to be atomic.
The delete _is_ atomic.
Wasn't meaning to imply that it wasn't if you are doing multiple
DELETEs rather than just one. It's all in the commit/rollback at
the <end_transaction>.
Maybe hundreds of single DELETE statements, but all within the
context of a single, atomic DB transaction. The DB transaction
spans a shorter time time (read DB, process rules, update DB) than
the single, atomic EOF transaction. It is important to keep in
mind that the DB transaction is "nested" within the EOF one.
Is this one-to-one or can an EOF transaction have multiple DB
transactions?
It is one EOF transaction to one write transaction. There might be a
lot of read ones as it fires faults prepping for the save.
What you _don't_ want is to successfully delete the parent if
someone has just added children.
Right, but if I have locked the parent for delete, then children
shouldn't be added.
That is pessimistic locking, EOF (usually) uses optimistic.
Your suggested delete would do just that.
I think the single deletes would do the same, leaving unreachable
children records in the DB.
No, because when the DELETE for the parent is executed it causes FK
constraint violations and the DB transactions aborts.
That might be acceptable in some situations, but not in all. EOF
pointedly deletes what it knows about and nothing else. If there
are other records, the DB will return an error when the
transaction is committed. This will inform the application which
can then take appropriate action.
But I still think that is the meaning of setting 'cascade' rather
than 'deny'. I think this is introducing a shade of grey in between.
I can see your point.
I suspect that is why the designers of EOF elected to not use
this "optimization". Generally, they seem to have chosen safe
over fast in implementing EOF.
I agree with that, but I have a feeling that expanding the
transaction over time makes for more chance for untoward things
to happen. However, limiting the possibility for a race condition
to occur does not mean that it won't occur and have very nasty
consequences.
I don't think there is any race condition as EOF is implemented.
One process or the other will receive the correct error notification.
I should hope so.
Thanks for your and Peter's answers, I don't want to waste
anymore of people's time on it, since the answer seems to be that
it's working as intended (and hopefully correctly) so it's
nothing I'm doing that's stupid to cause seemingly non-optimum
(and I hope safe) behaviour. I don't believe this is covered in
the wikibook yet.
Learning more about how EOF works is rarely a waste of time. I
doubt that is in the Wiki.
Thanks, it's an interesting topic, I think I'll run back to the
Chris Date and Jim Gray now.
I'm glad that someone else thinks it is an interesting topic. I was
starting to feel like a geek. :-P As for the Chris Date, forget the
SQL. The database is just an artifact of implementation. Objects
are where its at.
Chuck
However, all I see is the DELETEs a single one at a time. Maybe
I just have an simple case?
Thanks
Ian
On 19/03/2007, at 2:08 PM, Peter Vandoros wrote:
Hi Ian,
WebObjects does this because it needs to bring all the EO's
into memory to process the delete rules specified in your
EOModel.
Regards
Peter
Ian Joyner wrote:
I just noticed in my system that if I have the cascade delete
rule set on a to-many relationship in EOModeler that the
deletes happen one by one.
That is if I have a master record with three detail records,
WO issues three SQL DELETEs.
The setup is, each record has a primary key PRIMARY_KEY,
which relates to the owned records which have OWNER_KEY. To
do the delete cascade, I would think that DELETE FROM <owned
table> WHERE OWNER_KEY = <PRIMARY_KEY of owner> would suffice.
Have I got something set up wrong here, or am I missing
something?
Thanks
Ian Joyner
Sportstec
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-
email@hidden)
Help/Unsubs
--
Peter Vandoros
Software Engineer
Etech Group Pty Ltd
Level 3/21 Victoria St
Melbourne VIC 3000
Australia
Ph: +61 3 9639 9677
Fax: +61 3 9639 9577
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
40global-village.net
This email sent to email@hidden
--
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden