SQL ????? ????????? ?????? ?? ??? ???? ?? INF: ?? ?????

???? ?????? ???? ??????
???? ID: 224453 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ???? ??? ?? "???????" ???????? ???? ??? ???-?? ?? ???? ??????? ?? ???? ???????? ??????? ?? ??? ??? ?? ???? ID (SPID) ?? ????? ???? ??? ???????? ?? SPID ?? ????? ???????? ?? ??? ??? ?? ?????????, ?????? ?? ??????? sense ??? ???? ??? ????????? ?? ?????? ???? ??? ???????, ???????? SPID ?? ????? ??? ???? ????? ?????? ?? ???? ???????? ???? ??? ?? ??????? ?? ?? ??????? ?? ?????? ?? ???? ?? ??? ?????? ??? ???? ??? ??????? ????????? ??? ?? ?? ???? ???????? ?? ???? ??? SQL ????? ?? ???????????? ??? ?? ??? ?? ??????? ???????? ?? ???? ??? ??????? ????????? ?? ?????? ??????? ?? ?? ?? ???? ??????? ????????? ?? ?? ?? ???? ??????? ?????????? ?? ?????? ???????? ?? ??? ??? ???? ??? ???? ???? ???????, ???? ?? emanate ?? ????? ??????? ?????????? ?? ??? ????????? ?? ??? ????????? ?? ?? ???? ?? ??????? ?? ????? ?? ???? ????

???? ???????

???? ?? ??????? ??????? ??????? ?????? (RDBMS) ?? ???????-?????? concurrency ?? ?? unavoidable characteristic ????? ?? ??? ??? SQL ????? ?? ??????? ?? ??? ???? ?? ???? ????????? ?????? ?? ?? SPID ???? ????? ?? ?? ??? ????? SPID ???? ?????? ?? ?????? ?????? ??? ?????? ??????? ???? ?? ?????? ????? ??????????, ??? ?????? ??????? ????? ??? ???? SPID ???? ?????? ???? ???? ??? ?? ?? releases ??????? ??, ????? ??????? ?? ?????? ????? ??? ??????? ???? ?? ????????? ?? ???? ???? ?? ??? ???? ??? ?? ??????? ??????? ?? ?? ?? ??? ???? ?????? ??????????? ?? ??? noticeable ?????? ?? ??? ?? ??? ?? ????????? ?? ????? ?? ?? ???? ???

??? ?????? ?? ???? ?? ????????? ?????? ????????? ????? ???? locks held ??? ??, ????, ???? ???????? ?? ???? ????????? ??? ??? ????????? ??? ?????? ?? ????????? ???? ?? (?? ??? ????? hints ????? ??? ???? ???), locks ????? ?? ??? ???? ?? ??? ????? ???? ?? held ???? ?????? ?? ?????? ?? ???? ?? ??? ????, ??? ?????? ??? ??? ?? ??? ?? ???? ???????? ????, ??????, ?? DELETE ?????, ?? ??? locks ??????, ????? ?? rolled ??? ??? ?????? ?? ?? ???? ?? ??? ?????? ???? ?? ??? ?? ???? ?? ?????? ?? ??? ?? ???? ?? ??? held ????

????????? ?? ???? ????????? ????????, ?? ??? ???? ?? ????? ??? locks ??? held ?????? ?? ??????, ????????? isolation ???? ?????? ????????? ?? ??????? ???? ?? ???? ?? hints ????? ??? ?????? ??? ?????? ?? ??? ????? ?? ??? ????? hints ?? ????????? isolation ????, ????? ???? ????? SQL Server ???????? ?????? ???:
  • ??????? ????? ??? ??????
  • ??????? ?????? ?? ?????? ?? ??????
  • ??????? ???
  • ??????? ??????
  • ?????? ??? ??????? ????? Isolation ???? ??????-??????
  • ????????? (??????? ?????) ????????
?? ?????? ?? ?????? ?? ?? ????? ?? ???????? ?????? ??????????? ?? ??? ?????? detrimental ??, ???? ??? ?? ????? ?? ????? ?????? ?? ???? ?? ?? ????:
  • ??? SPID ???? locks ???????? ?? ?? ??? ?? ?????? ?????? ???? ?? ???? ?? ??? ?? ??? ????????? ???? ?? ???? ?? ?????? ?? ??????? ?? ??? ?? ??? ??? ?? ?? ????, ????? ????? ?????? ??????? ???? ?? ???? ????
  • ??? SPID locks ?? ???????? ?? ?? ??? ?? ???? ?? ?? ?????? ??? ???? releases ??? ?? ?????? ?? ??????? ?? ??? ?? ?? ???? ????? ?? indefinitely ???????? ???????? ?? ????? ????? ???
?? ???? ???????? ??? ???, ????????? ?????? ???? ??? ??? ?? SPID releases locks ?? ??? ???? ???????, ?????? ???? ??? ?? ??? ' ???? ???????? ?? ??????? ?? ??? ??, ??? ??? ?? ?????? ????? ?? ??? ?? ?? SPID ???? ?? ??? ??? fluid ?? ???? ???? ?? ???? ?? ??? ?? ???????????? ?? ?????? ?????? SQL Server ?????????? ??????? ?? ???-??? SQL ?????? ?? ????? ???? ?? ??? ???? ?? ???? ???? ????? ?????? ???????? ?? ????? ???? ?? ??? ???? ?? ???? ??? ?? ???? ?????? ??? ???

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

