TIP: Perform deletions with zero blocking on large tables
Rajasekhar Reddy Bolla || [email protected] || +91 9966246368 (WhatsApp)
The WITH (ROWLOCK, READPAST) hint in the SQL DELETE statement is used to optimize concurrency and minimize locking contention. Here's what each hint does:
1. ROWLOCK:
•
Ensures that the DELETE operation acquires row-level locks instead of page-level or table-level locks.
•
Helps minimize locking contention, allowing other queries to access different rows in the table concurrently.
•
Useful in high-concurrency environments where multiple processes might be accessing or modifying different rows.
2. READPAST:
•
Instructs the DELETE statement to skip over rows that are currently locked by other transactions instead of waiting for them to be released.
•
Prevents blocking when multiple processes are trying to delete or modify different sets of rows.
•
Especially useful in high-throughput OLTP (Online Transaction Processing) systems where contention for rows is common.
Why Use Them Together?
•
ROWLOCK ensures that only the necessary rows are locked, preventing excessive locking that could slow down performance.
•
READPAST helps avoid blocking issues by skipping locked rows, allowing the DELETE operation to proceed without waiting.
•
This combination is effective in scenarios where concurrent transactions frequently modify or delete rows in a table, reducing contention and improving performance.
When Should You Be Cautious?
•
READPAST can cause some rows to be skipped during deletion, meaning those rows may remain in the table if they are locked by another transaction.
•
This might lead to inconsistent data if not handled properly, so it's important to understand whether missing some deletions is acceptable in your use case.
Query:
Below query helps is to purge the data
DELETE TOP (@BatchSize)
FROM dbo.AuditLog WITH (ROWLOCK, READPAST)