List:General Discussion« Previous MessageNext Message »
From:Patrick Date:July 29 2003 10:02pm
Subject:Re: Transactions
View as plain text  
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.

<soap box>
    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.
</soap box>

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
> dnelson@stripped
> -- 
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Transactionsb b29 Jul
  • Re: TransactionsDan Nelson29 Jul
    • Re: Transactionsb b30 Jul
      • Re: TransactionsPatrick Sherrill30 Jul
        • Re: Transactionsb b30 Jul
        • Re: TransactionsKaarel31 Jul
          • Re: TransactionsStephan Lukits1 Aug
  • Re: TransactionsPatrick30 Jul
  • Re: TransactionsPaul DuBois30 Jul
RE: TransactionsGilbert Wu30 Jul