How can remove lock from table in SQL Server 2005?

Question

I am using the Function in stored procedure , procedure contain transaction and update the table and insert values in the same table , while the function is call in procedure is also fetch data from same table.

i get the procedure is hang with function.

Can have any solution for the same?

Answer 1

This can be difficult to diagnose. Microsoft has provided some information here:

INF: Understanding and resolving SQL Server blocking problems

A brute force way to kill the connection(s) causing the lock is documented here:

http://shujaatsiddiqi.blogspot.com/2009/01/killing-sql-server-process-with-x-lock.html

Some more Microsoft info here: http://support.microsoft.com/kb/323630

Answer 2

How big is the table? Do you have problem if you call the procedure from separate windows? Maybe the problem is related to the amount of data the procedure is working with and lack of indexes.

Answer 3

If I'm hearing you right, you're talking about an insert BLOCKING ITSELF, not two separate queries blocking each other.

We had a similar problem, an SSIS package was trying to insert a bunch of data into a table, but was trying to make sure those rows didn't already exist. The existing code was something like (vastly simplified):

INSERT INTO bigtable
SELECT customerid, productid, ...
    FROM rawtable
    WHERE NOT EXISTS (SELECT CustomerID, ProductID From bigtable)
    AND ... (other conditions)

This ended up blocking itself because the select on the WHERE NOT EXISTS was preventing the INSERT from occurring.

We considered a few different options, I'll let you decide which approach works for you:

1. Change the transaction isolation level (see this MSDN article). Our SSIS package was defaulted to SERIALIZABLE, which is the most restrictive. (note, be aware of issues with READ UNCOMMITTED or NOLOCK before you choose this option)
2. Create a UNIQUE index with IGNORE_DUP_KEY = ON. This means we can insert ALL rows (and remove the "WHERE NOT IN" clause altogether). Duplicates will be rejected, but the batch won't fail completely, and all other valid rows will still insert.
3. Change your query logic to do something like put all candidate rows into a temp table, then delete all rows that are already in the destination, then insert the rest.

In our case, we already had the data in a temp table, so we simply deleted the rows we didn't want inserted, and did a simple insert on the rest.