*Edit: Solution turned out to be nothing in the answers here, I was using SQLAlchemy in Python, which is an ORM. I was executing the delete statement below in a transaction, but never committing it. This lead to around 10 open transactions occuring that eventually all needed to be rolled back, thus locking the entire table until rollback was complete.

Given the following table structure, records are being continuously inserted with the update_time of a very recent datetime. A separate Connection to the DB is periodically trimming old records with dates of older than 2 weeks.

Table Structure:

Table Structure

The following Insert is being run very frequently, with different values:

Insert Statement

The following delete statement is repeated until required, running a select right after to see if the process is complete

delete top(5000) from trade_options with (READPAST) where update_time < '<Two Weeks Ago>'

Activity Monitor shows that the Delete is locking, and the Insert is waiting ( LCK_M_IX):

Image of the Activity Monitor


**Edit: Here are the Indexes/Keys as script outputs

CREATE NONCLUSTERED INDEX [IX_order_option] ON [dbo].[order_option]
    [update_time] DESC

    [exchange_id] ASC,
    [symbol] ASC,
    [update_time] ASC
  • I have been debugging this, and it is only these two queries running. I have isolated the database, and I am also surprised that it's locking the whole table. Something is wrong here. I wonder if the delete statement needs an order by clause to prevent the scope reaching the whole table? – CatalystNZ Sep 25 '19 at 7:13
  • I have added the script output of both the Index (On update_time) and the keyed fields which also appeared under indexes in object explorer, however I'm not sure if that means that there is indexing on the keyed fields or not. – CatalystNZ Sep 25 '19 at 19:36

Your problem has a name of lock escalation. You can read more on this here: How to resolve blocking problems that are caused by lock escalation in SQL Server.

By default, SQL Server will acquire the finest-grain lock possible, in order to attain the greatest concurrency. In most cases, this means SQL Server will acquire row (RID or KEY) locks. SQL Server can acquire hundreds or thousands of individual locks on data in a single table without causing any problems. In some cases, however, if SQL Server determines that a query will access a range of rows within a clustered index, it may instead acquire page locks. After all, if every row on a page is going to be accessed, it's easier to manage a single page lock than dozens, or hundreds, of row locks. In other cases, primarily when there is no usable index to help process a query, SQL Server may lock an entire table right at the beginning of processing a query.

In your case I suspect there are some indexes on the table (this means that delete acquires locks on them too) or you do your deletes in one transaction (this means that all the locks are held until the end of the transaction). In this case this happens:

SQL Server will escalate locks when any individual session acquires more than 5,000 locks in a single statement. In this case, there is no randomness in choosing which session will get its locks escalated; it is the session that acquired the locks.

So you should get rid of the wrapping transaction if it exists, or decrease batch size to a smaller one, or disable lock escatation on this table:

alter table order_option
set ( lock_escalation = disable )

Do you think lowering the delete batch to less than 5000, or removing the index would help? In the case of removing the index, wouldn't that slow the delete considerably?

You should not drop this index, it helps you with your delete.

You can try batches of 2000 rows, it should help.

I don't know the kind of queries that run against this table but it could make sense to have clustered index on update_time and PK as nonclustered

| improve this answer | |
  • Thank you. The insert is wrapped in a transaction, but the delete is not. How might you interpret that? The insert happens withing an orm. Everything happens inside transactions and I can't prohibit that unfortunately. – CatalystNZ Sep 25 '19 at 8:31
  • You insert one row at a time and it's not insert to hold X lock on the whole table. It's your delete. But it gets more than 5000 locks at a time so I think you have indexes on yor table. Why don't you check for them? – sepupic Sep 25 '19 at 8:38
  • Yes, there is an index on update_time, I have added that to my post above. Sorry, I was on my phone when I responded to you last time, so I couldn't check at that time. Do you think lowering the delete batch to less than 5000, or removing the index would help? In the case of removing the index, wouldn't that slow the delete considerably? – CatalystNZ Sep 25 '19 at 19:44
  • I updated my answer, decreasing batch size should help – sepupic Sep 26 '19 at 6:59
  • I've accepted this answer, as I think not only did you provide a working solution (like others probably did), your explanation contains the best information. Thanks. I would like to know more about the recommendation around changing to clustered index, and PK nonclustered, but you answered the original question, so happy to accept. – CatalystNZ Sep 28 '19 at 2:38

I see the above situation as very normal case when you want to delete and insert records at the very same time, not sure if insertion takes place only in working hour or even after working hour. I would suggest to put these delete statement inside a SQL job and schedule them everyday late in the night when there is very less/no activity on database. This would prevent any blocking on database.

As mentioned by Darko in previous answer, consider smaller chunks of data while performing delete and keep committing them upon completion. It would be even easier if you could create a view on top of this table and delete from there using your condition as explained by Mr. Brent Ozar here.

In case, you have operations running 24*7 then, its explained nicely with examples by Mr. Daniel here.

Hope above helps.

| improve this answer | |
  • Yes in this case, it's 24/7 so the after hours approach doesn't work. I suppose I'm trying to make sure I have not missed something important here, as I'm not clear why there is contention here. In other words, why there is a wait occuring when the queries hit update_time values that don't conflict. Your link is very helpful, I will try the view approach. – CatalystNZ Sep 25 '19 at 19:40
  • Could you please include DDL of the table and query execution plan for delete statement. – Learning_DBAdmin Sep 26 '19 at 5:24

You should use smaller chunks when you deleting rows. But that would not solve your problem. If you execute the DELETE statement inside transaction the row lock might escalate to the table lock.

I don't know is it possible to apply table lock during the INSERT? It means to use 'WITH (XLOCK)’ option. Is that allowed by your apps?

What's more, it is not a bad idea to check indexes. Do you have one on ‘update_time’ column?

The moral of the story is that locking is normal SQL Server behavior and you can't avoid it. The only thing you can do is to reduce the waiting time.

There is a resource on the internet Building a Custom Blocked Process Report that might help you in order to be able to detect locking easily.

| improve this answer | |

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.