????? ???????? ?? ?????? ?? ?????? counteract, ?? ??? ???? ??????? ?????????? constantly ?????? SQL ????? ?? ???????? ?? ?????? ?? ??????? SQL ????????? ?? ????? ?? ????? ?? ????????? ?????? ?? ???? ?? ????????? ?????????? ????????, ??? ?? ??? ?? ?????? ?? ????? ????? ??? ??????? SQL ????????? ?? ??? ??????? ?? ??? ?? ???????? ???? ?? ??? ???? ?? ??? ????? ?? ??? ????? ?????? ?? Microsoft ???????? ??? ?????:
271509How to monitor blocking in SQL Server 2005 and in SQL Server 2000
The scripts in this article will perform the tasks below. Where possible, the method for obtaining this information from SQL Server Management Studio is given.
  1. Identify the SPID (Session ID) at the head of the blocking chain and the SQL Statement.
    In addition to using the scripts in the previously mentioned Knowledge Base article, you can identify the head of the blocking chain by using features that are provided through SQL Server Management Studio. ??? ???? ?? ???, ????? ??? ?? ???? ?? ???? ????::
    • Right-click the server object, expandReports, expandStandard Reports?? ????-????? ????, ?? ???? ???Activity ? All Blocking Transactions. This report shows the transactions at the head of blocking chain. If you expand the transaction, the report will show the transactions that are blocked by the head transaction. This report will also show the "Blocking SQL Statement" and the "Blocked SQL Statement."
    • Use DBCC INPUTBUFFER(<spid>) to find the last statement that was submitted by a SPID.</spid>
  2. Find the transaction nesting level and process status of the blocking SPID.
    The transaction nesting level of a SPID is available in the @@TRANCOUNT global variable. However, it can be determined from outside the SPID by querying thesysprocessestable as follows:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    						
    The value returned is the @@TRANCOUNT value for the SPID. This shows the transaction nesting level for the blocking SPID, which in turn can explain why it is holding locks. For example, if the value is greater than zero, the SPID is in the midst of a transaction (in which case it is expected that it retains certain locks it has acquired, depending on the transaction isolation level).

    You can also check to see if any long-term open transaction exists in the database by using DBCC OPENTRANdatabase_name.

Gathering SQL Server Profiler Trace Information

In addition to the above information, it is often necessary to capture a Profiler trace of the activities on the server to thoroughly investigate a blocking problem on SQL Server. If a SPID executes multiple statements within a transaction, only the last statementthat was submitted will show in the report, input buffer, or activity monitor output. However, one of the earlier commands may be the reason locks are still being held. A Profiler trace will enable you to see all of the commands executed by a SPID within the current transaction. The following steps help you to set up SQL Server Profiler to capture a trace.
  1. Open SQL Server Profiler.
  2. ????? ???????????? ??,????? ????-????? ????, ?? ???? ???Trace.
  3. ????? ???????????tab, specify a trace name and a file name to capture the data to.

    ??????????The trace file should be written to a fast local or shared disk. Avoid tracing to a slow disk or network drive. Also make sure Server processes trace data is selected.
  4. ????? ????Events Selection??? ??, ?? ??? ???? ?? ??? ????? ????Show all events??Show all columnscheck boxes.
  5. ????? ????Events Selectiontab, add the Event types that are listed in Table 1 to your trace.

    Additionally, you may include the additional Event types that are listed in Table 2 for further information. If you are running in a high-volume production environment, you may decide to use only the events in Table 1, as they are typically sufficient to troubleshoot most blocking problems. Including the additional events in Table 2 may make it easier to quickly determine the source of a problem (or these events may be necessary to identify the culprit statement in a multi-statement procedure). However, including events in Table 2 will also add to the load on the system and increase the trace output size.
Table 1: Event types
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
Heading?????
Errors and WarningsException
Errors and WarningsAttention
Security AuditAudit Login
Security AuditAudit Logout
SessionsExisting Connection
Stored ProceduresRPC:Starting
TSQLSQL:BatchStarting

Table 2: Additional Event types
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
Heading?????
TransactionsDTCTransaction
TransactionsSQLTransaction
Stored ProceduresRPC:Completed
TSQLSQL:BatchCompleted
Stored ProceduresSP:StmtStarting
Stored ProceduresSP:StmtCompleted

For more information about using the SQL Server Profiler, please see SQL Server Books Online.

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

??????? ??????? ?? ??????? ?? ??? ??, ?? ??????? ????????? ?????? ?? ???? ????????? ?? ???? ???? ?? ???? ?? ??? ??? ?? ??????? ?? ??? ??????? ????? ???????? ?? ?? ???? ?? ??? ?? ??????? ?? ????? ???? ?? ??? ???? ?? ??? ????? ??? ?? ????? ????? ????? ????????? ???? 271509 ??? (???????? ????) ????? SPID ?? ??????? ?? ?????? ?? ???? ?????? ??? ?? ???? Profiler ????? ???? ?? ??? ????? ???? ?? ??? ?????? ????? ???

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

?? ???? ????sys.sysprocesses?????? heads ????? chains ?? ??? ????? ?? ???
??? ?? ????? ????????? ?? ??? ??? ??? ????????? ???? ??, ???? ???? ?? ??? "????? chains ?? ??? ?? SPID ????" ?????? ???? ???? SPID ????????? ?????? ??? ??????? ?? ??? ??? ?? SPID ?? ???? ???? ???? ???
SPIDs at the head of blocking chains
?? ???? ?????? ????????? ??? ?????? ??, ??? ??? ?? ????????? ???? ????? heads ?? ???? ?? ??? ?? ??? ??sys.sysprocesses?????? ?? ????????? ?? SPID ????? ??? ?? ????? ??? ?? ??????? ???? ?? ?? ??????
?? ???? ????sys.sysprocesses?????? ????? ???????? ?? ??? ?? SPID ?? ???? ??? ??????? ?? ????
????? ?? ????????? ???? ?? ??? ?????????? ??sys.sysprocesses?????:

