Re: db constraints evaluation question

joe_celko_at_my-deja.com wrote:
>
> >> If I have a set of constraints in my (e.g. object-oriented) db and a
> transaction which performs several update, delete etc. operations, how
> do I determine which deferred constraint checks are needed at the end
> of the transaction - to find out if my data still is in onsistent/valid
> state or not. <<
>
> The SQL-92 Standard says that ALL constraints -- defered or not -- must
> be met when the transaction is finished. Otherwise, you get a ROLLBACK
> WORK and a set of error messages which tell you which constraints were
> violated. Deferred constraints are turned off only until you turn them
> back on, or try to COMMIT WORK.
>
> --CELKO--
Joe is exactly correct. Usually, the best method in an OO system is for the property setters to validate object state and integrity as you go. This will allow you to throw errors when a constraint is violated at the time the violation occurs (fail fast). Then, you need to test all applicable constraints before any objects are stored/committed to the database. In our Manufacturing Execution System (MES) FACTORYworks (#1 in Semiconductors - adv. :-) we do this by calling postBeginTxnCheck methods on all objects being stored so that relevant constraints to each object/class are validated. This occurs before any data is actually stored. Any failure will cause the overall transaction (included distributed txns) to be rolled back.

In any case, large scale system will usually perform best with the least database I/O performed. This is why you want to fail as soon as a constraint is detected. If you do that before you update any data, then you take a BIG load off the database and I/O systems.

For whatever its worth, our MES is a fully OO system which uses a relational DB (Oracle) as an object repository, so minimizing database I/O is especially important for the scale of systems we support.

Bill Boyle
woboyle_at_ieee.org Received on Wed Apr 12 2000 - 00:00:00 CEST