Re: Timeout - @@LOCK_TIMEOUT

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: wei xiao [MSFT] (weix_at_online.microsoft.com)
Date: 12/08/04 

Date: Wed, 8 Dec 2004 15:22:35 -0800

As an optimization, SQL Server internally needs to check if a transaction
can acquire some locks without waiting. When this fails, the lock timeout
event is generated (with a duration of 0) but SQL Server will wait for the
lock instead.

The value of the "Duration" column indicates for how long SQL Server waits
before the timeout. If the value is 0, then this is the internal no-wait
timeout. 

-- 
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"OJ" <anonymous@discussions.microsoft.com> wrote in message 
news:193d01c4dd59$34acbc60$a501280a@phx.gbl...
> I'm not sure how this can help, but here it is (one of
> many examples):
>
> row_number=188337
> event_class=27
> text_Data=NULL
> Binary_data=0x000707000DD07C4F23006F01DE541AB4
> DatabaseID=14
> HostName=PROD1
> SPID=73
> ObjectID=1333579789
> IndexID=30
> Mode=3
>
>
> Also, some of them are caused by SPID lower than 50, and
> mode in that case is: 5.
>
> Thanks,
> OJ
>
>>-----Original Message-----
>>This event is generated by a low level system component.
> Please post the
>>entire event data column for us to understand the
> situation.
>>
>>-- 
>>Wei Xiao [MSFT]
>>SQL Server Storage Engine Development
>>http://weblogs.asp.net/weix
>>
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>
>>"OJ" <anonymous@discussions.microsoft.com> wrote in
> message
>>news:200b01c4dca6$c0f48a30$a301280a@phx.gbl...
>>> Hi,
>>> Trace file which I have here, contains many Lock:
> Timeout
>>> events. I don't understand how is it possible when
>>> @@LOCK_TIMEOUT is set to -1 for all connections.
>>> Thanks,
>>> OJ
>>
>>
>>.
>>

Relevant Pages

  • Re: Timeout expired troubleshooting suggestions
    ... Escalation, Lock Timeout, and Deadlock Chain events. ... Periodically the client recieves timeout expired messages and all subsequent inserts fail, as though connectivity has been totally broken. ... We stop and restart the client services (not sql server) and connectivity is restored, but all the inserts that failed remain in a que. ...
    (microsoft.public.sqlserver.server)
  • Re: Timeout - @@LOCK_TIMEOUT
    ... SQL Server internally needs to check ... >lock instead. ... >before the timeout. ... >> confers no rights. ...
    (microsoft.public.sqlserver.server)
  • Re: Detecting Table Locks?
    ... takes some time because it is running through alot of data and the task ... waiting to use the table to timeout over the HTTP protocol. ... just try to lock the table with a short lock timeout, ... But in SQL Server operations rarely ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Extremely High Lock Timeouts
    ... There are lots of internal synchronization protocols which request locks ... with a 0 length timeout. ... SQL Server can get the lock now, great, otherwise if it can't get the lock ...
    (microsoft.public.sqlserver.server)
  • Re: Check Linked Table Connection
    ... That will only test to make sure the connection is available. ... Run the query with a 20 second timeout. ... but I don't have any direct access to their SQL Server nor their ... I am an outside programmer that end-users contract, ...
    (comp.databases.ms-access)