Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: Lock on inactive thread won't go away

    Hello,

    I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved)
    I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem

    my question is: can I change this behaviour somewhere?

    The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:

    first a select is performed to fetch a certain record in the file that gets locked
    next an update is performed with following syntax:

    update ... set.... where current of 'pointername'

    this update is waiting for a lock to be released before the batch continues.

    My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed.
    The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people

    If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful

    database is SQL Server 2005 SP1 on win2k3

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Usually, a lock held on a table like this is caused by a SPID opening a transaction, and not committing or rolling it back. Run the following in the database, to see if it has an open transaction
    Code:
    dbcc opentran

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    O the Joy of Cursors

    have you tried declaring the CURSor with OPTIMISTIC WITH VALUES locking

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Feb 2008
    Posts
    2
    Hello all,

    MCrowley, the lock occured again last night so I was able to look for the open transactions but none were open

    I did print the info of the 3 processes that had a lock last night.
    Thread 55 was the idle thread still holding a lock (the last picture)
    the other 2 were awaiting 55

    I ran a debug log on the batch that caused this, if you would like to see it as well, let me know...

    Since these queries are automatically created by the software database layer, I have no hand in changing the use of cursors
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •