0

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>
Prakazz
  • 421
  • 1
  • 8
  • 21
  • How many records you have in the table? what query you have written so far? – Rajat Jaiswal May 28 '18 at 07:09
  • 1
    1) [sp_whoisactive](http://whoisactive.com/downloads/) 2) How big is your table? 3) How do you know it is not I/O problem? 4) Do you delete all rows or some subset of table? After we got answers for these question we may be able to help you, otherwise it will be reading tea leaves. – Lukasz Szozda May 28 '18 at 07:17
  • @RajatJaiswal First I tried deleting around a set of records (206 records) then took only 1 ID and tried deleting only one record. In both cases it is getting locked. And the statement is a simple one I am not even joining it with other tables. – Prakazz May 28 '18 at 07:32
  • then show us this simple delete statement. Also, are there any triggers on this table ? Maybe `cascade delete` is set to on for a foreign key ? If so then maybe there are triggers on that table ? – GuidoG May 28 '18 at 07:41
  • @GuidoG I have added how my delete statement looks like. There are no triggers on the table, there is no "Cascade on Delete" operation on for a foreign key. – Prakazz May 28 '18 at 07:55
  • if you turn this into a select, how many records does it returns ? – GuidoG May 28 '18 at 08:29
  • can there be concurrent transaction on this table ?First, find the default Isolation level of your database.Most probably it is because of READ COMMITTED.you should implement READ Committed Isolation Level. – KumarHarsh May 28 '18 at 08:36
  • Maybe someone else is locking. [Here](https://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) you can find how to check what is blocked and by whom – GuidoG May 28 '18 at 09:07

0 Answers0