Well said Dan. While foreign keys, cascades and built-in transactions are
convenient, atomicity and referential integrity are readily attained by
proper implementation and the appropriate code.
Setting buffers,dirty flags and commit functions really is the
responsibility of the application programmer. A lack of referential
integrity is not the same as data corruption. More creative coding
and less whining.
No offense intended, I just need an occasional rant.
----- Original Message -----
From: "Dan Nelson" <dnelson@stripped>
To: "b b" <hell_wild_hot2@stripped>
Cc: "mysqllist" <mysql@stripped>
Sent: Tuesday, July 29, 2003 4:59 PM
Subject: Re: Transactions
> In the last episode (Jul 29), b b said:
> > Most web hosting companies run the mysql standard. Which means one
> > can't run transactions. If that is the case, then how do you handle
> > many to many relationships with truely normalized manner without
> > risking data corruption
> > For example: You have an org, ctry tables and a middle table orgCtry.
> > The middle table has the two foriegn keys one from org and one from
> > ctry. This way an org could be operating in many countries and a
> > country could have many organizations.
> > Now to insert an organization "AAA" that operates in USA, Canada, and
> > Argentina one would need typically to do four sql statements:
> > insert into org(name, .....
> > get the newly inserted org ID
> > insert into orgCtry(ID for org, ID for USA) ...
> > insert into orgCtry(ID for org, ID for Canada) ...
> > insert into orgCtry(ID for org, ID for Argentina)
> > ...
> > To do the above securely one has to put it in a transaction. If
> You really mean "To do the above atomically" here. You can still do it
> securely, but you have to make the client apppplication smarter. If
> the server crashes after the 2nd insert, you end up with 1 record in
> orgName and 1 in orgCtry. So when the end-user retries the request,
> the client has to realize that AAA already exists and simply insert the
> remaining two records.
> When you decide to delete the "AAA" user, make sure you delete
> dependent records first. So remove the orgCtry records before removing
> the parent record in org. Otherwise, if the server crashes, you end up
> with dangling records that you have to clean up in a maintenence script
> (not difficult, but not necessary if you delete in the right order).
> Transactions are most important in places where you have to update
> multiple records or tables, and /cannot/ allow a partial update
> (double-entry bookkeeping, etc). Foreign keys (and subqueries) are
> handy to have, but you can always duplicate their functionality with
> extra code in the client.
> Dan Nelson
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1