Re: FOREIGN KEY CONSTRAINTS

Tech-Archive recommends: Fix windows errors by optimizing your registry


Granny Spitz via AccessMonster.com wrote:
A relational database engine that doesn't use transaction logs *can't* be
trusted to rollback a transaction, but that doesn't make it worthless.

I beg to differ on that one. Remember, we're not talking about a user
transaction; rather, a transaction created internally by the engine for
its own use i.e. to be able to rollback a partially completed
operation. So let's be clear: are you proposing that any operation that
involves the engine creating a transaction should be avoided? (I've
correctly identified the problem - absence of transaction logs - from
the symptoms this time, correct?) I think such an approach would result
in a fairly worthless application.

I note you still haven't posted any way of reproducing this
bug/observing this feature and we only have (your) anecdotal evidence
that it exists. I'm not dismissing it as a fallacy, though; we've all
seen inexplicable corruption, I'm sure. Based on what has been
presented here, I'm classifying it as a rare, unpredictable,
unfortunate event rather than something to actively plan for.

Most
people who use Access have no idea what a transaction rollback or commit is,
but they find that their Access database application is very useful to them.

This sounds most sinister. Because we're talking about the engine's own
transactions, what you are getting at is basically a false sense of
security, yes?

Are you saying anything that can go corrupt in Jet should be avoided?

No. I'm saying that the causes of those corruptions should be avoided.

That would be Access, then <g>? Are *you* coming over all aaron, now
<vbg>?

if they were doing financial
transactions or mission-critical data operations, Jet would would be the
wrong database engine to use. They need the stability and robustness of a
client/server database engine that uses transaction logs in those situations,
where rollbacks are conducted properly to keep the data consistent.

Now this sounds more like the balanced approach I was hoping for:
cascade referential actions are fine for your average, run of the mill
Access/Jet application, however for financial transactions or
mission-critical data operations a more robust SQL DBMS should be
seriously considered.

Jamie.

--

.

Relevant Pages

  • Re: Classic Nest SP with Transaction Question
    ... enclosing transaction or should begin and commit/rollback its own ... > I echo Ron's points and also have been using an approach very similar to> Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the> ChildSP is called from the ParentSP. ... > The ParentSp now has to ROLLBACK everything up to that point. ... As Ron> mentioned, the ParentSP may have called many child SPs, and performed many> updates up to this point, and all of these need to be rolled back. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... PMFJI, but if your child proc is using an explicit tran, then it can be coded as follows: create proc dbo.ChildProc as set nocount on declare @trancount int set @trancount = @@TRANCOUNT if @trancount> 0 begin tran ChildProcTran else save tran ChildProcTran /* ... Do some stuff */ if @@ERROR> 0 begin raiserror rollback ChildProcTran return end ... commit tran go ... I believe I'm having the same issue as Chad with nested stored procedures inside a transaction. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... This will take care of what is done in the child, but what I want to do is ... rollback the entire outer transaction - the one initiated in the outer SP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)
  • Re: Classic Nest SP with Transaction Question
    ... Tom's method of using a SAVE PT instead of starting a new TRANSACTION if the ... ChildSP is called from the ParentSP. ... The ParentSp now has to ROLLBACK everything up to that point. ... it out erasing all record of the error in the Child. ...
    (microsoft.public.sqlserver.programming)