??????

?? ????? ?? ???? ????? SPID ?? ?????? ?????? ??? ??????????, ??sleeping?????? ??????? ?? ?? SPID ???????? ????? ?? ???? ?? ?? ???? ?????? ?? ??? ????? ???? ?? ??? ????????? ?? ??? ????????? ?? ??? ??? Arunnable,?? ?????,sos_scheduler_yield?????? ?? ????? ???? ?? ?? SPID ?? ??? ??????? ?? ?????? ??? ????? ?????? ?????? ?? ??????? ????? ?? ????????? explanations ???? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
??????????
background:SPID ???? ????????? ?????, ???? deadlock ??? ?? ??? ???
SleepingSPID ??? ????????? ???? ??? ?? ????? ?? ????? ???? ?? ?? SPID ????????? ??? ?? ?? ???? awaiting ???
?? ?????? scheduler ?? SPID ??? ?? ??? ???
RunnableSPID scheduler ??? ??????? ???? ?? ??? ????????? ?? ??? ?? ?? ?? scheduler runnable ???? ??? ???
Sos_scheduler_yieldSPID ?? ??? ??, ????? ??? scheduler ??? ??????? ???? ?? ??? ???? ???? SPID ???? ?? ??? scheduler ?? ???? ??? ?????? yielded voluntarily ???
???????SPID ??? ?????, ???? ??? ??????? ???? ?? ??? latch ?? ??? ????????? ?? ??? ???
??????SPID ?? ????????? ?????? ??? ???
Defwakeup?? ????? ???? ?? ?? SPID ????? ???? ?? ????????? ??? ?? ?? ?? ?????? ?? ??? ????????? ?? ??? ??? The waitresource field should indicate the resource in question.

Open_tran

This field tells you the transaction nesting level of the SPID. If this value is greater than 0, the SPID is within an open transaction and may be holding locks acquired by any statement within the transaction.

Lastwaittype, waittype, and waittime

Thelastwaittypefield is a string representation of thewaittypefield, which is a reserved internal binary column. ???waittypeis 0x0000, the SPID is not currently waiting for anything and thelastwaittypevalue indicates the lastwaittypethat the SPID had. ???waittypeis not zero, thelastwaittypevalue indicates the currentwaittypeof the SPID.

For a brief description of the differentlastwaittype, ??waittypevalues, see the following article in the Microsoft Knowledge base:
822101Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
For more information aboutsys.dm_os_wait_stats, see SQL Server Books Online.

Thewaittimevalue can be used to determine if the SPID is making progress. When a query against thesys.sysprocessestable returns a value in thewaittimecolumn that is less than thewaittimevalue from a previous query ofsys.sysprocesses, this indicates that the prior lock was acquired and released and is now waiting on a new lock (assuming non-zero waittime). This can be verified by comparing thewaitresourcebetweensys.sysprocessesoutput.

Waitresource

This field indicates the resource that a SPID is waiting on. The following table lists commonwaitresourceformats and their meaning:
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
??????????????????:
??????DatabaseID:ObjectID:IndexIDTAB: 5:261575970:1
In this case, database ID 5 is thepubssample database and object ID 261575970 is thetitlestable and 1 is the clustered index.
PageDatabaseID:FileID:PageIDPAGE: 5:1:104
In this case, database ID 5 ispubs, file ID 1 is the primary data file, and page 104 is a page belonging to thetitlestable.

To identify the object id that the page belongs to, use the DBCC PAGE (dbid, fileid, pageid, output_option) command, and look at the m_objId. ?????? ?? ???::
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
?????DatabaseID:Hobt_id (Hash value for index key)KEY: 5:72057594044284928 (3300a4f361aa)

In this case, database ID 5 is Pubs, Hobt_ID 72057594044284928 corresponds to non clustered index_id 2 for object id 261575970 (titlestable). Use the sys.partitions catalog view to associate the hobt_id to a particular index id and object id. There is no way to unhash the index key hash to a specific index key value.
??????DatabaseID:FileID:PageID:Slot(row)RID: 5:1:104:3

In this case, database ID 5 is pubs , file ID 1 is the primary data file, page 104 is a page belonging to the titles table, and slot 3 indicates the row's position on the page.
CompileDatabaseID:ObjectID [[COMPILE]]TAB: 5:834102012 [[COMPILE]] This is not a table lock, but rather a compile lock on a stored procedure. Database ID 5 is pubs, object ID 834102012 is stored procedure usp_myprocedure. See Knowledge Base Article 263889 for more information on blocking caused by compile locks.
Other columns

The remainingsys.sysprocessescolumns can provide insight into the root of a problem as well. Their usefulness varies depending on the circumstances of the problem. For example, you can determine if the problem happens only from certain clients (hostname), on certain network libraries (net_library), when the last batch submitted by a SPID was (last_batch), and so on.
Examine the DBCC INPUTBUFFER output.
For any SPID at the head of a blocking chain or with a non-zero waittype, the blocking script will execute DBCC INPUTBUFFER to determine the current query for that SPID.

In many cases, this is the query that is causing the locks that are blocking other users to be held. However, if the SPID is within a transaction, the locks may have been acquired by a previously executed query, not the current one. Therefore, you should also view the Profiler output for the SPID, not just the inputbuffer.

