Kbase P113009: 4GL "do transaction ... undo" does not undo transactions initiated by stored procedures in MS SQL Se
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
29/10/2008 |
|
Status: Verified
SYMPTOM(s):
4GL "do transaction ... undo" does undo transactions initiated by stored procedures in Progress 9.1x prior to 9.1E and 10.0x
FACT(s) (Environment):
MS SQL DataServer
Progress 9.1E
OpenEdge 10.x
The stored-procedure is submitted with the same transaction block
The stored-procedure is used to update the record
Stored procedure resides in the SQL Server Database
CAUSE:
Starting Progress 9.1E and OpenEdge 10, the SQL Server DataServer implemented firehouse cursor to improve performance for the read-only query. The firehouse cursor requires separate ODBC connection pool for queries. However, when query is finished, the ODBC connection can be reused from the connection pool.
With this new implementation, the submission of stored-procedure used a different connection from the pool than the statement querying the record. Therefore, the track of the record lock state was lost.
FIX:
Use the *-Dsrv PRGRS_SP_CACHE,0* SQL Server DataServer switch.
By default, the switch is not set. If you set it to 0, the logic will cause the procedure pool to be ignored such that the procedures are returned to the firehouse connection pool and will isolate procedures to their own pool but then will run out of connections at "zero". Therefore, the procedure would revert back to the same connection (and transaction) as the 4GL.