SQL Server Performance

AllowTableLocks?

Discussion in 'T-SQL Performance Tuning for Developers' started by mtmingus, Sep 10, 2003.

  1. mtmingus New Member

    Why there is no AllowTableLocks in sql server 2k, only
    AllowRowLocks and AllowPageLocks go with SP_INDEXOPTION:

    SP_INDEXOPTION 'table_name', 'Allow<...>Locks', TRUE/FALSE

    ?
  2. gaurav_bindlish New Member

    This option specifies how the index pages are locked ( lached to be more precise) while data is being modified. As index pages don't have a table, there is no option to lock tables.

    For locking data pages for the table TABLOCK is provided.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. Twan New Member

    Good point though... There is no way to force SQL to only use row or page locks i.e. to never escalate to table locks... Are you having a specific problem that you are trying to solve?

    Cheers
    Twan
  4. mtmingus New Member

    What can I do to disallow lock escalation to table level?
    I mean, row level locking is acceptable.
  5. chilisauce New Member

    From what I understand about locking, row-level locking is more resource intensive. Therefore a table lock requires less resources which is a good thing.

    -chili
  6. gaurav_bindlish New Member

    There is no way you can do that. By providing locking hints, you can specify the optimizer to start with particular kind of lock but during query execution if optimizer is taking lot of memory because of multiple locks, it will escalate the same to next level.

    For addressing these issues, consider creating appropiate indexs on the table.

    Normalization to redue the data size can also be considered.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  7. gaurav_bindlish New Member

    Chilli, row locking is desirable in certain cases wherein there are very few rows impacted by query. This was a considerable perofrmnace improvement in SQL Server 7.0.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. mtmingus New Member

    Thanks. My 1st reply was a few secs too earlier.
    Yes, I have lock escalation problem:

    a fairly large table, a half million records,
    from time to time, a process kicks in and does some
    updates after comparing the incoming data. I have a
    row lock on every single update. The worst thing is,
    I CANNOT have an index on the unique column which is
    one of the where clause fields - I know if I had that
    index, there should be no lock escalation.
  9. chilisauce New Member

    I understand this concept. I was trying to convey that lock-escalation to the table level is not a bad thing. Many people think this is the case when in fact it is the opposite. Think about it. It is easier to lock and entire object then to find specific elements within the object and lock them.

    -chili
  10. gaurav_bindlish New Member

    Do you experience row locks or you have forced row locks in your query?

    And could you be more specific about not being able to create index on the unique column?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  11. Twan New Member

  12. gaurav_bindlish New Member

    Chilli,

    Again elaborating on the same point, table level lock is good from less memory consumption point of view but bad from concurrency point of view. So we should leave it to SQL Server what it thinks is the best option.



    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  13. mtmingus New Member

    quote:Originally posted by gaurav_bindlish

    Do you experience row locks or you have forced row locks in your query?
    <<< I have a FORCED ROWLOCK hint

    And could you be more specific about not being able to create index on the unique column?
    <<< that index incurs performance hit for massive insert

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

  14. Twan New Member


    An index would not prevent lock escalation... If you need to prevent it then batch updates in a few hundred rows at a time...

    Cheers
    Twan
  15. bambola New Member

    Did you try using PAGLOCK?

    Bambola.
  16. Twan New Member

    What does your insert do? <br />When you say massive, how massive? <br />How much is performance affected?<br /><br />How many columns in the table?<br /><br />If you have an identity field, could you create a clustered index, which would be ok for insert performance at the end of the index...?<br /><br />What is your ratio between insert/update/delete/select on this table? <br />The index could assist the latter three, but would hurt the insert to some extent...<br /><br />Why is the insert so time critical?<br />Is the insert done during the same time period as the updating, or are both batched?<br /><br />Sorry lots of questions... <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Twan
  17. mtmingus New Member

    Haven't tried PageLock, but I suspect it could cause the
    same problem: deadlock.

    I think TWan's batching idea is good. Actually that's what
    we have been doing a lot of other places. This one was
    apparently missed because it doesn't occur as frequent,
    but deadlock even once is bad enough.

    Thanks everyone!


    quote:Originally posted by bambola

    Did you try using PAGLOCK?

    Bambola.
  18. gaurav_bindlish New Member

    Twan, an index will not prevent lock escalaltion but it will hwlp to access the record faster and the locks will be relased soon.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  19. Twan New Member


    yep definitely agree. An index could potentially help updates, selects and deletes, but would penalise inserts to some extent (I wouldn't have thought there would be a drastic difference though). Sounds like Zackhu will choose to batch the updates...

    Cheers
    Twan
  20. mtmingus New Member

    If I don't want to rowlock escalated into pagelock so
    I run the disallow command. Would that actually make
    rowlock escalated into the nect level: tablelock, or
    the qyery would run slower by stick to rowlock?

    quote:Originally posted by Twan


    yep definitely agree. An index could potentially help updates, selects and deletes, but would penalise inserts to some extent (I wouldn't have thought there would be a drastic difference though). Sounds like Zackhu will choose to batch the updates...

    Cheers
    Twan
  21. Twan New Member

    Hi Zackhu,<br /><br />if you disallow page locks then row locks will escalate to table locks. The only way to prevent SQL from escalating is to not let the sql take out enough locks to warrant escalation (which is by batch updating <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> )<br /><br />Cheers<br />Twan
  22. bambola New Member

    Sql server does not really escalates locks to the next level. It actually always escalates to table lock. Both rowlock and paglock will escalate to a table lock. This is why using PAGLOCK might make a difference as it will use less resources.
    You might want to consider adding an index after all. This together with batch updates as Twan suggested would probably solve the update problem.
    And when you have massive inserts, you could drop the index then recreate it.

    Bambola.

Share This Page