SQL ????? ??? ????? ?? ????? ?? ???? ???? ??? ?? ????????? ?????? ?? ?? ???? ?? ??? ???? ????

???? ?????? ???? ??????
???? ID: 323630 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

??????? ????? ?? ?????? locks ??? ?? fine-grained locks (???? ?????? ?? ????? locks) ??? ?????? ???? ?? ????????? ??? Microsoft SQL ????????? ???? ?? Server ?????? ??? ?? ?? ?? ????? ?? ??????? ???? ?? ???? ?? ?? ?????? ????, SQL Server ???? ???? ??? ??? ????? ?????, ???? ????????? ?? ?????? ????? locks ?? ??? ?????? ??? ??? ????? ?? ??? ??? held locks ?? ?????? ?? held locks ?? ??????? ??????????, SQL ????? ?? ??????? ??????? ?????? ????? ????? ??????? ?? ??? ?? ??????? ?? ???? ???? ??? ???????? ??????? ???? ?? ?? ???? ???? ???? ?? ??? ?? ??????? ?????? ????? ?????? ?? ?? ?? ?????? ??? ???????, ??? ????????? ?? ?????? ?? ??? ??????? ?? ?? desirable ???? ??, ?? escalated ?????? ????? ???? ???????????? ?? ??????? ?? ???? ??, ?? ??? ??? ????? ?? ????? ?????? ?? ???? ??? ?? ???? ?? ????????? ???? ?? ??? ???? ?? ????? ???? ?? ?? ???? ????? ????? ???? ?? ????? ?? ??? ?? ?? undesirable ????? ????? ?? ??? ??? ???? ?? ??? ???? ?????

???? ???????

???? ??? ????? ???? ?? ???????? ???????? ???? ????

??????? ????? ??????? ????????? ?????? ?? ???? ???? ???? ????????? ???? ?? ???? ????? ????? ?? ??-??? ??? ?? ?? ?? ????????? ?????? ????? ??????? ?? ??? ??, ?????? SQL Profiler ????? ????????????: ??????????? ??? ?? ???? ??? ??? ???????: ???????????? ??, ????? ????? ????? ?? ???? ??? ???? ????? ?? ?? ?? ?? ???? ??? ?? ?? ??????? ???? ????????? ??? ???? ???? ???? ???

??? ????? ????? ??????? ?? ??? ??, ?? ???????? ???? ?? escalated ?????? ????? ???? ?????????? ???????? ???

Head blocker ?? ????? ???? ???? ?? ???? ????? ????????? IDs (SPID) ????? ?? ??? ?? ?? head blocker ?????? held ????? ?????? ?? ????? ???? ???? ?? ???? ??? ???? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
224453SQL Server 7.0 ?? 2000 ????????? ?????? ?? ??? ???? ?? ?? ?????
??? ????? ?? ?? ???? ???????????? ?? ????? ?? ??? ?? S (????) ?? ?? (?????) X ????? ??? ?? ??? TAB (??????-????) ????? ?? ????? ??? ?? ??, ?? ??????? ????? ???? ?????? ??? Particular ???, ??? TAB ????? ?? (???? ??? ????? ?? ??? IS, IU ?? IX) ?? intent ??????? ??, ?? ???? ?? ????? ????? ?? ??????? ??? ???? ????????? ?????? ?? ???? ????? ?? ??????? ?? ???? ???? ?? ??? ???, ?? ??? ?????? ?????? ?? ??? Q224453 ???? ??????

??? ????? ?? ????? ?? ??? ???? ????

