locked
Lock Escalation settings RRS feed

  • Question

  • Hi All,

    In one of my production server Deadlock issue happened, for that issue we are planning to change lock escalation  to table.

    Will it make any impact to other tables or objects in database apart from a single table.

    Please suggest anyone it would be great helpful for me.

    Thanks in Advance...

    Tuesday, March 31, 2015 5:55 AM

Answers

  • Hi Srinivas,

    Below is snippet from Paul's Article

    In SQL Server 2005 (and 2008) you can change the behavior of lock escalation (if you really know what you're doing) using these trace flags:
    • 1211 – disables lock escalation totally and will allow lock memory to grow to 60% of dynamically allocated memory (non-AWE memory for 32-bit and regullar memory for 64-bit) and will then further locking will fail with an out-of-memory error
    • 1224 – disables lock escalation until 40% of memory is used and then re-enables escalation

    1211 takes precedence over 1224 if they're both set – so be doubly careful. You can find more info on these trace flags in Books Online.

    In SQL Server 2008, you can change the behavior of lock escalation per table using the ALTER TABLE blah SET (LOCK_ESCALATION = XXX) where XXX is one of:

    • TABLE: always escalate directly to a table lock.
    • AUTO: if the table is partitioned, escalate to a partition-level lock, but then don't escalate any further.
    • DISABLE: disable lock escalation. This doesn't disable table locks – as the Books Online entry says, a table lock may be required under some circumstances, like a table scan of a heap under the SERIALIZABLE isolation level.

    If you are changing lock escalation for particular table using alter table set lock escalation command it will change behavior for that table only

    You should also read How to determine blocking is caused by Lock escalation

    If you have partitioned tables please read partition Level Lock escalation


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, March 31, 2015 7:08 AM

All replies

  • No impact on other tables. But table lock is default behaviour of SQL, SQL Server will by default *always* escalate directly to the table level. 

    Unless earlier you had set lock escalation to AUTO(partition level) and your table was partitioned which you want to change to table now.

    http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/

    Tuesday, March 31, 2015 6:50 AM
  • Hello,

    It's not clear for me; what exactly do you want to change?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, March 31, 2015 6:55 AM
  • Escalating to a table lock may fix some deadlock scenarios, but certainly not all.

    Josh

    Tuesday, March 31, 2015 7:05 AM
  • Hi Srinivas,

    Below is snippet from Paul's Article

    In SQL Server 2005 (and 2008) you can change the behavior of lock escalation (if you really know what you're doing) using these trace flags:
    • 1211 – disables lock escalation totally and will allow lock memory to grow to 60% of dynamically allocated memory (non-AWE memory for 32-bit and regullar memory for 64-bit) and will then further locking will fail with an out-of-memory error
    • 1224 – disables lock escalation until 40% of memory is used and then re-enables escalation

    1211 takes precedence over 1224 if they're both set – so be doubly careful. You can find more info on these trace flags in Books Online.

    In SQL Server 2008, you can change the behavior of lock escalation per table using the ALTER TABLE blah SET (LOCK_ESCALATION = XXX) where XXX is one of:

    • TABLE: always escalate directly to a table lock.
    • AUTO: if the table is partitioned, escalate to a partition-level lock, but then don't escalate any further.
    • DISABLE: disable lock escalation. This doesn't disable table locks – as the Books Online entry says, a table lock may be required under some circumstances, like a table scan of a heap under the SERIALIZABLE isolation level.

    If you are changing lock escalation for particular table using alter table set lock escalation command it will change behavior for that table only

    You should also read How to determine blocking is caused by Lock escalation

    If you have partitioned tables please read partition Level Lock escalation


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Tuesday, March 31, 2015 7:08 AM
  • Hi All,

    Is it support in sql2005?

    if yes how can i change it to table level, please send me a query.

    Tuesday, March 31, 2015 11:45 PM