Rollback and $undowork

Doug Easterbrook doug at artsman.com
Wed Jul 13 13:42:49 UTC 2022


I’m not sure if you are asking a conceptual question here, or not.

the purpose of a transaction boundary ($begin through $commit) is to assume things are going to work as you do sql updates.

and if you find failure for any reason (somebody else locked a record, you can’t read something you expected to exist, some referential integrity failure occurred, you hit deadlock, a database connection failure, a stored procedure error, malformed sql, anything really), then you rollback your changes.


what do you know when you rollback changes — the database is in exatly the same state as it was before your started.    None of the updates actually occurred to the data.


so, can you do an undowork in omnis .  By that I assume you mean, can you tell all your table classes within the $begin and $comit boundary  to put all your data back to square one —   no.  you cannot.

you can only go back to square one by rereading all the data and starting over.


pseudo code example:

read 4 tables: customer,  inventory, orders, and payments
sell a widget which will need to update
— customer balance
— decrease inventory
— make an order
— add a payment


$begin()

customer table:

add to balance
$dowork.     <— omnis has completed the changes in this table, database has the change


Inventory table

Decrease count of inventory for widget and adjust inventory balance
$dowork.     <— omnis has completed the changes in this table, database has the change


Order table:
insert an order record
update balance
associate with customer
$dowork.     <— omnis has completed the changes in this table, database has the change


make a link record between the order and the inventory table to track what was sold as a detail item

insert order detail link record

BOOM — lets say we have a failure here because of a programming error and the serial number field was not set up correctly, so the record will not insert (I’m using this as an example, since you asked about this with in the past week).


$dowork.    <———  WILL FAIL for this table


your reaction to the $dowork failing is to do a $rollback.


at this point, the database is back in its original state, as if nothing ever happened.



what about omnis tables?


1) well, they all have the new values.
2) you can’t tell omnis to go back to original values before you started the transaction.
3) so you start over and

Read all 4 tables like you did at the start of the process  to get the data back to original within your omnis lists.





——————————

As for transactions, we use them a lot of the time, especially when updating two or more tables that must be consistent.   the best example of why you’d do that is a banking one

you deposit money
the bank updates their account with a debit
the bank updates your account with a credit so that things balance


if that was not in a transaction, and the process failed after the bank said they had more money, but before they said it was in your account, you’d end up feeding the bank money, and you’d have none.      it must be done in a transaction for banking system integrity.








Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Jul 12, 2022, at 3:26 PM, Das Goravani <goravanis at gmail.com> wrote:
> 
> 
> 
> Dear List,
> 
> I can’t figure this out and nowhere is this covered…
> 
> If you have a SQL Transaction going, and you are some ways through it, and you have done a $dowork command on a certain list, and you have to rollback, do you ALSO issue $undowork?
> 
> Said another way, if you $dowork in a transaction, and you rollback, do you need to do $undowork?
> 
> Thanks,
> 
> Das Goravani
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com



More information about the omnisdev-en mailing list