Hi Kevin,

>>In this context, transactions are merely a tool in achieving an easy rollback.
>
>When I taught SQL (and I still train new developers from time to time), I use this as the starting point.
>
>Maybe a tad oversimplified, I'll walk through this demo...."I have to manually perform three updates....I do the first, I do the second, and then...OH ****.....I didn't do the first one right", and then show that ROLLBACK is their friend. (Yes, I know you guys know all of this, but I thought your final sentence was a perfect segue)

>Yes, I could have backed up all three tables. But I show it as a way to safely verify and then 'post' all the changes, or hit the reset button and say, "let's start again"

>Yes, that is a simple example. Transactions are for more than just that....the isolation levels in SQL give us some great scenarios.....but that's where I start.

Since mike does not seem to do (much) SQL server, I deliberately took transactions in the context of VFP.
And this is where the fun begins. Depending on the scenario, Its significantly more complicated than in pure SQL server. You really need to know what you are doing.

But coming back to the point, transactions only prevent data corruption due to problems in saving records to the the table where the database is left in an inconsistent state. We both know that the topic of data corruption goes far beyond this problem.

A lot of VFP programmers, not being comfortable with SQL, do not realize you'll have to start transactions both in VFP and SQL server in order to get it right. Unlike SQL Server, you'll have to check whether CUD operations are successful as indicated by the return value of the TABLEUPDATE() command. If you are not using TABLEUPDATE() but relying on implicit updates by the movement of the record pointer (row buffering), you are in for trouble as you'll have additional error handling to do.

Personally, I'm using a class like below where the synchronization between SQL and VFP transactions will be handled for you.

The problem with VFP transactions is that it does not adhere to the ACID properties, so in case of a crashing application (which pre VFP9SP2 was not uncommon due to an issue in the runtimes) while your application was in process of committing the transaction your data still could be in an inconsistent state (D(urability) in ACID).

More worrying, as indicated above is that if you don't catch a failed tableupdate() (or implicit update) and commit the transaction in stead of rollback (VFP) you still end up with data corruption which you tried to solve (I've been there). To illustrated in SQL Server I can execute this

BEGIN TRANSACTION
UPDATE Table1 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE Table2 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE Table3 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
COMMIT TRAN

And it is nicely wrapped in a transaction and it either all succeeds or fails.

In pure VFP it is a bit more complicated

BEGIN TRANSACTION
UPDATE View1 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE View2 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE View3 SET MyField = 'MyValue' WHERE AnotherField = Somevalue

IF TABLEUPDATE(2, .T., "View1") AND TABLEUPDATE(2, .T., "View1") AND TABLEUPDATE(3, .T., "View1")
     END TRANSACTION
ELSE
     ROLLBACK
ENDIF

When adding SQL server transactions you have to deal with the VFP transaction and SQL transaction simultaneously. And that is a bit tricky. You could end up having the VFP transaction rolled back and the SQL server transaction committed or visa versa.

Therefore I use the class below which hides the complexity.

LOCAL oTx

oTx = CREATEOBJECT("Transactionhandler")
oTx.Start()

UPDATE View1 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE View2 SET MyField = 'MyValue' WHERE AnotherField = Somevalue
UPDATE View3 SET MyField = 'MyValue' WHERE AnotherField = Somevalue

IF  TABLEUPDATE(2, .T., "View1") AND TABLEUPDATE(2, .T., "View2") AND TABLEUPDATE(3, .T., "View3") AND oTx.Commit() 
    ** Success
ELSE
    ** Failed
    oTx.Rollback()
ENDIF

It is not that complicated, but it very easy to make mistakes. For example handling errors that might occur during the transaction) in which the transaction might erroneously be committed rather than rolled back.

Another complication with VFP code, is that in many implementations there might be some GUI involved, like a message or warning. This is killing when used in a transaction as the rows/pages/table are locked and will remain so until the transaction is either completed or rolled back, creating a heavy toll on concurrency.

And there are other scenarios to consider here. For example that the changes to multiple tables occur before the transaction started. When the transaction fails, the changes are still in the views/tables and you need to use Tablerevert() to go back to the initial state before the change.

Nothing is really difficult, but implementing transactions correctly, especially in combination with SQL server, is not a task to be underestimated.

DEFINE CLASS TransactionHandler AS CUSTOM
	*[2008/05/28 15:16:53] WM. A class that handles transactions.
	*[2010/07/29 10:17:20] Major revision

	DIMENSION Errors[1,7]
	DATASESSION = 1
	DBConnection = NULL

	FUNCTION INIT(oDbConn)

		DO CASE
		CASE TYPE("oDbConn.DataSourceType") = "C"
			** UDF connection
			THIS.DBConnection = oDbConn

		CASE TYPE("oDbConnection.DataSourceType") = "C"
			** Application wide connection
			THIS.DBConnection = oDBConnection

		OTHERWISE
			** Native connection only
			THIS.DBConnection = CREATEOBJECT("DBConnection")
			THIS.DBConnection.CONNECT()
		ENDCASE
	ENDFUNC

	*-

	FUNCTION START()
		=ExplicitPurgeMemory() && SYS(1104) to clean buffer to prevent C5
		STORE .F. TO THIS.Errors

		BEGIN TRANSACTION
		IF THIS.DBConnection.DATASOURCETYPE = "ODBC"
			SQLSETPROP(THIS.DBConnection.Sqlhandle,"transActions",2) && Manual Transaction
		ENDIF
	ENDFUNC

	*-

	FUNCTION Commit()
		LOCAL lRet
		lRet = .T.

		DO CASE
		CASE THIS.DBConnection.DATASOURCETYPE = "ODBC" AND SQLCOMMIT(THIS.DBConnection.Sqlhandle) > 0
			** Succesfull
			END TRANSACTION
			SQLSETPROP(THIS.DBConnection.Sqlhandle,"transActions",1) && Automatic Transaction

		CASE THIS.DBConnection.DATASOURCETYPE = "ODBC"
			** Remote save not succesfull
			TRY
				AERROR(THIS.Errors)
			CATCH
			ENDTRY
			THIS.ROLLBACK()
			lRet = .F.

		OTHERWISE
			** VFP tables
			END TRANSACTION
			FLUSH
		ENDCASE
		RETURN lRet
	ENDFUNC

	*-

	FUNCTION ROLLBACK
		DO CASE
		CASE THIS.DBConnection.DATASOURCETYPE = "ODBC"
			SQLROLLBACK(THIS.DBConnection.Sqlhandle)
			IF TXNLEVEL() > 0
				ROLLBACK
			ENDIF
			SQLSETPROP(THIS.DBConnection.Sqlhandle,"transActions",1) && Automatic Transaction

		OTHERWISE
			IF TXNLEVEL() > 0
				ROLLBACK
			ENDIF
		ENDCASE
	ENDFUNC

	*-

	FUNCTION DESTROY()

		THIS.ROLLBACK()
		THIS.DBConnection = NULL
		DODEFAULT()
	ENDFUNC
ENDDEFINE