Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread What happens to a transaction if there is a power outage
Thu, Sep 24 2015 3:58 AMPermanent Link

Trevor Davis

Davis Software

Hi,

With DBISAM, client/server, v4.26 build 3
during a transaction prior to rollback or commit
if there is a power outage
will the updates rollback or commit?

What if the power outage occurred during the commit? Will some updates be
applied and some not? Are they applied in the sequence they occurred?


I have a client who has all the processing completed, table updates done, up
to a point midway in a transaction, and no further processing done.

I don't know if there was a power outage, but am wondering if that could cause
this scenario. Or Windows crashing, network cable unplugged, Ctrl Alt Del,
etc.

Thanks.

--
Trevor Davis
Davis Software
www.dbsonline.com


Thu, Sep 24 2015 4:57 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Trevor


As I understand it inside a transaction the posts are written to memory, the commit flushes the changes down to the tables on disk. Up to the point of the commit if there's a power out the whole lot is lost ie essentially the same as a rollback.

If the power out happens during the commit ie when data is being written to disk a) you've been very unlucky in managing to hit the window and b) I'd expect some or all of the tables to be in a bit of a state (depending on just what had been / was being written at the time the power went) and c) I'd expect the overall consistency of the data to be compromised

I know transactions are an all or nothing thing but if the power goes when the disk itself is being written to that's what I'd expect. However, I could be totally wrong.

Roy Lambert
Thu, Sep 24 2015 5:48 AMPermanent Link

Trevor Davis

Davis Software

Thanks Roy,

That's what I would expect too for while the changes are in memory, and also
what I would 'kind of' expect during the commit. Except I think I may have
read some databases actually can handle a power outage during a commit... with
some '2 stage commit'. Maybe Oracle or an IBM feature on their minis.

This happened on just one invoice in 9 months while it was updating various
GL, inventory and AR tables.

--
Trevor Davis
Davis Software
www.dbsonline.com


"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:1117F88B-79DE-4F47-9256-2A8AFD84F751@news.elevatesoft.com...
> Trevor
>
>
> As I understand it inside a transaction the posts are written to memory, the
> commit flushes the changes down to the tables on disk. Up to the point of
> the commit if there's a power out the whole lot is lost ie essentially the
> same as a rollback.
>
> If the power out happens during the commit ie when data is being written to
> disk a) you've been very unlucky in managing to hit the window and b) I'd
> expect some or all of the tables to be in a bit of a state (depending on
> just what had been / was being written at the time the power went) and c)
> I'd expect the overall consistency of the data to be compromised
>
> I know transactions are an all or nothing thing but if the power goes when
> the disk itself is being written to that's what I'd expect. However, I could
> be totally wrong.
>
> Roy Lambert
>

Thu, Sep 24 2015 8:16 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Trevor

<<That's what I would expect too for while the changes are in memory, and also
what I would 'kind of' expect during the commit. Except I think I may have
read some databases actually can handle a power outage during a commit... with
some '2 stage commit'. Maybe Oracle or an IBM feature on their minis.>>

I think Oracle uses some sort of journalling and the journals do hit the hard drive or at least some form of non-volatile memory. I don't remember to much about S36, S38 or AS400 - to long ago

<<This happened on just one invoice in 9 months while it was updating various
GL, inventory and AR tables.>>


That could have been due to a stray beta particle Smiley


Roy Lambert


ps Didn't notice the web link last time


So you're BS1 - I feel proud to have answered a question from you Smiley

On the other hand - anyone who can stay awake long enough to write an accounts package - well I mean


Thu, Sep 24 2015 9:04 AMPermanent Link

Trevor Davis

Davis Software

Roy,

I googled it and yes it seems to be based on a journaling feature for
recovery. Since I've never seen it in the DBISAM docs I'd assume DBISAM has no
such feature.


> I don't remember to much about S36, S38 or AS400 - to long ago

I never encountered it, but I'm pretty sure I heard about it from a colleague
in the IBM world. Maybe it's from the big iron machines.


> That could have been due to a stray beta particle Smiley

LOL. Or a quantum physics effect... the program was on another computer at the
time! Smile


--
Trevor Davis
Davis Software
www.dbsonline.com



