SQL Server – How to Write a Large Update/Delete Script Without Provoking Locks

lockingsql servert-sqltransaction

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'
commit

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

    commit

    fetch next from @deleteCursor into @currentId

end

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.

Best Answer

@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!

Cheers!

-skibunnysqldiva

Related Question