Post reply

lock timeout

  • October 29, 2002 at 5:07 am

    #173566

    How does SQL server decide to enforce a lock timeout and how long does it take? Can I change that?

    Sometimes I know a table is locked (good lock) but I want to go home so I kick off the next process which should start running as soon as the lock is released.

    Sometimes this works great and other times the 2nd transaction gets killed due to "lock timeout request"

  • October 29, 2002 at 8:23 am

    #439699

    Use SELECT @@LOCK_TIMEOUT to return the timeout setting and SET LOCK_TIMEOUT to set new lock timeout setting.

  • October 29, 2002 at 8:33 am

    #439700

    Thanks. It says -1 which means no timeout is set. So how can I get this error if it is unlimited?

    I checked the client and the server.

    -k

  • October 29, 2002 at 8:46 am

    #439703

    Could it be due to dead lock or query timeout?

  • October 29, 2002 at 8:50 am

    #439704

    Unfortunately, I don't remember the error. I will write it down next time.

    Why? What if it was query timeout?

    What if it was deadlock?

    -k

  • October 29, 2002 at 12:51 pm

    #439735

    query timeout:

    sp_updatestats might help.

    If that does not help you can also change 'query wait' with sp_configure

    Default it is -1, which means 25 times the calculated time for the query.

    You can change it into a number of seconds.

    deadlock:

    Make database faster, rewrite the application or live with it.

  • October 29, 2002 at 4:58 pm

    #439758

    SQL manages the deadlocks by it self. Kills one of the locking transaction. It decides this by the most cost significant transactions and the most time running transaction.

    So there's nothing you can do with a deadlock, you manage this with the error SQL fires.

    A lock time out will be fired if you specified the lock_timeout value like Allen_Cui said. If you don't specified the lock_timeout you can't handle it from the server.

    But it your application, for example Visual Basic, you can specified with the command Ado object the timeout, to let the app handle it

  • November 1, 2002 at 2:54 pm

    #440095

    Deadlocks are different to lock tinmeouts. A deadlock is where 2 processes have locks that are stopping each other from proceeding, and so one has to be killed. The lock timeout is when an exclusive lock is held and another process wants to acquire a lock.

    You could write some code to examine syslockinfo to wait until no lock existed before trying to get the lock.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply