Locking scheme and promotion - changing the HWM and LWM

Posted on 2006-05-26
Last Modified: 2008-01-09

We have a table that has about 500K rows, with a locking scheme of ALLPAGES.  We'd like to change this to DATAROWS and increased the HWM and LWM to 5000.  Currently they are set to 200, which is the default.  Do you see any problems or issues to consider with this change?

The reason for this change is that we've seen many page locks and an occasional table lock on 1 table.   This has blocked other users.   There is a program that deletes and inserts to this table. It has to do  that for the entire set of rows before doing a COMMIT.  According to the user, the number of rows varies greatly from 10 to more than 5000.    

Btw, the number of locks is configured at 35000.

Question by:maria_rossi
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Summary - I think this would be a bad idea.

    Everyone seems to think table locks are the result of lock promotion. Now certainly some could be, but it's far more likely to be because of SQL and index design.

    If you have an UPDATE or a DELETE with a WHERE clause, and you do not have any indexes that are useful for at least one of the WHERE clauses, then you get an exclusive table lock without any lock promotion, regardless of locking scheme.

    (Here, "useful index" means "the column in the WHERE clause is the leading column of the index, nothing in the index key is being updated, and the optimiser has fresh statistics available that say it would be faster to use the index than to table scan".)

    The #1 cause of intrusive locks and lock problems is SQL and transaction design. Going straight to tuning locking scheme and lock promotion thresholds is at best unlikely to do much to help, and at worse will make matter very much worse.

    Re. lock promotions - do you have any reason to suspect these are the cause of your table locks? You could confirm this by running sp_sysmon at times when the table locks are known to occur. If it reports zero lock promotions then this isn't the problem, and you should not change the default lock promotion thresholds. Remember too that lock promotions are complicated and changing the promotion thresholds still is no guarantee that locks won't be promoted...

    Re. locking scheme - again, do you have any reason to think the problem is multiple connections/users attempting locks on rows on the same page? This is a lot harder to confirm - maybe a combination of sp_lock and sp_objectstats could give some evidence for this.

    Row level locking - datarows locking scheme - is very expensive. Your data takes up more space, UPDATES start causing a lot more fragmentation, and you need a *lot* more locks. Worst case you could need 500,000 more locks!!

    Let's look at your transaction design - you have some code that deletes and inserts, and you want to COMMIT the entire operation as a single batch. Any operation that has to table scan the table will be blocked by this, regardless of locking scheme or lock promotion thresholds...

    Note that your proposed lock promotion thresholds still wouldn't necessarily prevent any promotions if indeed any are occurring... LWM and HWM both at 2,000 means "never attempt a promotion below 2,000 page locks, and always attempt one once 2,000 are reached". If promotions were your problem you'd be getting this at 200 locks... changing to 2,000 isn't much of an improvement. The correct numbers would more likely be "more than 5,000" since that's how many page locks you could be dealing with.

    Also, once on datarows locking scheme, you still get lock promotions, but actually they're even worse! It will go straight to table locks from row locks, so you've paid all the overhead for row-level locking but are still getting table locks...

    I'd suggest instead confirming whether any lock promotions are occuring (sp_sysmon), and confirm where the locking problems actually are (sp_objectstats). If you have the option of looking at code, then see whether there are any opportunities to do your batch job in smaller batches. It might be that each DELETE/INSERT pair must be done in its own transaction, but there are no dependencies between any pairs...?

    Failing that, cautiously try datapages locking instead. It fixes some locking problems and isn't as expensive as datarows. (You can leave your number of locks at the same value, for example.)

    Good luck!

    Author Comment


    Thanks for the response.   I'll try to answer your questions.  

    The program  does delete and inserts only (no updates).  For the delete, I think it has a usefull index because

    Clustered index has these columns:

    And the WHERE clause of the delete is this:

           mfg_loc = @s_mfgloc                  and
           q2c_nbr = @s_q2cnbr                  and
           bom_nbr = @i_bomnbr            and
           ln_nbr = @i_lnnbr                  and
           sub_ln_itm_id = @s_sublnitmid            and
           orig_shpschd_id = @s_origshpschdid      and
           mstr_part_nbr = @s_mstrpartnbr

    Lock promotion:  today's sp_sysmon did not show any lock promotion.  However in our test, we tried it both ways with the same set of data.  When ALLPAGES, sp_lock should a bunch page locks (exc_page). When DATAROWS, sp_lock should a table lock (exc_lock).

    And yes, blocks are caused by these page locks (or table lock when it occurred).  We were able to confirm that sp_lock  and DbArisan's monitor.  And the locks are occurring only on 1 table.  This program has all those locks.

    Number of locks:  can you explain why the number of locks needed would increased considerably?  Is there is way to compute how much locks is needed?

    Blocking:   Wouldn't changing to DATAROWS reduce the blocking?  True, if the program is running and another is trying to use the same row, there it will be blocked even  with DATAROWS.  But if they are using different rows, there is no blocking.  But with ALLPAGES,  blocking can occur even if the 2 row are different but are at the same page.  Is that right?

    Can you clarify this paragraph?  Why would table lock occur at 200 locks when the HWM/LWM are at 2000?  Also, am thinking of setting them to 5000, not 2000.  

    Note that your proposed lock promotion thresholds still wouldn't necessarily prevent any promotions if indeed any are occurring... LWM and HWM both at 2,000 means "never attempt a promotion below 2,000 page locks, and always attempt one once 2,000 are reached". If promotions were your problem you'd be getting this at 200 locks... changing to 2,000 isn't much of an improvement. The correct numbers would more likely be "more than 5,000" since that's how many page locks you could be dealing with.

    I guess, our goal is to reduce the likelihood of:
    1.  blocking other users  thru page lock
    2.  table lock which will  block all userrs.  When this occur,  all the manufacturing lines are brought down.

    Thanks again.


    Author Comment


    Also, user might be able to tolerate the blocks caused by page locks,  as it does not block everyone. It would block only those using the rows at the same page as the ones that the program is using.  But we really want to avoid  is a table lock, as much as possible, as it would prevent everyone from accessing the table.

    Would it better/safer to retain the ALLPAGES locking scheme and just increase the HWM/LWM to 5000?  Would increasing the HWM/LWM at ALLPAGES use up more locks?


    LVL 24

    Accepted Solution

    1) With your WHERE clause:

          mfg_loc = @s_mfgloc               and
          q2c_nbr = @s_q2cnbr               and
          bom_nbr = @i_bomnbr          and
          ln_nbr = @i_lnnbr               and
          sub_ln_itm_id = @s_sublnitmid          and
          orig_shpschd_id = @s_origshpschdid     and
          mstr_part_nbr = @s_mstrpartnbr

    Are these local variables or stored procedure parameters?

    If local variables - you're not using any index, and this will table scan. (If a WHERE clause has local variables, you never use any index for them, since the optimiser can't know the value of the local variable at compile time).

    2) Lock promotion: Wait, you got exclusive table locks with DATAROWS but not with ALLPAGES? I wonder if DATAROWS forced a promotion because of how many more locks were used!

    You use vastly more locks with DATAROWS than with DATAPAGES or ALLPAGES, because you need one lock per row, and you almost always fit more than one row per page. Let's say you fit 10 rows per page, then to read or write to 200 pages with page locking you need 200 locks, but with row locks you need 2,000 locks. This is why you would need way more locks than you're currently using. Worst case, you end up reading every row in your table but for various reasons don't use a table lock. Since you have 500,000 rows then you could need 500,000 (row) locks.

    3) 2,000 vs 5,000: Sorry, typo on my part!!

    What I was trying to say in that paragraph is that if you were getting lock promotions, then with the default values for HWM and LVM you would be getting lock promotions frequently. If you aren't seeing any in sp_sysmon with the default settings, then I don't believe this is your problem.

    You might get *more* lock promotions with DATAROWS, since lock promotions works by number of locks used. Since DATAROWS uses considerably more locks, you'll hit those promotion thresholds much sooner! Remember too that you use a different set of parameters to configure row lock promotion thresholds...

    4) Blocking : Switching to DATAROWS might help your blocking, but maybe the problem isn't that people are trying to read the same row, or even different rows on the same page. It might be that they're blocking on data vs index access. DATAPAGES is a good first step to try since it eliminates index locking and usually this is all you need. It has most of the benefits of DATAROWS without the extreme overhead. It's always a good idea to try DATAPAGES first.

    5) So what's best?? From what you've written, sp_sysmon has never detected a lock promotion except when you use DATAROWS. So don't use DATAROWS! 8-) Switch the table to DATAPAGES and continue to monitor with both sp_sysmon and sp_objectstats.

    The best advice anyone can give you here is to carefully examine the design of this piece of code. Does the business really require that all the work be done as a single transaction? Are there really data dependencies between each row that is being deleted and inserted?

    Author Comment


    Thanks for the explanation.  Now, I have a bettter understanding of locking scheme and promotion.

    Those are procedure variables.  And unfortunately, business requires that it all be done in a single transaction, ie, all or nothing.  

    For now, we retained the ALLPAGES and just increased the HWM and LWM to 1000.   With ALLPAGES, we estimated that this setting will be sufficient to prevent a lock promotion to table lock. most of the time.  That seemed to have solved the problem.    Table lock has not occurred since the change was made.  There are still page-locks occuring, but  we have not seen any blocking.    Customer was willing to compromise a somewhat intermittent slow-down for a short period (when this program runs)  as long as it does not lock everyone out.  

    We thought this is the easiest change as the table does not  need to be recreated or re-organized. Should we encounter anymore problems, then we will look into DATAPAGES.
    Thanks again.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Synchronize a new Active Directory domain with an existing Office 365 tenant
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now