"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:FABB5F95-D920-439F-BA6D-EBE69720FD46@news.elevatesoft.com...
> Trevor
>
> <<That's what I would expect too for while the changes are in memory, and
> also
> what I would 'kind of' expect during the commit. Except I think I may have
> read some databases actually can handle a power outage during a commit...
> with
> some '2 stage commit'. Maybe Oracle or an IBM feature on their minis.>>
>
> I think Oracle uses some sort of journalling and the journals do hit the
> hard drive or at least some form of non-volatile memory. I don't remember to
> much about S36, S38 or AS400 - to long ago
>
> <<This happened on just one invoice in 9 months while it was updating
> various
> GL, inventory and AR tables.>>
>
>
> That could have been due to a stray beta particle Smiley
>
>
> Roy Lambert
>
>
> ps Didn't notice the web link last time
>
>
> So you're BS1 - I feel proud to have answered a question from you Smiley
>
> On the other hand - anyone who can stay awake long enough to write an
> accounts package - well I mean
>
>
>

Thu, Sep 24 2015 9:14 AMPermanent Link

Trevor Davis

Davis Software

Hey Roy,

Just noticed this after replying...


> ps Didn't notice the web link last time
>
> So you're BS1 - I feel proud to have answered a question from you Smiley

<Blush> Lost for words...


> On the other hand - anyone who can stay awake long enough to write an
> accounts package - well I mean

Staying awake is no problem. Waking up is the problem! Smile

Trevor

Thu, Sep 24 2015 9:17 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Trevor

>I googled it and yes it seems to be based on a journaling feature for
>recovery. Since I've never seen it in the DBISAM docs I'd assume DBISAM has no
>such feature.

Neither DBISAM nor ElevateDB has journalling Frown

>> That could have been due to a stray beta particle Smiley
>
>LOL. Or a quantum physics effect... the program was on another computer at the
>time! Smile

Strange but true - one early multiuser supermicro claimed this as the reason for infrequent HDD (really big 100 meg ones) problems

Roy Lambert
Thu, Sep 24 2015 11:55 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Trevor,

Roy pretty much handled the other stuff, and yes, write-ahead logs are what other database servers use to ensure that writes are actually on disk before a commit is considered "complete".  However, it is still possible for a hard drive to fail or the OS to do something funky, so things like making sure that data is flushed to disk and RAID are also required.

<< I have a client who has all the processing completed, table updates done, up to a point midway in a transaction, and no further processing done. >>

That would be very unusual, since commits are normally very fast.  Did you actually verify the database tables to see if there were any issues ?  At the very least, I would expect the indexes to be out of synch with the records in such a situation.

Tim Young
Elevate Software
www.elevatesoft.com
Thu, Sep 24 2015 11:56 PMPermanent Link

Trevor Davis

Davis Software

Thanks Tim,

The tables passed the verify test, however the problem happened in June and my
copy of the database is from September, so it's possible they ran a repair
option in the program before sending me a copy. But I wasn't told of any
problems so I suspect they didn't run the repair option.

I verified that the rollback works by raising an exception within the
transaction and also disabling a table 'edit' prior to a 'post'.

I could write to a log file for diagnostic purposes, but I might have to wait
another 9 months or more for it to happen again.

Trevor




"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:65B91D6B-8BE8-4A3C-AD46-5F29EE858492@news.elevatesoft.com...
> Trevor,
>
> Roy pretty much handled the other stuff, and yes, write-ahead logs are what
> other database servers use to ensure that writes are actually on disk before
> a commit is considered "complete".  However, it is still possible for a hard
> drive to fail or the OS to do something funky, so things like making sure
> that data is flushed to disk and RAID are also required.
>
> << I have a client who has all the processing completed, table updates done,
> up to a point midway in a transaction, and no further processing done. >>
>
> That would be very unusual, since commits are normally very fast.  Did you
> actually verify the database tables to see if there were any issues ?  At
> the very least, I would expect the indexes to be out of synch with the
> records in such a situation.
>
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Fri, Sep 25 2015 12:45 AMPermanent Link

Trevor Davis

Davis Software

> So you're BS1 - I feel proud to have answered a question from you Smiley
> On the other hand - anyone who can stay awake long enough to write an
> accounts package - well I mean

Roy I missed this last night: accounting = boring
It went over my head, up all night programming. Duh Smile

Trevor

Image