Does the delete operation block any insert into the same table?

Question

I have table A and a stored procedure that deletes all data from that table periodically. All queries in the stored procedure are packed into 1 transaction. But sometimes the stored procedure execution takes up to 5 minutes. Could it be that executing stored procedure will block inserts on the same table A?

The stored procedure will never be called again until the previous call has been completed.

Will it be different for READ COMMITTED and READ COMMITTED SNAPSHOT ISOLATION?

Answer 1

Yes, a statement like DELETE FROM YourTable; would take out a table lock blocking all other changes to the table until it was done. I don't think that changing the isolation level will help much, unless you put snapshot on the whole database (i.e., Snapshot Isolation).

Usually you want to try a different approach for cases like this. Either:

1. Try breaking the DELETE up into smaller "chunks" so that it each chunk takes less time and will not block the entire table. Or if this is not appropriate, then ...

2. Create an empty duplicate of YourTable, then change the name of YourTable to something like Yourtable_deleting and change the new table's name to YourTable. Then DELETE (or just DROP) Yourtable_deleting.