lock timeout event

• mchoka

  Old Hand

  Points: 338

  More actions

  January 5, 2004 at 7:42 am

  #83627

  Hello All-

  I've been troubleshooting deadlocks in a third-party app.  I ran a trace with the Lockeadlock, Lock:Timeout, RPC:Starting, SQL Batch:Starting, and all Error and Warning events.  I then imported the output files into a table for analysis.  My results show 90,000+ Lock:Timeout events and 20 Lockeadlock events for a 5 hour period!  The vast majority of the Lock:Timeout events are for index or table resources.  The server is running SQL2K, sp3a.

  Here's my questions:

  -Shouldn't I be seeing corresponding Attention or Exception events for the Lock:Timeouts?  I thought SQL Server returned an error 1229 to the client and terminated the statement on a lock timeout.  Would I not see this in the trace?  Do I really have 90,000+ statements being terminated?

  -a small number of the database ids in the Lock:Timeout events are large ids not included in sysdatabases.  Where are these coming from?  A bug?

  -My developers swear that they are not setting a lock_timeout on their connections.  Nor can I find a SET LOCK_TIMEOUT statement in the trace output.  So why am I getting all these timeouts?  I thought SQL Server by default waited indefinitely on a lock.

  I confused!  Any insight would be greatly appreciated.

  thanks-

  Margaret

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  January 5, 2004 at 8:27 am

  #488659

  You should check for the SET LOCK_TIMEOUT option in the trace to know id the app transaction is setting a lokcing option. But you should find anything.

  You should also check 'locks', 'remote query timeout (s)' option in the sp_configure stored procedure, wich all sets locking options to a server level.

  I think that if the app manage the dead lock error number, then you won't see an error of the deadlock.

• mchoka

  Old Hand

  Points: 338

  More actions

  January 5, 2004 at 9:45 am

  #488665

  Hi, thanks for your reply.

  I searched for SET LOCK_TIMEOUT in the trace and found nothing.  The 'remote query timeout' setting is set at the default of 600 seconds.  I thought that the 'remote query timeout' setting only affected heterogeneus queries and remote stored procedures. (?)  Unfortunately my third-party app doesn't trap the deadlock errors!

  thanks-

  Margaret