Re: Dr. Miguel 'Optimistic Locking' Arroz [was Re: WebObjects stress Testing tool?]
Re: Dr. Miguel 'Optimistic Locking' Arroz [was Re: WebObjects stress Testing tool?]
- Subject: Re: Dr. Miguel 'Optimistic Locking' Arroz [was Re: WebObjects stress Testing tool?]
- From: Kieran Kelleher <email@hidden>
- Date: Fri, 4 Dec 2009 09:19:05 -0500
Actually, let me rephrase that. My brain was not working earlier since
I had not yet had breakfast :-)
The lack of deferred constraints prevents one using foreign key
constraints in MySQL (InnoDB specifically supports FK constraints).
The problem is that MySQL will not let you delete related rows during
a transaction, rather than let you execute the full statement and then
check for FK integrity afterwards.
Please correct me if I am mistaken in my thinking here, but in
WebObjects, the "Deny" delete rule of a relationship essentially does
the same thing. Also, in java code, we have the ERXGuardedInterface~
(from now on let's say "~" means "or a class spelled something like
that) which implements canDelete() and that allows us to prevent
deletion based on logic. We also have validateForDelete to prevent
deletions in EOF logic.
Since I have never used FL constraints in MySQL, Chuck or someone else
can explain their exact role/benefit better I am sure.
Now on the orphan issue. If you have orphans, it means that the FKs
did not get nullified or cascade deleted or sth like that. It means
your database did not roll back the failing transaction fully. The
reason transactions protect you is because when you delete an object
and the related items that have a Nullify relationship Delete rule, WO
updates those foreign keys to null in the same transaction.
And if you are dealing with orphans on a MySQL database, it means one
of three things are wrong.
(1) One of your tables is a MyISAM table. Use SHOW CREATE TABLE to
check the engine type. MyISAM tables do not roll back. *Never* mix
MyISAM and InnoDB tables updates in the same transaction!
(2) Some fool in the *past* had a table that was MyISAM and screwed up
the data integrity leaving nice tripwires for you to cleanup. My
advice is get on the command line and find those orphans and fix them.
Don't dirty your business logic to handle a screwed up database. Fix
the data integrity and move on, never using MyISAM again
(3) Some fool in the recent present put a 'No Action' as a Delete rule
on the relationship in the EOModel, or
(4) Some fool upgraded to MySQL 5.0+, did not read the release notes,
and forgot to add the property 'innodb_rollback_on_timeout' in your /
etc/my.cnf. This will screw you in rare cases that your app has died
in the middle of a transaction I guess. I am baffled why that was
needed after 5.0.13. Here is the excerpt form the release notes
(copied from my own my.cnf file):
<snip>
# FROM The Release Notes for 5.0:
# Incompatible change: As of MySQL 5.0.13, InnoDB rolls back only the
last statement on a transaction
# timeout. In MySQL 5.0.32, a new option, --
innodb_rollback_on_timeout, causes InnoDB to abort and roll
# back the entire transaction if a transaction timeout occurs (the
same behavior as in MySQL 4.1).
innodb_rollback_on_timeout
</snip>
On Dec 4, 2009, at 7:57 AM, David Avendasora wrote:
On Dec 4, 2009, at 7:17 AM, Kieran Kelleher wrote:
Fair enough. Finally, we have one specific strike against it. ;-)
Since we have Delete rules in the EOModel, is this feature a
"safety net" that is needed for external non-WO apps that are
accessing the database?
What do you mean? That EOF will clean up orphaned objects that
didn't get cascade-deleted by EOF when the related object was
deleted? No. If EOF loads rows that have invalid FKs in them, it
will create a fault for that object, then when you go to try to
follow that relationship and the fault is fired, you'd get a missing
object exception. I've had to deal with this exact situation before.
I have never implemented constraints and have yet to have an orphan
record since transactions/rollback protect against that, right?
How would transactions protect you from having an invalid FK if you
don't have any FK constraints?
Dave
-Kieran
On Dec 4, 2009, at 12:39 AM, Chuck Hill wrote:
On Dec 3, 2009, at 5:44 PM, Lachlan Deck wrote:
On 04/12/2009, at 12:25 PM, Kieran Kelleher wrote:
I was just wondering why people were saying disaster, toy,
etc .... wondering if I am missing something and going to lose
all my data next week!
Like I said, I have not used FrontBase or PostgreSQL in
production and have never touched PostgreSQL, so if it is
comparison you are after, I don't have one. However I will say
that I started using MySQL at 4.0, then 4.1 and now 5.0. Being
the stickler for learning as much as I think I need to do
something right, I bought the original Jeremy Zawodny book
"Advanced MySQL" and that gave me a clear understanding and
confidence of how to set the thing up. I have never used the
cluster engine (NDB).... yet. I have always used InnoDB. I used
MyISAM once for a readonly database (about 5 tables only) that
has geocode lookups on tables of about 100 million rows because
at the time it appeared faster (with mysql 4.0 at the time) to
do points in radius operations which sometimes selected up to
500,000 rows in a select. My main ongoing project is InnoDB and
every user is a user that does edits, with a small percentage of
users absolutely hammering the database with production
processing during business hours each day. I replicate to 3
slaves on that project purely for backup. It runs 24/7 and
almost never have any "Scheduled Maintenance" downtime garbage
because of the fact that the replication slaves are where the
backups happen. One slave is remote and 2 onsite with the
master. The binary logs on the master are written to a separate
phyaical drive
Why do I like it?
- It is free
- It has never left me down - no data/table corruption
- It is simple to set up and configure
- replication is a breeze to set up
- It has multiple engine types for different scenarios
- and finally the reason that most people like what they use: "I
am comfortable with it" ;-)
What would I like that I think I might be missing?
- transactional structure changes (ie., create table and roll
back.) transactions in InnoDB only apply to table/record edits
themselves.
+ Deferred constraints!
That is a pretty big strike against MySQL in my books.
Chuck
--
Chuck Hill Senior Consultant / VP Development
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
_______________________________________________
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
David Avendasora
Senior Software Engineer
K12, Inc.
*****
WebObjects Documentation Wiki : http://wiki.objectstyle.org/confluence/display/WO/
*****
WebObjects API: http://developer.apple.com/legacy/mac/library/documentation/MacOSXServer/Reference/WO54_Reference/index.html
*****
_______________________________________________
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