Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

> ## Additional record locking details

> In 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 the 4GL, when the transaction level returns to zero. If `record` `2` is also updated by `SP`, then `record` `2` will be locked out to the 4GL transaction that’s also trying to update it. `SP` will continue to hold `record` `2` and lock out the 4GL 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.

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