Transaction Processing - Rollbacks- HELP! 1

[itflash]

I have a VFP 6.0 application.

Do transactions work for more than one table? It does not make it clear in the help.

I have a form. I Issue a start transaction.
The form writes to 7 tables (using aliases).
When the user cancels, I rollback and it works!

Is this right, do transactions work for all opened tables?
Note, I only issue one start transaction.

Also, I delete some items then save. Will rollback undelete and undo changes at the same time.

Also, what should I be doing about buffering? I have locks sorted out, so that part is not an issue.

 

[TomasDill]

Hi!

Transaction will rollback deletes.
Buffered tables require to be tableupdate()-ed before end transaction. Rollback will cancel tableupdate() only, data in the buffered alias will remain changed and fields are marked as changed when using GetFldState(). To cancel changs at all in the buffered alias you will require to use TableRevert(). This logic is made specially for VFP form for data entering and editing with navigation buttons and buttons like 'Save' and 'Cancel'. Cancel just use TableRevert(). Save starts transaction, do all tableupdates(), when some tableupdate() failed, rollback transaction and than show message to user about problem; otherwise commit transaction. User than can cancel changes or correct them and try to save again.

Note also that transaction should last as little time as possible, so put all messageboxed and dialogs outside of the transaction, as well as do not ever transaction for duration of user input. Transaction locks immediately edited record and is not unlocked until transaction rolled back or commited.

Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[itflash]

Sorry, another quick question....I cannot seem to figure out the correct way to use FoxPro ;-)

1. Form is loaded
2. Transaction Started
3. Form Fields populated.
- some fields are static and not connected.
- some fields directly update 2 tables.
4. User Amends data.
5. User Saves/Cancels (Cancel just Rollbacks).
6. Static fields are put into the relevant tables (REPLACE)
7. Transaction is Ended (with error, Rollback is issued).

NB: The records the user is editing will not be available to anybody else. But other records in the tables will be available.

Am I doing this the best way? Or will it affect performance? Or am I barking up the wrong tree?

Thanks.

 

[TomasDill]

No. Your approach will lock record for the duration of user working. Remember that transaction should last as little time as possible. Following scenario is MUCH better.

1. Form is loaded, buffering set for tables.
2. Form Fields populated.
- some fields are static and not connected.
- some fields directly update 2 tables.
3. User Amends data.
4. User Saves/Cancels (Cancel just Tablerevert()).
4.1. Transaction Started when save
4.2. tableupdate() done for all tables(with error, Rollback is issued and user is noticed about error).
Here you also can check for collisions (another user already changes these data) when using optimistic buffering (using OldVal() and CurVal() functions)
4.3. Transaction is Ended when all tableupdates are successful

What is static fields?
Remember also that with 2-d and 3-d buffering table is automatically updated as soon as record is moved, so such record source will be automatically updated when you edit multiple records at the same time or just show it in the grid. Use 4-d or 5-th buffering in such case.


Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[itflash]

Thats great. Thanks.

Putting it into steps makes it easier to understand.

By static, I mean it does not have a data source.

The form I am using has a query of data, which only one user should be editing at one time. Therefore, I do not need buffering as record locking is not an issue.

Just for the record, my father was Ukranian! Noticed your email address with the ua.

 

[TomasDill]

Well, with no buffering, to revert data, you will require to open transaction before data editing. That is a disadvantage - you open transaction OR you buffer the data, when you want to allow revert change by user. More complex and tremendous way is to store record values into memory variables r object by Scatter Memvar and use them for editing. Than save by Gather command or revert by re-reading the variables/object. NAME option in scatetr/gather give you great tool for this too. However, this is similar to the buffering, just organized by manual way...

P.S. There are many Ukrainians all over the world...
Vlad Grynchyshyn
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[itflash]

The reason I populate the form manually, is because I have to perform complex cross-referencing between different groups of data. I can only do this programatically and therefore chose this way.

I have not used grids in the form, again for the above reason.

I issue a start transaction, I think is better, because I have quite a few tables, I dont need to lock these records in these tables, and I do a lot of updates programatically.

I need to recover the whole thing as a unit.

I think this is the best way for the moment.

You answered my initial question of buffering against transactions and how they differ in their uses and what they can achieve.

The only reason I can see the benefit of the buffering over transactions is that you can quickly lock, update and release data on a form with buffering whilst transactions lock the record for the duration of the transaction.