???:Because the blocking script consists of multiple steps, it is possible that a SPID may appear in the first section as the head of a blocking chain, but by the time the DBCC INPUTBUFFER query is executed, it is no longer blocking and the INPUTBUFFER is not captured. This indicates that the blocking is resolving itself for that SPID and it may or may not be a problem. At this point, you can either use the fast version of the blocking script to try to ensure you capture the inputbuffer before it clears (although there is still no guarantee), or view the Profiler data from that time frame to determine what queries the SPID was executing.

Viewing the Profiler Data

Viewing Profiler data efficiently is extremely valuable in resolving blocking issues. The most important thing to realize is that you do not have to look at everything you captured; be selective. Profiler provides capabilities to help you effectively view the captured data. ????????dialog box (on the???????? ??,???), Profiler allows you to limit the data displayed by removing data columns or events, grouping (sorting) by data columns and applying filters. You can search the whole trace or only a specific column for specific values (on the?????????? ??,??????). ?? ???? ???? SQL Server ?????? ?? ??? Profiler ???? ?? ???? ???? ??? (?????????? ??,?? ??? ??? ???????? ????-????? ????, ?? ???? ?????????) ?? ??? ?? ??????? SQL ?????? ?? ??????

Careful ???? ?? ???? ?? ?????? ?? ???? ?? ????? ?? ????? ????? ?? ????????? ????????? ????. ??? ?? ?? ????? ?? ?? ?????? ????? ???? ???, ?? ?? risk ????? ??????? ???? ?? ??? ?????? ??? ?? ???? ??? ??? ???? ????? ?? ??? ??? ?????? ????? ?????? ?? ???? ?? ?????? (?????????? ??,?? ??? ??? ??????) ?? ??? ?? ????? (?????????? ??,?????) ??? ???? ?? ????? ???? ????? ?? ????? ???? ?? ????? ?? ??? ??, ?? ????????? ???? ?????? ??? ?? ????? ?????? ???? ?? ??? ???? ??, ??? ??? ???? ??? ???? ????????? ????? ?? ?????? ?? ??????? ??????? ?? ?? ??? ??? ???? ?? ??? ???? ?? ??? ?????? ????? ???? ???? ??? ?? ???? ????

???? ?????:
  • ??????? ?????? ?? ???? ????????? ????? ???????? ?? ??? ?? SPID ?? ???? ?????
    ??? ????? ???????? ?? ??? ?? ?? ?? ????? SPID ?? ??? ????? ???? ?? ?????? (?????????? ??,???; ?? ???? ???????????? SPID ??? ????????? ????)? ?? ??? ??? ???? SPID ????? ?? ??? ?? ???? ?? ???? ??? ?? ????????? ???? ?? ?? ???? ?? ???? ???? ??? ?? ????????? ??????? ????? ???, ?? ????? ?? ????? ???? ?? ??? ????????? ??????? ???? ??? ??? ??????, ?? ??? ?? ???? ????????????? ???, ?? ??? ????? ??????, COMMIT, ?? ?????? ?? ????????? ?? ?????????? ?????open_tran?? ???sysprocesses????????? ???? ?? ?? ??? ????????? ????? ??? ???? ?? ??? ?????? ??? ????????? ?????? ?? ????????? ?????? ????? ???? ????? ???? SPID locks ?? ?? ?? ????????? ???? ?? ??? ????

    ??? ????, ????? ??????? ?? ???? ?? ????? ???? ???? ????? ??????? ???? ?? ??? ???? ?? ???? ?? ????? ?????, ??? ?? ?????? ??? ????? SPID ???????? ???????????? ???? ???, ?? ???????SP: ?????????,SP: ??????????;SQLBatch, ??RPC????? ????????? ??? ????????? ???? ?? ?? ???? ?? ????? ????? ?? ??? ???? SP ????? ??????
  • ???? chains ???????? ?? ??? ?? SPID ?? ??? ?????? ?? ???? ???
    ??? ?? ????? ???, ??????? ????? ????????????? ?????? ???????? ??? ????????? ?? ???? ???? ???? ???????? ???????? ??????? ?? ??? ??? ?? ??????? ??? ??? ?? ???? ???? To determine why the query is performing slowly, view theCPU,????, ??Writescolumns, as well as theExecution Plan??????

Categorizing Common Blocking Scenarios

The table below maps common symptoms to their probable causes. The number indicated in theScenariocolumn corresponds to the number in the "Common Blocking Scenarios and Resolutions" section of this article below. TheWaittype,Open_Tran, ????????columns refer tosysprocessesinformation. TheResolves?column indicates whether or not the blocking will resolve on its own.

?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
ScenarioWaittypeOpen_Tran??????Resolves?Other Symptoms
1Non-zero>= 0runnableYes, when query finishes.??? ?? Physical_IO, ??/CPU ?? Memusage ????? ????? ?? ?????? ?????? ?? ??? ???? ????? ???? ?? ??????
20x0000> 0sleeping????, ????? SPID killed ?? ???? ????????? ?? ??? ????? Profiler ?????? ???????? ?? ???? ???? ?? ????? ????, ?? SPID ?? ??? ????? ?? ??? ???? ?? ???? ???
30x0000> = 0runnableNo. ????? ?? ???? ?? ?? ??????? ??? ????????? fetches ?? ??????? ??? ?? ???? ??? SPID killed ???? ?? ???? ??, ????? ?? 30 ????? ?? ???? ???????open_tran= 0, and the SPID holds locks while the transaction isolation level is default (READ COMMMITTED), this is a likely cause.
4Varies>= 0runnableNo. Will not resolve until client cancels queries or closes connections. SPIDs can be killed, but may take up to 30 seconds.Thehostnamecolumn insysprocessesfor the SPID at the head of a blocking chain will be the same as one of the SPID it is blocking.
50x0000>0rollbackYes.An attention signal may be seen in the Profiler trace for this SPID, indicating a query timeout or cancel has occurred, or simply a rollback statement has been issued.
60x0000>0sleepingEventually. When Windows NT determines the session is no longer active, the SQL Server connection will be broken.Thelast_batchvalue insysprocessesis much earlier than the current time.

