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.