*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.
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 < '<Two Weeks Ago>'
Activity Monitor shows that the Delete is locking, and the Insert is waiting ( LCK_M_IX):
**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