List:General Discussion« Previous MessageNext Message »
From:Patrick Sherrill Date:July 30 2003 12:10pm
Subject:Re: Transactions
View as plain text  
I'm reticent to consume any more of this lists bandwidth and trust this will
end the thread, but here is my point.  There are many 'gotchas' to consider
when developing an application that uses any dynamic file structure from
simple flat ascii
files to engorged dbms's. Add multi-user and multi-tasking to the mix and
you have a tiger by the tail. The bottom line is, it is the responsibility
the programmer to ensure that every tool they use is safely and
correctly implemented.  Transactions or commit and rollback levels help, but
they are only a small piece of the package.  It is dangerous to assume that
because referential integrity has been maintained, that the data is as
intended.  Any application that allows its data to be manipulated in other
than a read-only status is responsible for its integrity.  Your example of
the web browser is a good one.  Using the http protocol and html to hook-up
heterogeneous systems has become common and is relatively simple.  It is, as
you point out, stateless. The browser does not know or care about the
success or failure of an operation.  It is the responsibility of the person
writing the html and any other subsequent code to communicate the
appropriate result to the client. You certainly would not want to lock a
region or even a row from a
stateless client.  If two people access the same record/row at the same time
and change non-key information but commit the changes sequentially 2 seconds
apart the referential integrity could be correct, but the data is not as the
first person intended. In this scenario, the programmer needs to provide a
mechanism to advise the first
client that his changes were overwritten, thereby turning a stateless event
into a stateful one and maintaining control of the application.

A robust file handler or dbms is a wonderful tool but it is only a tool and
does not relieve a programmer of their responsibility. That's my point.


BTW MySQL is one of the best SQL Servers out there, and our SQL Server of

----- Original Message ----- 
From: "b b" <hell_wild_hot2@stripped>
To: "Dan Nelson" <dnelson@stripped>
Cc: "mysqllist" <mysql@stripped>
Sent: Tuesday, July 29, 2003 11:11 PM
Subject: Re: Transactions

>  Good point. However, I disagree with you on
> fundemental points. Subsystems within an application
> should remain indepedant for a easier maintanance and
> better software development process.
>  Here we have a multi tiered system whereby the
> database can't guarantee it's integrity without good
> clients. Thats not very sustainable and defeats the
> purpose of having functionality distributed among
> different components.
>  Deleting, updating and insertions should be done
> correctly on the database level. In the example i
> gave, there is no guarantee that the client will try
> to do the insertion again. What if we have more than
> one client? lets say a web browser. Should we rely on
> the user hitting reload? Now we are relying on the
> good practice of the user to keep the db integral ....
>  Fortunately Scott Helms reminded us that mysql 4 does
> include the innodb which are transactional tables. I
> will give that a shot.
>  Thanks for the comment.
> --- Dan Nelson <dnelson@stripped> wrote:
> > 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
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> -- 
> 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