Kbase P125530: What is PRGRS_NATIVE_LOCKWAIT for DataServer for MS SQL Server?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/28/2011 |
|
Status: Verified
GOAL:
What is PRGRS_NATIVE_LOCKWAIT for DataServer for MS SQL Server?
GOAL:
What switch can be turned on when debugging locking issues against MS SQL server?
GOAL:
What new switch has been added to OE 10.1B02 to assist in debugging locking issues in MS SQL server data server?
FACT(s) (Environment):
Windows NT 32 Intel/Windows 2000
OpenEdge 10.1B
OpenEdge 10.1B02 Service Pack
OpenEdge 10.1C
OpenEdge 10.0B05 Service Pack
OpenEdge 10.2A
MS SQL DataServer
FIX:
In Open Edge 10.0B05, 10.1B02 service pack, 10.1C and 10.2A, a new connection-level switch PRGRS_NATIVE_LOCKWAIT has been added for DataServer for MS SQL Server:
In earlier versions of the DataServer for MS SQL Server, when an OpenEdge client could not obtain a record lock, the server timed out immediately and control was returned to the client to handle lock retries. This is desirable behavior for clients that want immediate control over lock handling.
However, some overall lock contention can be alleviated by setting a small period of time on the server in which it tries to handle lock condition on its own before timing out and returning control to the client. This is especially useful to applications with high contention for a record resource and can provide increased overall application efficiency.
The PRGRS_NATIVE_LOCKWAIT specifies the server-based wait period in which the server will block wait for access to a locked resource before returning to a MSS DataServer client application. The default value is zero, meaning that control is returned to the client immediately upon receiving a lock condition from the server. A number of milliseconds is recommended at startup for transactional applications that anticipate a higher contention for server resources. The optimal value is application-specific. Setting the PRGRS_NATIVE_LOCKWAIT is the server-equivalent of setting the "LOCK_TIMEOUT" T-SQL value in a SQL Server connection in milliseconds.
When the PRGRS_NATIVE_LOCKWAIT setting is used to set a maximum amount of time that a connection should block waiting for a lock, the connection that has the lock and is causing the blocking problem is not affected, but the connection waiting on the block is halted. This creates server-bound latency in the DataServer application that should be considered when setting the number of milliseconds to be applied as the PRGRS_NATIVE_LOCKWAIT value. When contention for a record resource is high, server latency may have a positive overall affect on the client's application performance since the client may end up processing fewer lock time-outs in the application and have fewer round trips to the server.
Generally PRGRS_NATIVE_LOCKWAIT is set to a small number of milliseconds but may cause problems for applications that want to respond to NO-WAIT if it is too high. The maximum value you can wait on the server is 32767, and any value larger than 32767 will wait for infinite time period if the resource is locked indefinitely.
The syntax for setting the switch is as follows:
prowin32.exe mydb -Dsrv PRGRS_NATIVE_LOCKWAIT,500
The example sets PRGRS_NATIVE_LOCKWAIT to 500.
Incrementing the PRGRS_NATIVE_LOCKWAIT to some finite number that avoids 2624 is good advice because it reduces rounds trips to the server, avoids overhead on the client that generates the 2624 error, and gets a more expedient retry request back to the server. Usually a value in milliseconds will be satisfactory to avoid 2624 but the real question for the application is, "How long do you want the server to wait for a lock release before the client is informed that its waiting on a lock and is given control over whether they continue to wait or have the option to cancel and move on?". Only the application developers know the right answer. The native lockwait range is from 0 to about 32 seconds. Don't confuse it with -lkwtmo lock timeout (at 1 minute) which is how long the client will wait before it takes matters into its own hands and gives up waiting for the lock irrespective of the application code.
prowin32.exe mydb -Dsrv PRGRS_NATIVE_LOCKWAIT,-1
Setting the switch to -1 means the client will block on the server infinitely if there is a resource locking problem, this is for trouble shooting purpose only or work around problems and .should be guided by Progress technical support engineers. .