Kbase P104950: How does SQL server handle lock time out?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/12/2008 |
|
Status: Verified
GOAL:
How does SQL server handle lock time out?
GOAL:
Why do I get Lock request time-out period exceeded error (error 1222) from SQL server?
GOAL:
How to adjust SQL server lock time out?
GOAL:
How to handle timeout in SQL server DataServer?
GOAL:
What switches can I use in SQL server data server to handle time outs?
FACT(s) (Environment):
MS SQL DataServer
Windows
OpenEdge 10.1C02 Service Pack
FIX:
This solution is derived from MS SQL server help with regards on how SQL server handles lock timeout. This information is helpful for SQL server dataserver users to handle timeout situations.
Lock time out
Customizing the Lock Time-out
When Microsoft® SQL Server?¢ 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. If this causes a deadlock, SQL Server terminates one of the participating transactions (with no time-out involved). If there is no deadlock, the transaction requesting the lock is blocked until the other transaction releases the lock. By default, there is no mandatory time-out period, and no way to test if a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).
The sp_who system stored procedure can be used to determine if a process is being blocked, and who is blocking it.
The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 "Lock request time-out period exceeded" is returned to the application.
However, any transaction containing the statement is not rolled back or canceled by SQL Server. Therefore, the application must have an error handler that can trap error message 1222. If an application does not trap the error, it can proceed unaware that an individual statement within a transaction has been canceled, and error 1222 can occur because statements later in the transaction may depend on the statement that was never executed.
Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action for example, automatically resubmitting the statement that was blocked, or rolling back the entire transaction.
To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function, for example:
DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout
GO
With regards to data server, use no-wait option and -Dsrv PRGRS_NATIVE_LOCKWAIT switch can assist in lock timeout situations. 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.