Common Blocking Scenarios and Resolutions

The scenarios listed below will have the characteristics listed in the table above. This section provides additional details when applicable, as well as paths to resolution.
  1. ?? ??????? ??? ?? ???? ??? ?? ???? ????? ?? ??? ?? ???? ???????? ??? ?? ??? ??????

    ???????????:
    ?? ?????? ?? ????????? ?????? ?? ?????? ?????? ????????? ???? ?? ????? ?? ??? ????? ?? ??? ??? ?????? ???, ?? ???? ????????? ?????? ?? ?? ???? ?? ???? ???????? ?????? ??, ?? ?? ??? ??? ??? pursue ???? ?? ???????? ??? ??????? ????-???? ?????? ???? ?????? ?? ?????? ?? ??????? ?? ??? ????? Microsoft ???????? ???? ?????:
    243589????-???? ???????? ?? SQL Server 7.0 ?? ??? ?? ????????? ?? ?????? ???? ????
    ??? ????????? ???????? ?????? ??????, ?? ??? ????? ???????? ???? ?????:
    224587TO HOW: ???????????? SQL ????? ?? ??? ????????? ????????
    ???? ??????? ?? ???, ????????????? ??????? ?? ???? ???? ????????????? MSDN ??? ???? ?? SQL Server 2008 ???????? ?????? ????:
    HTTP://MSDN.Microsoft.com/en-us/library/ms187830.aspx
    ??? ???? ??? ?? ???? ???? ?????? ?? ?? ???? ???????????? ?? ????? ?? ??? ?? ?? ???????? ???? ???? ?? ???? ??, ?? OLTP ?? ?? ???? ?? ????? ???? ?? ??? ??? ?????? ?????? ?????? ????????
  2. ???????? ???? Sleeping SPID ?? ????????? ???????? ???? ?? ????? ???? ???

    ???????? ?? ?? ?????? sleeping ?? awaiting ????, ??? ?? ????? ????????? ???? ???????? ?? ?? ???? SPID ?????? ????? ?????? ?? ???? (@@ TRANCOUNT,open_tran??sysprocesses) ????? ?? ???? ??? ?? ?? ?? ???? ?? ??? ????????? ??? ?????? ??????? experiences, ?? ?????? ??/?? COMMIT ??? ?? ?????? ?????? ?? ?? ???? ??? ???? ???? ???? issues ????? ?? ??? SPID ??????? ?????? ???????? ?? ???? ???? ??, ??? ????? ??????? ?????? ?? ??? ?? ???, ?????? ???? ????? ???????? ??? ?? ???? ??? ??? ?? ????????? ?? ????? SQL ????? ?? ??? ????????? ?? ???? ???? ?????? ?? rolled ???? ????? ?? ???? ???? ?? ?? ??? ?? ?????? ???? ???? ?? ??? ??? ????????? ??, ?? ??? ?????????? ??? ?????? ???????? ?? ???? ???? ????? ???? ????? ????? ????? ?? ??? ??? Profiler ????? ??? SPID ?? ????

    ?? demonstrate ???? ?? ??? ?????? ???????? ?? ????? ??????? ?????? ?????:

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    ?? ?????? ?? ????????? ?? ??? ??, ??? ??? ????? ???????? ??????? ??? ?????? ?? ???? ???? ??? ??, ?? ??? SELECT @@ TRANCOUNT ??????? ?? ?? ?? ????????? ???????? ???? ??? ???? ?? ???? ?? ??? ?? DELETE ?? ?????? ??????, ?? ????? ???? ?? ???? HOLDLOCK ???? ??? ?? ?? ??? ????, ??? locks ??????? ??? ?? ??? ?? ???? ?? held. ?????? ???, ?? ?? ??? ???? ?????? ??????? ???? ??? ?? ?? locks ?? ????????? ??? ???? held ?? ???? ??? ?? ?? held ?? ??? ?? ??? ???? ???? ??? ???

    ??????:

    • ????????? ??? ?? ????????? ???????? ???? ???????? ???? ????, ?? ?? ?????? ??? ?? ??? ?? ???????? ????? ????????? ?????? ?? ???? ?? ???? ??? ?? ?? ??? ?? ?? ??? accomplished ?? ?? ???? ???:
      1. ??????? ????????? ?? ?????? ??????, ??? ??? @@ TRANCOUNT ????? ???? > 0 ??? ?? ?????? ?? ????? ??? ?? ??????? ????????? ???? ???? ???? ????????? ?????? TRAN ?? ?????? ?? ?????? ??, ??????? ??? ?? ????? ??? ???????? ????????? ??????? ????????? ?? ??????? ?? ???? ??? ????????? ??????? ?? ???? ????? ??? ?? ???? ?????? ?? ???? ???? ?? ??? ??, ??? ????????? ????????? ?? ??? ????? ??????? ??? ????????? ??? ?? ????????? ?? ????? IF ?? ???? ???? ?? ??? ??? @@ ?????? <> 0 ?? ????????? ??????, ??? ?????? ??? ?? ?????? ??? ????????? ???? ???? ??????
      2. ??????? ?? ???, ?? ???? ?? ???????? ???????????? ????????? ??????? ???? ?? ??? ?????? ??? ?? ??? ???? ??? ????? ??? SET XACT_ABORT ???? ?? ????? ????? ??? ??-???? ?????? ?? ???? ??? ?? ?????? ????? ???? ?? ???? ????????? ?????? ?? ??????? ?? ??? ???????? ????? ????? ??? ?? ??? ?? ?????? ?? ???? ????? T-SQL ??? ????????? ???? ???? ??????
      3. ??? ??????? ????? ?? ?? ???? ???????? ?? ?????? ?? ??????? ???, ???? ??? ???-?????? ?????????, ?? ?????? ???? ?? ???? ???? ?? ?? ???? ????????? ??? ??????? ?????? ????? ???? ?? ??? ??, ?? ??????? ??? ?? ??????? ?????? ?? ????? ???? ??? ?? ???? ?? ??????? ????????? appropriately ????????? ?? ????? ???? ?? ??? ??????? ???? ?? ?? ?? ?????? ?? ?? ????? ??????? ?????? ?? ????? ????, ?????? ??????? ?????? ????? ???? ?? ???????????? ????? ??? ??? ??? ?? ???? ?? ???? ????????? ??? SQL ????? ???????, ???? ????? ????
      4. ??? ??????? ?????? ????? ??, ?? ?????? ????? ?? SQL Server 2000, ??????? ???????? MDAC 2.6 ?? ??? ??? ???????? ?? beneficial. MDAC ????? ?? ?? ??????? ??? ?????? ?? ODBC ??????? ?? OLE DB ??????? ?? ??? ?? ??????? ???? ???? ?? ???? "?????" ???? ?? ???? reused ??? ?? ??? sp_reset_connection ???? ?? ??? ??? ????? ??????? ????????? (??????? ????????? ?????? ??????? DTC ????????? ???????? ???? ???) aborts, ??????? ???????, SET ??????, ?? ??? forth ?? ????? ???? ??? ????? ??? ?? ??????? ?? ????? ???? ?? ?? ?? ??? ?? reused ?? ??????? ???, ????? ?? ?????????? ??? ????????? ?? ????? ?? ?? ??????? ??????? ??? ?? ??? ?????? ???, ????? ?? ?? ???? ?? ???? ???? ?? ???? reused ?? ?????, ???-?? ??? ?? ????? ????????? ???? ?? ????? ?? ??? ???? ??? ??? ??????? reused ???? ??, ????????? ????? ?? ?????? ?? ??????? ??? ?? ??????? ??? ?? ????? ???? ??? ??? ??????, ?? ???? ?????? ?????? ??? ????????? ?????? ???? ?? ?? ??????? ????? ?? ???? ?? ??? SET XACT_ABORT ???? ?? ????? ???? ?? ??? ??????? ????????? ?? ??? ???????? ???
    • ?????? ???, ?? ???? ????????? ?????? ?? ?? ???? ?? ?? ???????? ?????? ??, ?? ?? ??? ??? ??? pursue ???? ?? ???????? ??? ??? ?????? ???????? ??? diminished ?? ???? ??, ?? ???? ???? ?? ?????? ??????? ???? ???? ??? ????????? ??????? ????? ?? ???????? ?? ???? ???? ??? ????? ???? ?????????? ?? ????? ?? arise ??, ????? ?? ?????? ?? ??????????? ?? ??????? ?? ??? ?? ?? ??? ?? ?? ???? ???
  3. ???????? ???? ??? SPID ?? ????? ???? ?? ??? ?? ?????? ??????? ????????? ????? ?????? ?? ??? ????????? Fetch ???? ???

    ????? ?? ??? ??? ?????? ??? ??? ??, ?? ??? ??? ????????? ?????? ????? fetch ??? ?????? ????????? ????? ???? ?? ???? ??? ????????? ??? ?????? ?? ??? ????????? fetch ???? ??, locks ???? ???????????? ?? ????? ?? ??? ??, ???? ?? ???? ?? ???? ???? ??? ?? transparently submits SQL ???? ???? ??? ????????? ?? ????? ?? ??? ??? ????? ????? ?? ???, ????????? ?????? fetch ??? ?????? ?????????? ??? ?? ???? ?? (?? ??? ?? ??? ???? ?? ??? ???????? ???? ?? ?? ????), ????????? ?????? ?? ?? ???? ??? ?????? ?? ???? ??? ?? ?????? ?? ????, ?? ??? ??? ?????????? ?? ??? ??????-?????? ??????? ?? ??? ????????? poorly-behaved ????? ?? ???? ????

    ???????????:

    ????????? ?????? ?? ??? ????????? ?? ????? ???? ?? ??? fetch ???? ?? ??? re-written ???? ??????
  4. ??? ?????? ???????/????? Deadlock ?? ???? ????? ?? ???

    ??? ???????? deadlock ???? ?? ?????? ???? ?????? deadlock ???? ?? detectable ????? RDBMS ????? ???????? ?? due to the fact ?? SQL ????? ????? ?? ???? ?? ?????? deadlock ??? ????? ??? Deadlock ?? ???? ??? ??????? ????????? ???? ??, ??? ?? SQL Server ?? ???????? ???? ??? ????? ?? ?? ?????? ??? ?? ?? ?? ?? ???? ??, ?? ???? ????? ????????? ??? ?? ???? ?? ???? ????

    1. ??????/????? ?? ??? ??? ??????? ?? ??? Deadlock ?????? ?????
      ??? ??????? ?????? ??????? ?????, ?? ???????? ?? ???? ??? ????? ?? ??? ??, ?? ????? ?????? deadlock ?? ???? ??? Brevity, ?? ??? ?? "dbproc" ????? ???? ?? ??????? ?? ??????? ??????? ???????

       SPID1------blocked on lock------->SPID2
        /\                         (waiting to write results         
        |                           back to client)
        |                                 |
        |                                 |                      Server side
        | ================================|==================================
        |     <-- single thread -->       |                      Client side
        |                                 \/ 
       dbproc1   <-------------------   dbproc2
       (waiting to fetch             (effectively blocked on dbproc1, awaiting
        next row)                     single thread of execution to run)
      								
      ??? ????? ?? ??????, ??? ?? ??????? ????????? ????? ?? ?? ??????? ?????? ?? submits asynchronously dbproc1 ?? ???? SQL ????????? ??? ???? ???? ?? ?? ??? ??? ???? ?? ???? ???? ??? ?? ??? ??? ?? ????????? ????? ????????? ??? dbproc2 ?? ???? ???? SQL ???????? submits, ?? ?????? ??? ?? ???? ?? ????????? ??????? ???? ?? ??? awaits. ?? ???? ??????? ???? ??? (whichever dbproc ???? ??--???? dbproc1 ??), ??? ?? dbproc ?? ??? ???? ?? ????? ???? ?? ??? ??????? ???? ??? ?? fetches ???????? ?? dbproc1 ?? SPID1 ????? (??????? ?? ??????? ??? asynchronously ?? ?? ??? ?????) ?????? SPID2 held ?? ??????? ???? ??? ?? ????? ??, dbproc1 ????? ????????? indefinitely ?? ???? ?? ???? SPID2 ????? ?? ??????? ???? ???? ??? ??, ????? ???? ???????, dbproc2 ???? ?? ??? ???? ????? ?? ?????? ???? ??? ???????, dbproc2 ??????? ?????? ????? ?? ?? ????????? ??? ?? dbproc1 ?? ??? ??? ????? ??? ?? ????????? ?? ???????? ?? ??? ????? dbproc1. ?? ?????? ??? deadlock ?? SQL ????? ?? ??? ????? ?? ???? SQL ????? ?????? ????? ???????? ?? ???? ?? ?? ?? ??????? ?? ???? ???? ?? ?????
    2. ??????/????? ?? ??????? ?? ????? ??? ????? Deadlock ?????? ????

      ??? ??????? ?? ???????? ??????? ?? ??? ?? ??? ????? ????? ??, ?? ?? ?? ?????? deadlock ?? ?? variation ??? ?? ?? ???? ????? ??? ?? ????? ?? ?? ??? ??? ???

      SPID1------blocked on lock-------->SPID2
        /\                         (waiting on net write)        Server side
        |                                 |
        |                                 |
        | INSERT                          |SELECT
        | ================================|==================================
        |     <-- thread per dbproc -->   |                      Client side
        |                                 \/ 
       dbproc1   <-----data row-------   dbproc2
       (waiting on                     (blocked on dbproc1, waiting for it
        insert)                         to read the row from its buffer)
      								
      ?????? dbproc2 ?? SPID2 ??????-??-???-??????? ???? ??? ?? ??? dbproc1 ???? ?? ??? ??? ?????, ??????, ?? ??? ??? ???? ?? ?????? ?? ???????? ?????? handing ???? ?? intention ?? ?? ?? ??? ???? ??? ??? ??? ??? ?? ?? ???? ?? ???????? ??? ?? ????? ?? ?????? ???, ?????? ?? ???? ???? ??? ?????, SPID1 (???????? ????, ??????, ?? DELETE ????) ?? ??? ????? SPID2 ?????? held ??????? ?? ???? (?? ??? ?? ??? ??)? SPID2 ??????? dbproc2 ???? ?? ??? ??? ?????? ?????? ????? ??? ?????? ??? ?? ???? dbproc1 ??????? ???? ?? ??? Dbproc2 ??? ????? ???? ??, ????? finds dbproc1 ?????? ?? (??? ????? ?? ?? SPID1 ??????? ?????, ??? ?? SPID2 ????? ???? ??? ?? ?? ?????? ???? ?? ??? ????????? ?? ??? ??)? ?? ????? ??, dbproc2 ?? ?? ????????? ??? ?????? ????? ???? ??? ????? SPID (SPID1) ?????? ????? ?? ??????? ???? ?? SPID2 dbproc1. ???, ?? ?????? ??? deadlock ?? SQL ????? ?? ??? ????? ?? ???? SQL ????? ?????? ????? ???????? ?? ???? ?? ?? ?? ??????? ?? ???? ???? ?? ?????
    A ?? B ????? ?????? ??? ????????? ???????? ?? ??????? ???? ????? ?? ??????? ?????? ??? They must code applications to handle these cases appropriately.

    ??????:

    Two reliable solutions are to use either a query timeout or bound connections.

    • Query Timeout
      When a query timeout has been provided, if the distributed deadlock occurs, it will be broken when then timeout happens. See the DB-Library or ODBC documentation for more information on using a query timeout.
    • Bound Connections
      This feature allows a client having multiple connections to bind them into a single transaction space, so the connections do not block each other. For more information, see the "Using Bound Connections" topic in SQL Server 7.0 Books Online.
  5. Blocking Caused by a SPID That Is in a "Golden," or Rollback, State

    A data modification query that is KILLed, or canceled outside of a user-defined transaction, will be rolled back. This can also occur as a side effect of the client computer restarting and its network session disconnecting. Likewise, a query selected as the deadlock victim will be rolled back. A data modification query often cannot be rolled back any faster than the changes were initially applied. For example, if a DELETE, INSERT, or UPDATE statement had been running for an hour, it could take at least an hour to roll back. This is expected behavior, because the changes made must be completely rolled back, or transactional and physical integrity in the database would be compromised. Because this must happen, SQL Server marks the SPID in a "golden" or rollback state (which means it cannot be KILLed or selected as a deadlock victim). This can often be identified by observing the output ofsp_who, which may indicate the ROLLBACK command. The??????column ofsys.sysprocesseswill indicate a ROLLBACK status, which will also appear insp_whooutput or in SQL Server Management Studio Activity Monitor.
    ???????????:

    You must wait for the SPID to finish rolling back the changes that were made.

    ??? ????? ?? ?? ???? ?? ???????? ?? midst ??????? ?? ???????? ???? ?? ????????????? ??? ??? ?? ?????, ?? ?? ???? ????? ????? ???? ?? ?? ?? ??? ????? ????????? ??????? ??? ???? ???? ????????? ????????????? essentially ???? ?????? ?? ????? ??-???? ????????????? ?? ??? ??? ????????? ?? ??? ???? ??, ?? ??????? ?? ???? ?? ????? ????? ????? ???? ??? ??????, ?????? ?????? ??? ?? SPID ????? ?? ??? ???? ????? ?? ????? ???? counterproductive.

    ?? ?????? ?? ????, ?? ??? ???? ??? ??? ???????? ????, ??????, ???? ?? OLTP ???????? ?? ?????? ????? ?? ????? ???????? ?? ?????? ??? ???? ??, ?? ??????? ?? ??????? ?? ????? ??? ???????? ???
  6. ??? Orphaned ??????? ?? ???? ????? ?? ???

    ??? ??????? ????????? traps ?? ??????? ?????????? ???????? ????, ????? ?? ??? ??????? ?? ???? ?? ???? ????? ???? ???? ?? ???? ??? ????????? ?? ???????? ????? ?? ???????????? ???, ?? ????? ???? ?? ??? ??????? ??? ?? ????? ??, ?? ??? ?? locks ??????? ??? ?? ??? ??? ?? ?? ???? ??????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
    137983??? SQL Server orphaned ??????? ???????? ?? ?????? ???? ????

    ???????????:

    ???? ???????? ?? appropriately ??? ??? ???? ??????? ????????? ?? ?????????? ?? ???? ??, ??? ?? SPID KILL ???? ?? ????? ?? ??? ?? ???? ???? KILL ???? ????? ?? ??? ??? SPID ??? ???? ??? ?????? ?? ???, SPID 9 ?????, ?? ??? ????? ????? ????? ????:

    KILL 9
    						

    ???:KILL ???? ?? ????? ???? ?? ???, ?????? KILL ???? ?? ??? ???? ???? ?? 30 ????? ?? ???? ????

