Re: Database consistency after a power shortage

On Wed, Dec 15, 2010 at 8:12 AM, Alberto <blob2020(at)gmail(dot)com> wrote:
> My question is regarding a potential situation:
>
> I have a program that inserts values on 3 tables linked to each other. My
> program is used in a POS. In this specific case, the program has to update
> the tables "header_invoice", "detail_invoice" and
> "payments_x_header_invoice".
>
> In a normal operation, the program should insert first a registry on
> "header_invoice", then insert N registries on "detail_invoice" referencing
> the header_invoice number. After that it should insert N registries
> regarding the payments related to the header_invoice, referencing again the
> invoice.
>
> So the order goes like this:
> 1) Insert 1 new registry on "header_invoice"
> 2) Insert N registries on "detail_invoice" referencing header_invoice
> 3) Insert N registries on "payments_x_header_invoice" referencing the
> header_invoice
>
> If lets say the header_invoice registry was inserted, operation was
> committed and then a power shortage occurs and the system shuts down. In
> that case the database will never know that more registries had to be
> inserted, because that happened on the application level.
>
> Is there any way to make the 3 operations be one transaction for the
> database, so that it keeps them all consistent in case a power shortage
> occurs in the middle?

Yes, put them in a transaction.

begin;
insert into head_invoice ...
insert into detail_invocie ...
insert into payments_x_header_invoice ...
commit;

Then they either all go or none go.