I am facing an issue while trying to delete a set of records from a table. When I run the delete statement it ends up locking the table and hence blocking all other operations on that table. Only with NoLock hint I am able to view the result sets from the table. Did check using "SP_WHO2" as well but there is no "Blk By" SPID against any connection. Can anyone explain me what issue I am currently facing with.
DELETE tbl
FROM <TableName> tbl
WHERE tbl.Condition1 = <Value> AND tbl.Condition2 = <Value> AND tbl.Condition3 = <Value>