Delete without locking?

  • Is there a way to do a delete on a large group of records without locking the table for reading?

  • 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.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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.

  • "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.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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

  • 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