????????? ???? ???? ?? ??????? ????? thresholds ??? ???? ?? ??? ??? ???? expensive ???????? ?? ????? footprint ?? ?? ???? ?? ??? ????? ????? ?? ????? ?? ??? simplest ?? safest ????? ??? ???????? ??? ?????? ?? ?? ?? ?????? ?? ??????? ???? ?? ??? ?? ????? ???:
  • ?? ???? ???????? ??? ??? ??? ????????? ?? ??? ????? ?????? ?? ???, ???? ???????? ?????? ?? ?? hundred thousand ?????? ??????? ?? ??????? ?? ??? ????? ?????? ???? ?? ??, ?? ???? ??? ?? ???? ?? ?? ?? ???? ???????????? ?? ????? ???? ??? ????? ????? ?? ???? suppose:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						
    ?? ??? ??? ??? hundred ?? ?????? ?? ????? ?? ?? dramatically locks ?? ????? ????????? accumulate ?? ??????? ????? ?? ????? ?? ?????? ?? ?? ???? ???? ?????? ?? ???::
    SET ROWCOUNT 500
    delete_more:
         DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • ?????? ????? footprint ???? ?? ??? ??? ???? ?????? ???? ?? ?? ????? ????? ?? ??? ?? ???????? Lookups ?? ??? ?????? ??? ???? ?? ????? ????? ?? ??????? ??; ???? ??? ??, ?? deadlocks ?? ??????? ?? ?????? ??, ?? ????????: adversely ???????? concurrency ?? ???????????? ?? ?????? ?? ??????? ???? ?? ??? ??????? ????? ?? ???? ??, opportunities ????? ??????????? ?? ?????? ????? ?? ??????? ?? ??? ?? ??????????? ?? ?????? ?????? ?? ??? ??? ?????? ??????????? ??? ?????? ?? ??? ?? ??? ??????????? ????? ?? ??? ????? seeks ??? ?????? ?? ?? ?? ??? ????? ??????????? ???????? ?? ???? ?? ??? ?????? ???????? ?????? ????? ??? ????? ?? ????? ????? ??? ???? ?? ???, ???????????? ??,??????????? ???????? ???????SQL Server 2000 ?? ??? ?????????????? ?? ???????? ??????? SQL Server 7.0?

    ?? ???????????? ?? ?? ?????? ?? ??????????? ????? ?? ??? seeks ?? ??? ??? ??? ????????? ?? ???? ???? ?? ??? ??? ???????? Lookups (selectivity ????? ?????? ?? ??? ??????????? ?? ???) ?? ???? ?? ???? ?? ??? ???? ??? ??? SQL ????? ?? ?????? ????? ?? ?? ???? ???????? ????? ??????? ?????? ?? ????????? ???? ?? ???? ??, ?? ?? PREFETCH ???????? ????? ???? ?? ??? ????? ?? ???? ??? SQL Server PREFETCH ?? ????? ???? ???????? ????? ?? ???, ??? ?? ?????? ?? ?? ??? ?? ??? repeatable ???? ?? ??? ?????? ?? ?? ??? ?? ????????? isolation ???? ?? ??? ???? ?????? ???? ???? ?? ?? ?? ???? ??? ????? ?? ??? ???? isolation ???? ?? ?? ?? ??? ???? ??? ?? ??? ???? ?? ???? ?? ??????? ????? locks (????? ????? ??????????? ?? ?? ?? nonclustered ???????????), ????? ????? thresholds ?? ??? ???? ?? ??? ??? ??? ?????? ???? ?? ???? ??? ?????? ?? ?? ????? ?? ????? ??? ?? ?????????? ?? ??? ?? escalated ????? ?? ???? ?????? ?????, ??, ??? ??, ?? ???? ??????? ??? ?? ???? ??????? ????? ?? ??? ???? isolation ???? ?? ??????? ??? ??? ???????? ????? ?? ??? PREFETCH ??? ????? ???? ?? ??? ??, ?? ?????? ????? ??? ??????????? ????? ?? ??????????? ????? ??????? ?????? ???????? ????? ??????? ?????? ???? ??? ????? ???? ???? nonclustered ??????????? ?? ???????? ??????? ?? ?????? ?? ????? ????? Covering ??????????? (??????????? ?????? ??? ????? ??? ?? ???? ?????? ??? ??? ????? ????? ??? ??) ????? ?? ??? ???? ??, ?? ?? ?? ?? ?? ??????????? ?? ?? ????? ?????? ?? ????? ?? ??? ?? ???? ??? ??? ??? ??? ????? ???? ??? ????? ??? ??????? ?? covers impractical ???

    A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.

    ???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
    260652Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer
  • Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in themytabletable and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN				
    This query acquires and holds an IX lock onmytablefor one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using ansp_indexoptionstored procedure).
Additionally, you can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
???:When a "1204" error occurs, it stops the processing of the current statement and causes a rollback of the active transaction. The rollback itself may block users or lead to a long database recovery time if you restart the SQL Server service.

????????? ????? ????? ???? alters ROWLOCK ???? ??? ????? ????? ?? ????? ?? ??? ??? ??????? hints ????? ????? ?? ??? ???? ???

?? ???? ????????? ????? ????? ?? ??? ??? ?? ?? ?? ???? ??? ???? discussed ?? ????? ????? ?? ????? ???? ?? ????? ?????? ???? ??? ??, ???? ??????? ????????: ?????? ?????? ?? ??? ???? ??????? ?? ??? ????? ????? ?? ????? ???? ?? ????? ???????? ???? Microsoft ??????? ???? ?? ???? ?? discussed ???? ?? ???? ??? ??? ?? ??? investigated ?? ????? ???? ????? ???????? mitigate ???? ?? ??? ???? ???? ???????? ?? ????? ??????? ????? ?? ???? ?? ?? ????? ????? ?? ???? ?? ???? ?? ????? ???? ?? ????? ???? ?? ??? whenever SQL Server ??????? ???? ??, ?? ???? ????? ????????? ???????? ?? ??? ??? ????? ???

????? ?? ??? ??? ????? ????????? ????????, ????? SQL ?????????? ??????? ??? ????-????? ????, ????? ???????, ?? ???? ??? ???????????? ??,????????? ????????, ?? ???? ??? ????? ???????? (??????? ??? ?? ?????? ??? ?? ??? ???) ?????:
-T1211
?? SQL ????? ???? ?? ??????? ???? ?? ??? ??? ??? ????????? ???????? ?? ??? ????? ???? ?????? ??? If you run the following query in Query Analyzer the trace flag takes effect immediately:
DBCC TRACEON (1211, -1)				
However, if you do not add the-T1211startup parameter, the effect of atraceoncommand is lost when the SQL Server service is cycled. Turning on the trace flag prevents any future lock escalations, but it does not reverse any lock escalations that have already occurred in an active transaction.

???

???? ID: 323630 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
??????: 
kbinfo kbmt KB323630 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:323630

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com