Results 1 to 3 of 3

Thread: Lock escalation

  1. #1
    Join Date
    Dec 2001
    Posts
    40

    Unanswered: Lock escalation

    MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?

    thanks

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Lock escalation

    RE:
    MSSQL seems to be escalating locks to table locks while we do some write lock testing.

    Q1 What's the best way to handle MSSQL taking table locks?

    Q2 Does mssql support partitioning a table into different file groups? Thanks
    A1 The "best" way to handle MSSQL taking table locks depends on the specifics of the issue and the requirements of the design / situation.

    For example (only) simply exceeding the number of available locks due to high levels of concurrent use would be very different from, say a situation in which inappropriately high isolation levels have been implemented. In the former one might increase memory available for locks, in the latter one might reduce the implemented isolation levels to the lowest possible levels that will not cause corruption issues.

    A2 That type of requirement may be indirectly implemented in versions >=7.0.

  3. #3
    Join Date
    Feb 2002
    Location
    Sweden
    Posts
    34

    Re: Lock escalation

    Originally posted by ryanveach
    MSSQL seems to be escalating locks to table locks while we do some write lock testing. What's the best way to handle MSSQL taking table locks? Does mssql support partitioning a table into different file groups?

    thanks
    Hi!
    Some facts.
    If SQLServer can´t lock a suitable row or range it is forced to lock the whole table. When sqlserver is forced to take so many row locks that it is more economic to take a table lock it "converts" the row locks to a table lock.

    A1:Try to add suitable indexes to the tables in order to help sqlserver just to take row or range locks. If your aplication uses MTS or COM+ the isolation level is Serializable = expensive, but might be required for your app.
    - Jonte

Posting Permissions

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