1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AlexMal
  • 21
  • 4
  • 2
    You delete _all data_? And it takes 5 minutes? I mean I can't even start to fathom why you are doing any of this. – Aaron Bertrand Feb 05 '22 at 15:00
  • 3
    If you delete up to a maximum of 5'000 rows in a single transaction, SQL Server will default to **row-level locks** and thus your deletes won't bother anyone. If you delete more than 5'000 rows at once, in a single transaction, SQL Server might escalate to a **table-level lock** - and that of course would in fact prevent any operation - `SELECT` as much as `INSERT` or `UPDATE` - from being carried out – marc_s Feb 05 '22 at 15:03
  • 1
    Besides delete, there are other DMLs that will select/insert/delete but use other tables within the same transaction. – AlexMal Feb 05 '22 at 15:03
  • Though unclear, it sounds like your procedure uses a single transaction for all the statements it executes. Under that assumption, the insert statements into "table A" will not be blocked by delete statements on that same table within the procedure logic. – SMor Feb 05 '22 at 15:17
  • Table A, let's call it a queue, is filled with data provided by triggers from other tables (these tables are not used in the stored procedure). And yes, all DMLs inside the stored procedure are packed into 1 transaction. – AlexMal Feb 05 '22 at 15:24
  • Perhaps Table Switching might be a quicker way to clear a whole table, see https://sqlperformance.com/2021/09/sql-performance/refreshing-tables-partition-switching – Charlieface Feb 05 '22 at 19:22

1 Answers1

3

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Yeah, thanks for the answer, I've found how its called: "Lock escalation" https://learn.microsoft.com/en-us/troubleshoot/sql/performance/resolve-blocking-problems-caused-lock-escalation#lock-escalation-thresholds – AlexMal Feb 05 '22 at 20:57