May 23, 2006 at 11:08 am
Is there a way to do a delete on a large group of records without locking the table for reading?
May 23, 2006 at 12:05 pm
All DELETES require locks, but keep in mind that deleting a large group of rows does not always mean that your whole table will be locked. SQL Server will determine the most efficient locking method for the job. This means that you could see row, extent, page, or table locks. You can still read from a table that has locks at any level if you set your transaction isolation level to allow dirty reads or using the NOLOCK or READPAST locking hints.
I would only recommend doing this if you don't care if your results contain dirty reads. If your SELECT statements must contain only committed data, you cannot get around waiting for your DELETE locks.
May 23, 2006 at 12:14 pm
I tried using ROWLOCK on the DELETE, but I believe the DELETE's are too large. I ended up adding NOLOCK to the SELECT's that are being done and it's working like a charm.
May 23, 2006 at 12:30 pm
"I tried using ROWLOCK on the DELETE, but I believe the DELETE's are too large. "
SQL Server may decide to escalate the lock method even when you use a locking hint such as ROWLOCK. If SQL Server determines that using row locks is much too expensive, it will escalate to whatever lock it decides is most efficient.
May 23, 2006 at 2:06 pm
Delete your set by parts. Use a bloody loop or a cursor.
It gonna take forever, but at least your table will be accessible all this time.
_____________
Code for TallyGenerator
May 23, 2006 at 8:36 pm
Schedule it to run off-peak, if you have a peak. You might want to look at partitioning your table. How come you are deleting so much data? Are you archiving old records?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply