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

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Wed, 7 May 2003 22:53:12 +0100
Message-ID: <b9d551$48gi$>

"Alfredo Novoa" <> wrote in message
> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> > 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
> > 'invoices in progress'. These tables have the relevant constraints on them
> > as each line is inserted (and committed) into these tables, the
> > 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
> > 'invoice in progress' without actually doing an update? Now the
> > are available in the catalog, all we need do is pull them out and test
> > 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

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

Original text of this message