While running a T-SQL script that is performing massive amounts of deletes, I had the unpleasant experience of causing some sort of lock contention that prevented inserts of data in some tables for other connections. I tried to remedy this by breaking down the request into smaller transactions with the hopes of avoiding an overarching transaction and locks:

set implicit_transactions off

declare @idsToDelete table (id bigint)
declare @currentId bigint
declare @deleteCursor cursor

begin transaction
    insert into @idsToDelete
    select someId from someTable with (nolock) where someCondition = 'some value'

set @deleteCursor = cursor for select id from @idsToDelete
open @deleteCursor
fetch next from @deleteCursor into @currentId

while @@fetch_status = 0 begin

    begin transaction

        delete from relatedTable
        where id = @currentId

        delete from someTable
        where id = @currentId


    fetch next from @deleteCursor into @currentId


close @deleteCursor

My understanding is that set implicit_transactions off should enable me to not have a top-level transaction. Therefore, each of the deletes would run, complete, and we'd be done with it. I was able to see progress with sets of rows being deleted in piecemeal fashion, so I thought it was all good. However, in actuality, inserts into this table were being blocked. And worse, when I noticed and cancelled the run, the whole thing rolled back, continuing to block as it did so!

Is there a way for me to get this thing to run with independent transactions and no locks (except row locks which are OK)? I need to write a big update script that could have similar problems if I'm not careful.

  • By the way, I already noticed a similar question dba.stackexchange.com/questions/49264/… which does not answer my question. – Jacob Dec 4 '14 at 0:19
  • 2
    Could you have a transaction active from a higher level in your call stack? Check @@trancount from within your cursor loop. It should return 1. – Michael Green Dec 4 '14 at 3:08
  • As a workaround, can you completely eliminate the transactions, or do you explicitly need transactional integrity? – Daniel Hutmacher Dec 4 '14 at 10:53
  • If your selection criteria is aligned with a partition scheme, maybe you could simply switch out a chunk of rows? Even if it's not perfectly aligned, maybe you can begin transaction; switch out a larger partition; delete the specific records from the switched-out table, switch back the into the partition; commit transaction? This will admittedly place a schema&tablock on the table for the duration, but the operation may instead be so much faster that it's worth it? Long shot, I know. :) – Daniel Hutmacher Dec 4 '14 at 10:58
  • @Jacob - I use a pattern similar to what Aaron Bertrand described in the link you reference. Please tell us how it falls short in your case. Note also that Daniel Hutmacher asked if you can just delete rows (e.g. 1 at a time) without a transaction. If so, the blocking of any row should be very brief. Do you also have cascading deletes to deal with? – RLF Dec 4 '14 at 14:24

@Jacob - I would say we've all had an unpleasant experience at some point in our lives, so don't beat yourself up. It's admirable that you're seeking guidance and learning from it. So hats off to you.

Some key things to target might be the impacted rowcounts.

  • If you're inserting into @idsToDelete, ideally you have a recordset around 1000 rows or less. Higher rowcounts (thousands+) will cause your queries to slow significantly. I've done a side by side inserting into #temptable vs. @temptable with 1000+ rows, or using a CTE and you will find time and time again that the #temptable or CTE with higher recordsets will outperform @temptable significantly.
  • Deleting from a table where you are sure you're deleting more than 1 row (by ID or something), should include a WITH (ROWLOCK) hint. This means that when scanning a table for a specified recordset to delete, it will not lock the entire tables and cause this massive blocking you're experiencing.

I suspect that a combination of the slowed performance against the table parameter with significant rowcounts, along with deleting potentially thousands of rows from each related table without that lock hint, you would find things crawl and show persistent blocking patterns.

Regarding the transaction, I would keep the transaction to deleting around the delete statements only, or consider utilizing/wrapping the tsql into a stored procedure. It depends on the usage, but typically I recommend steering away from cursors, for performance and best practices.

I can provide an example too if that's helpful. :)

And if you're ever still unsure, if you're able to find a second set of eyes - that's saved a few too!



  • I'm thinking the with (rowlock) would have been key, thanks. And interesting information about temporary tables. – Jacob Dec 4 '14 at 17:01

A few things:

  1. Remove the set implicit_transactions off Actually, this is probably for the best as you don't want them on. This setting has nothing to do with a "top level transaction". When implicit_transactions are ON, INSERT and DELETE (and others) will auto-start a transaction.

  2. You do not need a BEGIN TRAN / COMMIT around a table variable. Not only does it not make sense to require a transaction just to populate a working/scratch table, table variables aren't even bound to transactions in the first place ;-).

  3. You can add the WITH (ROWLOCK) hint to the DELETE statements. Without the hint you might run into Lock Escalation which is when a low-level lock is upgraded to a Table Lock, which happens at 5000 changes.

  4. You might be better off switching back to doing sets of deletes. In this case you would need another table variable or temp table to hold the batch of IDs as you aren't going to delete everything in the @idsToDelete table variable at the same time. Table variables are typically not good for JOINing against as they appear to only have 1 row in them, but that can usually be avoided by using a TOP (n). But a local temp table might just be easier.

For example, rather than moving through, row-by-row, via a CURSOR, just do a WHILE loop until done:


declare @idsToDelete table (id bigint);

insert into @idsToDelete
  select someId
  from someTable with (nolock)
  where someCondition = 'some value';

WHILE (1 = 1)

  DELETE TOP (500) tmp
  INTO   #TempIDs (ID)
  FROM   @idsToDelete;

  IF (@@ROWCOUNT = 0)
    BREAK; -- exit the loop cuz we done

  delete tab
  from relatedTable tab WITH (ROWLOCK)
  INNER JOIN #TempIDs tmp
          ON tmp.ID = tab.ID;

  delete tab
  from someTable tab WITH (ROWLOCK)
  INNER JOIN #TempIDs tmp
          ON tmp.ID = tab.ID;


  TRUNCATE TABLE #TempIDs; -- clear out for the next run
  • 1
    Lock escalation looks like the most likely culprit since some of the deletes in the transaction could include more than 5000 rows. I did not know about the with (rowlock) hint. I wish I could accept multiple answers. – Jacob Dec 4 '14 at 17:00
  • @Jacob Thanks. Just to be clear, that lock escalation is for a single operation, such as one of those delete statements. If a particular @id value can match more than 5000 rows in either of those tables, then yes, you likely ran into lock escalation. However, that doesn't mean that it was the issue. Depending on indexes, etc you can still have blocking and even deadlocks, depending on what other queries are running on the system while you do this. Just FYI. – Solomon Rutzky Dec 4 '14 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.