In MS SQL Server, if transactions are scoped within other transactions, nested or embedded, all commit/rollback operations on the nested transactions are ignored by SQL server. Not until the transaction nesting levels are reduced to zero are all the nested levels actually committed or rolled back. This is a restriction of the data source with ramifications illustrated by the following code:
DO TRANSACTION:
RUN STORED-PROC SP.
CLOSE STORED-PROC SP.
UPDATE record 2.
ASSIGN fld2 = X.
END.
If the stored procedure SP has a commit or rollback statement within its TSQL, the commit or rollback isn't actually executed until the END statement in ABL, when the transaction level returns to zero. If record 2 is also updated by SP, then record 2 will be locked out to an ABL transaction that's also trying to update it. SP will continue to hold record 2 and lock out an ABL update even if SP explicitly commits the update inside the stored procedure. To avoid this problem, it is necessary to recode the example as follows:
DO TRANSACTION:
RUN STORED-PROC SP.
CLOSE STORED-PROC SP.
END.
DO TRANSACTION:
UPDATE Record 2.
ASSIGN fld2 = X.
END.
