Wiki Transactions And Buffering
A key point about transactions: In VFP, during the time between BEGIN and END TRANSACTION, the table header is locked, meaning that nobody else can insert. This is a big problem scalability-wise since contention tends to increase geometrically with the number of concurrent users. So you always want your BEGIN and END TRANSACTION statements to be as proximate as possible.-- Steven Black
--------------------------------------------------------------------------------
There seems to be some confusion as to how transactions and buffering work together and affect each other, as well as when it is appropriate to use them. There are several articles on the subject, so I won’t try to get into all the details. Rather, I’ll try to explain the scope of each layer in relation to the other.
Before getting started, I should state that the most common and best practice is to wrap TABLEUPDATE()s to buffered tables inside of a transaction. This keeps the transaction short and simple, which is good because of the table header locks mentioned above. However, we can further examine the scope of transactions and alternate uses for them.
First of all, it should be understood that row/table buffering and transactions are two completely separate layers of buffering. Each layer can function on its own and independently of the other (someone please correct me here if I am wrong). Buffering updates can occur inside of a transaction, or a transaction can complete before buffers are written to disk, or the two can intertwine. All changes are not actually written to disk until both TABLEUPDATE()s and END TRANSACTION are completed.
With row/table buffering, why do we need transactions? Well, at best, table buffering can affect the changes made to a single table. In the real world, we want changes to all tables to be successfully written to disk, or for all updates to fail. We definitely do not want partial updates out there. This illustrates the core need for transactions. They allow us to treat all table updates as a single unit of work that either succeeds or fails.
To further understand the scope of a transaction is to understand what happens when a ROLLBACK command is issued. This can be summed up in one statement: A ROLLBACK command returns data to the exact state it was in when the BEGIN TRANSACTION command was issued. This applies to all tables (buffered or un-buffered), cursors, views, etc. that were affected by the transaction. (The exception is that tables that are not part of a database are not affected by transactions, which is useful for tables that keep track of primary key ID’s in a multi-user environment.) Because of this, it may be necessary to perform a TABLEREVERT() on tables when a ROLLBACK occurs. Of course, this is only when you really want to revert the changes made in the buffers. For example, if the save process fails on a data entry form, reverting the changes to all tables would require the user to re-enter the data and may not be practical. Whereas, you could simply ROLLBACK the transaction and allow the user to retry.
As an example of the above, here’s a scenario that’s used in my application:
- User enters data on form
- User presses Save button
- BEGIN TRANSACTION
- Based on data entered by user, system processes changes to several tables
- Perform TABLEUPDATE()s
- If any TABLEUPDATE() fails, ROLLBACK
- If all TABLEUPDATE()s succeed, END TRANSACTION
Notice that changes made to the tables when the data is processed are inside of the transaction. If an error occurs or a TABLEUPDATE() fails, these changes are rolled back. But the data entered by the user is still intact, even though it is still in a buffered state. Hence, you can see the extra layer of buffering that transactions afford. At this point, the user can make changes to the data or simply try to save again. TABLEREVERT()s are handled by a separate Cancel button. Admittedly, this makes for a long transaction, but I think it illustrates the flexibility you have with transactions. -- Joel Leach
--------------------------------------------------------------------------------
There is also a section explaining this in Hack Fox: "Begin Transaction, End Transaction, Rollback" -- Alex Feldstein
--------------------------------------------------------------------------------
* Error handling: grab the message and set a flag.
on error lErrHlr()
public glErr, gcErrMsg
gcErrMsg = ""
glErr = .f.
* Set up a database and tables
create database Foo
create table Customer ;
( pkCus c(10) primary key, ;
cName c(20), ;
yCreditLimit y, ;
yDue y check yDue <= yCreditLimit )
create table Invoice ;
( pkInv c(3) primary key, ;
fkInvCus c(3) references Customer, ;
yAmt y, ;
yBalance y default yAmt check yBalance >=0 )
create table Receipts ;
( pkPmt c(3) primary key, ;
fkPmtInv c(3) references Invoice, ;
yAmt y )
Insert into Customer ( pkCus, cName, yCreditLimit ) values ( "ABC", "A Big Company", 500.00 )
lInv( "001", "ABC", 200.00 ) && Invoice #001 to "ABC" for $200
lInv( "002", "ABC", 200.00 ) && Invoice #002 to "ABC" for $200
lInv( "003", "ABC", 200.00 ) && Invoice #003 to "ABC" for $200 - fails, because they are over the limit
lPay( "123", "001", 150.00 ) && Payment check #123 to pay $150 of Inv#001
lPay( "124", "001", 100.00 ) && Payment check #124 to pay $100 of Inv#001 - fails, because the balance of inv#100 is only $50
lPay( "124", "002", 100.00 ) && Payment check #124 to pay $100 of Inv#002
lInv( "003", "ABC", 200.00 ) && Invoice #003 to "ABC" for $200
function lInv( tkInv, tkCus, tyAmt )
? "Adding Customer:", tkCus, "Invoice: ", tkInv, "Amount", tyAmt
begin transaction
* Add a record the invoice
insert into Invoice ( pkInv, fkInvCus, yAmt ) values ( tkInv, tkCus, tyAmt )
* Update the client's Total Due
update Customer set yDue = yDue + tyAmt where pkCus = tkCus
if glErr
?? " -- Aborted: ", gcErrMsg
rollback
glErr = .f.
else
end transaction
endif
return
function lPay( tkChk, tkInv, tyAmt )
? "Adding Payment Check:", tkChk, "Invoice:", tkInv, "Amount of check:", tyAmt
local lkCus(1)
select fkInvCus from Invoice where pkInv = tkInv into array lkCus
begin transaction
* Add a record of the Payment
insert into Receipts ( pkPmt, fkPmtInv, yAmt ) values ( tkChk, tkInv, tyAmt )
* Update the Invoice Balance
update Invoice set yBalance = yBalance - tyAmt where pkInv = tkInv
* Update the client's Total Due
update Customer set yDue = yDue - tyAmt where pkCus = lkCus
if glErr
?? " -- Aborted: ", gcErrMsg
rollback
glErr = .f.
else
end transaction
endif
function lErrHlr()
local laErr(1)
aError( laErr )
gcErrMsg = laErr(2)
glErr = .t.
messagebox( gcErrMsg )
return
--------------------------------------------------------------------------------
Here's an example of using transactions on optimistically buffered tables.
BEGIN TRANSACTION
llSuc = TABLEUPDATE(.T.,.T.,'Ext2') .AND. ;
TABLEUPDATE(.T.,.T.,'StaEqu') .AND. ;
TABLEUPDATE(.T.,.T.,'KeyData')
IF llSuc
END TRANSACTION
ELSE
ROLLBACK
MESSAGEBOX( 'One or more of the tables failed to update. Cancelling transaction.', 0, NEC_LOC )
TABLEREVERT(.T.,'Ext2')
TABLEREVERT(.T.,'StaEqu')
TABLEREVERT(.T.,'KeyData')
ENDIF
--------------------------------------------------------------------------------
clear
* Clean up previous runs
close database all
on error ? mess()
rollback
rollback
drop table Foo
on error
* Set up a database and tables
create database Boo
create table Foo (iFid1 i primary key)
use Foo shared
* add and backout some records
lTrn()
* Same thing on a free table
create table Bar free (iFid1 i)
lTrn()
return
function lTrn()
set multilocks on
CursorSetProp( "Buffering", 5 )
begin transaction
insert into Foo values (1)
append blank
? [alias(), reccount():], alias(), reccount()
replace iFid1 with 2
? [alias(), reccount():], alias(), reccount()
rollback
? [alias(), reccount():], alias(), reccount()
tablerevert()
? [alias(), reccount():], alias(), reccount()
?
return