Re: "Transactions are bad, real bad" - discuss

"Alfredo Novoa" <alfredo_at_ncs.es> wrote in message news:e4330f45.0305031034.6d8a568d_at_posting.google.com...
> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
news:<b8p42r$1ltu$1_at_gazette.almaden.ibm.com>...
>
> > I want to offer you some alternate solutions.
> >
> > A) Invoice lines are persistent
> >
> > Each invoice line is stored in the database in a set of tables set aside
for
> > 'invoices in progress'. These tables have the relevant constraints on them
and
> > as each line is inserted (and committed) into these tables, the
constraints
> > are checked. Once an invoice is complete - defined by say a view over the
> > 'invoices in progress' it can be moved into the 'main' database tables.
>
> I like it very much!
>
> I would implement it adding a "Completed" boolean attribute to the
> invoice header.

That is one way. However it generally makes defining you constraints a little more difficult. Also a bigger argument against useing the same set of tables for 'incomplete' things is that often the incompletness means that not all attributes required in a tuple for a completed thing are known. Keeping things seperate save us from defining special values/union domians to cover the 'missing information'.

> I could declare a "CompletedInvoices" view, and then I would not need
> to move anything.
>
> It is very good for me because the users will see the article stock
> variations instantly. It is important when you work with articles with
> serial numbers.

I agree that the pros/cons are business specific.

> > Frankly this is the best design. It avoids issues such as not defining the
> > constraint
> > 'Every invoice must have at least one line'
>
> It should be:
>
> 'Every completed invoice must have at least one line'
>
> > B) Invoice lines are not persistent
> >
> > As above, but the tables set aside for 'invoices in progress' are local,
> > in-memory tables on the client. Again, the constraints on these will be
> > similar, but not identical to those on 'completed invoices'.
>
> I have considered it, but I would need a local DBMS on the client,

Oh for such things.

> and
> several constraint violations would not be detected until the invoice
> completion.

I see little reason why a local (non-persistent) DBMS could not declare constraints that reference data in a server DBMS.

> > The problem we have is: how do we get to test the DBMS constraints against
our
> > 'invoice in progress' without actually doing an update? Now the
constraints
> > are available in the catalog, all we need do is pull them out and test
them
> > against our proposed update.
>
> But the constraint could be violated only after the test.

You mean after the update? No, the point is that we make our update against a *logical* copy of the database and test the resulting database value against the defined database constraints. Physically you could, if you wanted, just copy what SQL DBMSes do - an uncommited update that gets rolled back

> IMHO it looks a lot like a distributed database update with defered
> synchronization, and frankly I don't like it.

I don't see the analogy.

> Where would we 'store' the proposed updates?

In local relvars (tables)

> Proposed updates looks very similar to uncommited updates.

Indeed, because that is the functionality that I am trying to emulate, but in a logically consistent way that also IMO fits business requirements better.

>
> Thanks a lot for your suggestions. I think I will use the A)

No worries.
I agree that A) is the best in a SQL environment

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Wed May 07 2003 - 23:53:12 CEST