???????? ???????? ??? ????????? Involvement

?? ??? ????????? ?????? ????? ???? ?????-???? ???????? ?? ?????????? ???????? ?? ???? ???? ?? ??? ??? tendency ?? ???? ??? ??? ??, ?? ????? ?? ???? ?????? ?? ??? ?? ???? ??, ?? ??? ?? ????? ??????? ????????? ?? ??? submits ??????? ?? ??????? ?? ??? ?? ????????? ????? absorb ?? ???? ???? ??? ???? ?? ??????? ????????? exposes ??????? ??? ??? ?? ???, ????????? ?????? ?? ???? ??? nonetheless ????? ?? ????? ???? ??? ????????? ?? ????????? ?? ???? ??????? ?????? ???????? ?? ???? ??? ???? SQL ??? ?? ????? ???????? ?? ??? ?????? ??, matter ?? ??????? ???????, ???? ??? ????????? ?? ??????, ?? on ???? ?????????? ????? ?? ??????? ???????, ?????? ????????, ?????? ???????, ?????? ???? ?? ??? ??? ?????? ???????? ?? ?????? ???? ???? ??, ??? ????????? ?????? ???? ?? ???? ?? resolvable. ?? potential SQL ????? ?? ??? ????? ??? ?? ??????????-??????? OLTP ???????? ?? ??? ??? ????????? ?????????? ????? ?? ??? ???? ?? ???? closely examined ???? ??????

Vital ?? ??????? ?? ????????? ?? ??????? ?? ??????? ??? ?? ????? ????? care exercised ???? ?? ???? ??? Particular ??? ?????? ?????, isolation ???? ?? ????????? ?? ????? ????? ?? ?????????? ???????? ?????? ?? ???? ???????? ?????? ?? ????????? ???? ????? ????? ???? ?? lightweight. ??????? ?? ????? ??????? discipline exercised ?????? ???? ?? ???? ??? ??? ?? ??? ???? ??, ?? ???? ?? ?? ????????? ???????????? ?? ?? ?????? ??? ????????? ???????? ?? ??? ????? ?? ???? ??, ????? ???????? ?? ????? ???? ?????????? ?? upward ?? ?????? ?? ??? ??? ???????????? ??????? ?? ???? ???

????????? ?? ???? ?? ?????? ??????, ?? ??? Microsoft SQL Server ?? ?? ???? ??? ?????, ?? ???????? ?? ??? simultaneous ???????????? ?? ?? ???? ?? ?????? ???? ??? ??????

???

???? ID: 224453 - ????? ???????: 02 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
??????: 
kbhowto kbtshoot kbexpertiseinter kbinfo kbmt KB224453 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:224453

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

 

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