Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Wink Unanswered: Deleting without locking?

    Hi All,

    Yes, It can be a stupid question but I was trying to understand if it is really impossible!
    I have a big table (around 200 millions of records) and I have to delete the old record. I read about the hints and ROWLOCK seems to be perfect. I don't want to have pages locked at all.

    However, when I am deleting this table using rowlock I got the following locks
    mode - key , type U (Update) and
    mode - tab, type X (Exclusive).

    I have tried a SELECT on new records but got to wait.

    Is there a chance to lock only the records to be deleted?

    Thanks a lot,
    Felicia Schimidt

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In order to lock only the records you want to delete, you will have to specify a small enough range of the primary key for the index to kick in (around 4%, I think). See what happens when you break up the one big delete into 10 or so smaller deletes.

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    Thanks for your answer
    4% of Index size or table size?

    Thanks a lot,
    Felicia

    Originally posted by MCrowley
    In order to lock only the records you want to delete, you will have to specify a small enough range of the primary key for the index to kick in (around 4%, I think). See what happens when you break up the one big delete into 10 or so smaller deletes.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    4% of the number of rows in the table, actually. But that is only dimly remembered from a long time ago. Before you run the delete, check the estimated query plan to see if it is doing an index seek for the delete, or still doing a full table scan/access.

Posting Permissions

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