Results 1 to 6 of 6

Thread: delete without locking the table?

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    delete without locking the table?

    Hi,

    is there anyway i can run delete statements on an audit table that will not lock the whole table?
    As it is an audit table, when i run the delete query, the whole application freezes until the delete query is complete.

    the process is taking several minutes as the table is approx 40M rows, the where clause in the delete is indexed but thats it really.

    any thoughts greatly appreciated by my system users!

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Sql has to put lock on each row involved at least, so try use rowlock hint in your delete statement.

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    Check the query paln to see if index is used, it may be doing full table scan and locking the entire table. Are you deleting all rows or just few?. Also Try to delete few thousands at a time.

  4. #4
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    how do i enforce that the sql uses the index?

    have i misunderstood bol? Doesnt it say hints about rowlocks dont work for deletes?

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Where did you see rowlock doesn't work for deletes? It's part of delete statement's syntax. And you can use index hint to enforce index.

  6. #6
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    my mistake, have re-read bol - was reading nolock and rowlock as the same thing, weeellllll they sound the same!!!

    am now experimenting with small numberes of rows to see if the rowlock sorts the problem. if it does for sure then its a done deal.

    thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •