Re: Handling transactions?

["Björn Lundin" <b.f.lundin@gmail.com>]

On 2015-07-27 14:21, EGarrulo wrote:
> What is the Ada idiom to handle transactions in procedures?  I mean, let's suppose we perform the following steps in a procedure:
> 
>    Obj_1.A ();
>    Obj_2.B ();
>    Obj_3.C ();
>    Obj_4.D ();
> 
> If we don't reach the last line because of an exception, we must undo the effects of any previous actions.  My guess is that you would use a controlled type to embody a transaction, like this pseudo-code:
> 
>    Transaction.Start ();
>    Obj_1.A ();
>    Transaction.Add_Rollback (Rollback_Obj_1_A);
>    Obj_2.B ();
>    Transaction.Add_Rollback (Rollback_Obj_2_A);
>    Obj_3.C ();
>    Transaction.Add_Rollback (Rollback_Obj_3_A);
>    Obj_4.D ();
>    Transaction.Commit (); -- Don't run any registered rollback.
> 
> Is this the way it is done, or is it any different?   Thank you.
> 

You do not mention what kind of transactions you mean.
At work, where we do transactions towards databases
in a multi-process environment,
we do like this:

loop
  begin
    Transaction.Start ();
    Obj_1.Read;
    Obj_2.Read;
    Obj_3.Read;
    Obj_4.Read;

    Obj_1.A ();
    Obj_2.B ();
    Obj_3.C ();
    Obj_4.D ();
    Transaction.Commit ();
    exit;
  exception
    when Transaction_Conflict =>
     Transaction.Rollback;
  end;
end loop;


The idea is that you want to read values within the
transaction, so you can re-read new values,
if another process committed rows that affect the
objects.
You then want to roll EVERYTHING back, and start over,
perhaps with another result-set.
The reads may give different results, depending on
what the external source did to cause the transaction_conflict.

Now, exactly what raises Transaction_Conflict is up to you.
We run db in read_committed mode, and each table has two columns,
latest updater and latest updated timestamp.
Each sql that modifies a row also HAVE to change these
columns with its process-name and timestamp.
This we ensure with auto-generated code

We include the fields in the where-clause of the statement

update T set A=1,B=2
where some-logical-condition
and Last_Updater=:Last_Updater
and Lastest_Updated=:Latest_Updated

These two fields gets their value when reading the rows -     Obj_1.Read;

Then the update really does
Sql.Execute(Statement, Rows_Affected);

if Rows_Affected = 0 then
  raise Transaction_Conflict;
end if;


The two fields may be replaced with a row-version field of course.
Some put triggers on each table to auto-increment this row-version,
so user-code does not need to care.

But the idea is the same:

loop
  BEGIN
  read
  update
  if no row affected, raise exception
  COMMIT
  exit loop
  catch exception
  ROLLBACK
end loop


This way, the row is only locked when actual writing to it is done


--
Björn