unlock the tables programmatically

  • Hi,

    we have a stored procedure which updates a table. This table has parent child relationship. some times while updating the table it goes into infinte loop and locks the table. Right now we are not sure why it is locking the table. This stored procedure is called from the ASP.NET web page. I was thinking of putting a timer inside the asp.net web page and once the timer expires, if there are any locks on any of the tables in the database, unlock them. Is there a way to do that? Please let me know.

    Thanks,

    Sridhar.

  • I assume that you are using a SQLCommand object.  Couldn't you just set the timeout value to something other than 0 so that the update would fail with a timeout expired. 

    Steve

  • Try to solve the bigger issue of locking inside the loop then u wouldn't need to unlock or set timeout.

    Thanks

    Sreejith

  • The only way that I am aware of to unlock a table is to kill the process that has it locked.

    I agree that you should be trying to track down the process to find out why it is locking a table and not releasing a lock.  Two common reasons is an infinite loop (an end condition is never met) or a deadlock (one process locks one table and is waiting for a second table to be unlock, a second process has locked the second table and is waiting for the first to be unlocked).

    If this happens frequently, you should be able to run a trace from SQL Profiler to find out what is really happening.

    Steve

Viewing 4 posts - 1 through 3 (of 3 total)

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