Kbase P111467: 4GL client hangs when stored procedure is submitted to the MS SQL Server
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/1/2006 |
|
Status: Verified
FACT(s) (Environment):
MS SQL DataServer
Progress 9.1E
OpenEdge 10.x
SYMPTOM(s):
4GL client hangs when stored procedure is submitted to the MS SQL Server
The EXCLUSIVE-LOCK is used with the FIND 4GL statement to get the record
The stored-procedure is submitted with the same transaction block
The stored-procedure is used to update the record
Stored procedure resides in to the SQL Server Database
The same code worked in versions prior to 9.1E
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 then the FIND statement was using even though the transaction block was the same. Therefore, the track of the record lock state was lost, hence, the stored procedure was waiting until the record lock time was up. If it used the same connection as the FIND statement with EXCLUSIVE-LOCK, the same ODBC connection would keep the record lock state on track, and the SQL Server would let the stored procedure update the record.
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.