How to prevent LCK_M_IX waiting/locking on the following Delete and Insert queries *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:



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



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 &lt; '&lt;Two Weeks Ago&gt;'


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



Thanks

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

CREATE NONCLUSTERED INDEX [IX_order_option] ON [dbo].[order_option]
(
    [update_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[order_option] ADD PRIMARY KEY CLUSTERED 
(
    [exchange_id] ASC,
    [symbol] ASC,
    [update_time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
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