Kbase P133919: New -Dsrv switch for NO-WAIT functionality - MS SQL Server DataServer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
05/11/2009 |
|
Status: Verified
GOAL:
New -Dsrv switch for NO-WAIT functionality
GOAL:
What is the new -Dsrv switch for NO-WAIT?
GOAL:
What is PRGRS_NOWAIT_OVERRIDE switch?
FACT(s) (Environment):
Windows
MS SQL DataServer
OpenEdge 10.1C02 Service Pack
OpenEdge 10.1B03 Service Pack
FIX:
A new -Dsrv switch for NO-WAIT functionality has been introduced in 10.1B03 Hotfix, 10.1C01 hot fix, 10.1C02 and 10.2A.
New switch details are explained below :
A new connection-level switch PRGRS_NOWAIT_OVERRIDE has been added for DataServer for MS SQL Server:
Currently the PRGRS_NATIVE_LOCKWAIT setting permits an application to set the maximum time a statement waits on the MS SQL server for a blocked resource. Note that the ABL client is also blocked while the PRGRS_NATIVE_LOCKWAIT period is active on the server. The new switch may be useful to those who want the NO-WAIT instruction on an ABL database request to override the PRGRS_NATIVE_LOCKWAIT period in which the application is blocked on the server.
This new PRGRS_NOWAIT_OVERRIDE connection-level switch will resolve the scenario for NO-WAIT queries. The valid values for PRGRS_NOWAIT_OVERRIDE switch are 0 and 1. By default PRGRS_NOWAIT_OVERRIDE will be disabled (i.e. 0). Setting of this new switch alone will be ignored by the DataServer and would otherwise have no affect on the NO-WAIT conditions in your application because the default PRGRS_NATIVE_LOCKWAIT period is zero which means the server returns immediately to the client when it encounters a locked resource and does not block at all. The override setting is only meaningful when specified along side the PRGRS_NATIVE_LOCKWAIT connect option. Note that the PRGRS_NOWAIT_OVERRIDE switch only applies to server side blocking associated with the PRGRS_NATIVE_LOCKWAIT. Client side resource blocking, as timed by the -lkwtmo switch, is not affected by PRGRS_NOWAIT_OVERRIDE switch and already designed to unblock in response to a NO-WAIT ABL instruction.
In the following client startup example the client lock wait time out is set to 180 seconds, the native lockwait time out is set to 500 milliseconds and the PRGRS_NOWAIT_OVERRIDE switch is set on:
prowin32.exe mydb -lkwtmo 180 -Dsrv PRGRS_NATIVE_LOCKWAIT,500,PRGRS_NOWAIT_OVERRIDE,1
If the "customer" resource is blocked on the server using the following ABL statement and continues to be blocked indefinitely
FIND FIRST customer NO-WAIT.
Then, the behavior as seen from the ABL client is the following:
1. Because of the NO-WAIT ABL condition and the PRGRS_NOWAIT_OVERRIDE,1 setting, control is returned immediately to the client despite the non-zero value for PRGRS_NATIVE_LOCKWAIT.
NOTE: Had the PRGRS_NOWAIT_OVERRIDE switch not been set, the server would have blocked a maximum of 500 milliseconds assuming the resource was not otherwise made available prior to the server time out period.
2. The client's lock wait time out period of 180 seconds begins when the server returns to the client. In this case, the NO-WAIT instruction in the ABL and the switching on of the PRGRS_NOWAIT_OVERRIDE option means that the client's lock wait time out period begins immediately.
NOTE: When the PRGRS_NOWAIT_OVERRIDE is off, the initial PRGRS_NATIVE_LOCKWAIT period (spent blocked on the server) is NOT accumulated by the client side '-lkwtmo" period because the '-lkwtmo' period doesn't begin until the client has control. This is not a concern when the PRGRS_NOWAIT_OVERRIDE switch is on and the ABL specifies NO-WAIT because no additional time is accumulated waiting on the server, i.e., the PRGRS_NATIVE_LOCKWAIT period is overridden by the NO-WAIT action.
3. The ABL application takes immediate control and receives the "LOCKED" ABL condition indicating that the resource is unavailable.
NOTE: If the ABL had not specified NO-WAIT and the resource continued to be unavailable after the PRGRS_NATIVE_LOCKWAIT period, the the client side -lkwtmo period would now commence and would wait 180 seconds, repeatedly cycling to the server in an effort to obtain the locked resource.
Additional Notes (not related to the test case):
1 Had the NO-WAIT ABL condition not been specified. and had the 180 second wait on the client expired without obtaining the resource, the ABL client would then fail with a "lock wait timeout exceeded" error message.
2. Had the NO-WAIT ABL condition not been specified, and the PRGRS_NOWAIT_OVERRIDE switch were set to off, and the resource remained unavailable, the start of the client -lkwtmo period would be delayed by the 50 milliseconds that it blocked on the server. Also, because of the difference in the 'seconds' granularity of the '-lkwtmo' switch as compared to the 'milliseconds' granularity of the 'PRGRS_NATIVE_LOCKWAIT, some rounding could cause the actual '-lkwtmo' period on the client to be 181 seconds, versus the specified 180 seconds.
3. Generally, PRGRS_NATIVE_LOCKWAIT should be set to a small number of milliseconds in order to allow the server a reasonable opportunity to resolve resource locks before returning control to the client that is expected to handle recovery programmatically. In such cases, the small PRGRS_NATIVE_LOCKWAIT value setting is probably negligible enough to make the PRGRS_NOWAIT_OVERRIDE switch unnecessary for NO-WAIT ABL requests. The PRGRS_NOWAIT_OVERRIDE switch has greater applicability if the PRGRS_NATIVE_LOCKWAIT value is set to a non-negligible period. Only then does the potential negative affect on application response time for ABL code that uses the NO-WAIT condition become a concern for which the PRGRS_NOWAIT_OVERRIDE might be applied..
4. The DataServer for MS SQL Server uses a default server lock wait timeout of zero - which results in no server blocking from lock contention (and negates the need for the PRGRS_NOWAIT_OVERRIDE switch). The maximum value you can wait on the server using the PRGRS_NATIVE_LOCKWAIT switch is 32767. Any value larger than 32767 is the equivalent of an infinite time period whereby the server would block infinitely. If the server were to wait indefinitely for a resource and the PRGRS_NATIVE_LOCKWAIT period were infinite then meaningful use of the client-side '-lkwtmo' switch would be negated and the need for turning on the PRGRS_NOWAIT_OVERRIDE switch would be necessitated in order to ensure a client response could be received from the NO-WAIT ABL condition.
Note:
For NO-WAIT to be effective while working on locked resources, users will have to set both PRGRS_NATIVE_LOCKWAIT and PRGRS_NOWAIT_OVERRIDE switches.
.