Progress
SQL-89
Guide and Reference


Transaction Processing

Because Progress has its own transaction management facilities, it is not necessary to terminate a transaction explicitly.

If you use SQL statements to update the database (INSERT, UPDATE, or DELETE), Progress/SQL performs the entire operation or nothing at all. This differs from Progress behavior. For example, if you update 50 rows with Progress statements and an error occurs on the fiftieth row, Progress only undoes that row and leaves the other 49 rows updated. If the same error occurs when you use SQL statements to update the database, Progress/SQL rolls back all 50 rows.

Because an SQL update is treated as a single transaction, Progress holds record locks for all affected rows until the end of the transaction to allow a rollback. To include an UNDO statement with your SQL INSERT, UPDATE, or DELETE, you must include an enclosing transaction to override the transaction that the SQL statement started. For example, in the following procedure the UNDO does not actually roll back any work; the UPDATE statement is treated as a transaction.

DO ON ERROR UNDO, LEAVE:
  UPDATE Customer SET Credit-Limit = 0 WHERE State = ’NH’
  UNDO, LEAVE.
END. 

If you explicitly start a transaction, however, the UPDATE statement is treated as a subtransaction and the UNDO causes the UPDATE to be undone.

DO TRANSACTION ON ERROR UNDO, LEAVE:
  UPDATE Customer SET Credit-Limit = 0 WHERE State = ’NH’
  UNDO, LEAVE.
END. 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095