???? ?? ??????? ??????? ??????? ?????? (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 ???????? ??? ?????:
271509
(http://support.microsoft.com/kb/271509/
)
How 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.
- 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>
- 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
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.
- Open SQL Server Profiler.
- ????? ???????????? ??,????? ????-????? ????, ?? ???? ???Trace.
- ????? ???????????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. - ????? ????Events Selection??? ??, ?? ??? ???? ?? ??? ????? ????Show all events??Show all columnscheck boxes.
- ????? ????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 Warnings | Exception |
| Errors and Warnings | Attention |
| Security Audit | Audit Login |
| Security Audit | Audit Logout |
| Sessions | Existing Connection |
| Stored Procedures | RPC:Starting |
| TSQL | SQL:BatchStarting |
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
| Heading | ????? |
|---|
| Transactions | DTCTransaction |
| Transactions | SQLTransaction |
| Stored Procedures | RPC:Completed |
| TSQL | SQL:BatchCompleted |
| Stored Procedures | SP:StmtStarting |
| Stored Procedures | SP: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 ???????? ????? ?? ???? ?? ?? ???? ?????? ?? ??? ????? ???? ?? ??? ????????? ?? ??? ????????? ?? ??? ??? A
runnable,
?? ?????,
sos_scheduler_yield?????? ?? ????? ???? ?? ?? SPID ?? ??? ??????? ?? ?????? ??? ????? ?????? ?????? ?? ??????? ????? ?? ????????? explanations ???? ???
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
| ?????? | ???? |
|---|
| background: | SPID ???? ????????? ?????, ???? deadlock ??? ?? ??? ??? |
| Sleeping | SPID ??? ????????? ???? ???
?? ????? ?? ????? ???? ?? ?? SPID ????????? ??? ?? ?? ???? awaiting ??? |
| ?? ??? | ??? scheduler ?? SPID ??? ?? ??? ??? |
| Runnable | SPID scheduler ??? ??????? ???? ?? ??? ????????? ?? ??? ?? ?? ?? scheduler runnable ???? ??? ??? |
| Sos_scheduler_yield | SPID ?? ??? ??, ????? ??? scheduler ??? ??????? ???? ?? ??? ???? ???? SPID ???? ?? ??? scheduler ?? ???? ??? ?????? yielded voluntarily ??? |
| ??????? | SPID ??? ?????, ???? ??? ??????? ???? ?? ??? latch ?? ??? ????????? ?? ??? ??? |
| ?????? | SPID ?? ????????? ?????? ??? ??? |
| Defwakeup | ?? ????? ???? ?? ?? SPID ????? ???? ?? ????????? ??? ?? ?? ?? ?????? ?? ??? ????????? ?? ??? ??? The waitresource field should indicate the resource in question. |
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 waittimeThe
lastwaittypefield is a string representation of the
waittypefield, which is a reserved internal binary column. ???
waittypeis 0x0000, the SPID is not currently waiting for anything and
the
lastwaittypevalue indicates the last
waittypethat the SPID had. ???
waittypeis not zero, the
lastwaittypevalue indicates the current
waittypeof the SPID.
For
a brief description of the different
lastwaittype, ??
waittypevalues, see the following article in the Microsoft
Knowledge base:
822101
(http://support.microsoft.com/kb/822101/
)
Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005
For more information about
sys.dm_os_wait_stats, see SQL Server Books Online.
The
waittimevalue can be used to determine if the SPID is making progress.
When a query against the
sys.sysprocessestable returns a value in the
waittimecolumn that is less than the
waittimevalue from a previous query
of
sys.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 the
waitresourcebetween
sys.sysprocessesoutput.
WaitresourceThis field indicates the resource
that a SPID is waiting on. The following table lists common
waitresourceformats and their meaning:
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
| ?????? | ?????? | ??????: |
|---|
| ?????? | DatabaseID:ObjectID:IndexID | TAB:
5:261575970:1
In this case, database ID 5 is thepubssample database and object ID 261575970 is thetitlestable and 1 is the clustered index. |
| Page | DatabaseID:FileID:PageID | PAGE:
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. |
| Compile | DatabaseID: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. |
The remaining
sys.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 the
Scenariocolumn corresponds to the number in the "Common Blocking
Scenarios and Resolutions" section of this article below. The
Waittype,
Open_Tran, ??
??????columns refer to
sysprocessesinformation. The
Resolves?column indicates whether or not the blocking will resolve on its
own.
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
| Scenario | Waittype | Open_Tran | ?????? | Resolves? | Other
Symptoms |
|---|
| 1 | Non-zero | >=
0 | runnable | Yes, when query finishes. | ??? ?? Physical_IO, ??/CPU ?? Memusage ????? ????? ?? ?????? ?????? ?? ??? ???? ????? ???? ?? ?????? |
| 2 | 0x0000 | > 0 | sleeping | ????, ????? SPID killed ?? ???? ???? | ????? ?? ??? ????? Profiler ?????? ???????? ?? ???? ???? ?? ????? ????, ?? SPID ?? ??? ????? ?? ??? ???? ?? ???? ??? |
| 3 | 0x0000 | > = 0 | runnable | No.
????? ?? ???? ?? ?? ??????? ??? ????????? 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. |
| 4 | Varies | >= 0 | runnable | No.
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. |
| 5 | 0x0000 | >0 | rollback | Yes. | 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. |
| 6 | 0x0000 | >0 | sleeping | Eventually.
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.
- ?? ??????? ??? ?? ???? ??? ?? ???? ????? ?? ??? ?? ???? ???????? ??? ?? ??? ??????
???????????:
?? ?????? ?? ????????? ?????? ?? ?????? ?????? ????????? ???? ?? ????? ?? ??? ????? ?? ??? ??? ?????? ???, ?? ???? ????????? ?????? ?? ?? ???? ?? ???? ???????? ?????? ??, ?? ?? ??? ??? ??? pursue ???? ?? ???????? ??? ??????? ????-???? ?????? ???? ?????? ?? ?????? ?? ??????? ?? ??? ????? Microsoft ???????? ???? ?????:243589
(http://support.microsoft.com/kb/243589/
)
????-???? ???????? ?? SQL Server 7.0 ?? ??? ?? ????????? ?? ?????? ???? ????
??? ????????? ???????? ?????? ??????, ?? ??? ????? ???????? ???? ?????:224587
(http://support.microsoft.com/kb/224587/
)
TO HOW: ???????????? SQL ????? ?? ??? ????????? ????????
???? ??????? ?? ???, ????????????? ??????? ?? ???? ???? ????????????? MSDN ??? ???? ?? SQL Server 2008 ???????? ?????? ????:??? ???? ??? ?? ???? ???? ?????? ?? ?? ???? ???????????? ?? ????? ?? ??? ?? ?? ???????? ???? ???? ?? ???? ??, ?? OLTP ?? ?? ???? ?? ????? ???? ?? ??? ??? ?????? ?????? ?????? ???????? - ???????? ???? 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
??????:
- ????????? ??? ?? ????????? ???????? ???? ???????? ???? ????, ?? ?? ?????? ??? ?? ??? ?? ???????? ????? ????????? ?????? ?? ???? ?? ???? ??? ?? ?? ??? ?? ?? ??? accomplished ?? ?? ???? ???:
- ??????? ????????? ?? ?????? ??????, ??? ??? @@ TRANCOUNT ????? ???? > 0 ??? ?? ?????? ?? ????? ??? ?? ??????? ????????? ???? ???? ???? ????????? ?????? TRAN ?? ?????? ?? ?????? ??, ??????? ??? ?? ????? ??? ???????? ????????? ??????? ????????? ?? ??????? ?? ???? ??? ????????? ??????? ?? ???? ????? ??? ?? ???? ?????? ?? ???? ???? ?? ??? ??, ??? ????????? ????????? ?? ??? ????? ??????? ??? ????????? ??? ?? ????????? ?? ????? IF ?? ???? ???? ?? ??? ??? @@ ?????? <> 0 ?? ????????? ??????, ??? ?????? ??? ?? ?????? ??? ????????? ???? ???? ??????
- ??????? ?? ???, ?? ???? ?? ???????? ???????????? ????????? ??????? ???? ?? ??? ?????? ??? ?? ??? ???? ??? ????? ??? SET XACT_ABORT ???? ?? ????? ????? ??? ??-???? ?????? ?? ???? ??? ?? ?????? ????? ???? ?? ???? ????????? ?????? ?? ??????? ?? ??? ???????? ????? ????? ??? ?? ??? ?? ?????? ?? ???? ????? T-SQL ??? ????????? ???? ???? ??????
- ??? ??????? ????? ?? ?? ???? ???????? ?? ?????? ?? ??????? ???, ???? ??? ???-?????? ?????????, ?? ?????? ???? ?? ???? ???? ?? ?? ???? ????????? ??? ??????? ?????? ????? ???? ?? ??? ??, ?? ??????? ??? ?? ??????? ?????? ?? ????? ???? ??? ?? ???? ?? ??????? ????????? appropriately ????????? ?? ????? ???? ?? ??? ??????? ???? ?? ?? ?? ?????? ?? ?? ????? ??????? ?????? ?? ????? ????, ?????? ??????? ?????? ????? ???? ?? ???????????? ????? ??? ??? ??? ?? ???? ?? ???? ????????? ??? SQL ????? ???????, ???? ????? ????
- ??? ??????? ?????? ????? ??, ?? ?????? ????? ?? 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 ??, ????? ?? ?????? ?? ??????????? ?? ??????? ?? ??? ?? ?? ??? ?? ?? ???? ???
- ???????? ???? ??? SPID ?? ????? ???? ?? ??? ?? ?????? ??????? ????????? ????? ?????? ?? ??? ????????? Fetch ???? ???
????? ?? ??? ??? ?????? ??? ??? ??, ?? ??? ??? ????????? ?????? ????? fetch ??? ?????? ????????? ????? ???? ?? ???? ??? ????????? ??? ?????? ?? ??? ????????? fetch ???? ??, locks ???? ???????????? ?? ????? ?? ??? ??, ???? ?? ???? ?? ???? ???? ??? ?? transparently submits SQL ???? ???? ??? ????????? ?? ????? ?? ??? ??? ????? ????? ?? ???, ????????? ?????? fetch ??? ?????? ?????????? ??? ?? ???? ?? (?? ??? ?? ??? ???? ?? ??? ???????? ???? ?? ?? ????), ????????? ?????? ?? ?? ???? ??? ?????? ?? ???? ??? ?? ?????? ?? ????, ?? ??? ??? ?????????? ?? ??? ??????-?????? ??????? ?? ??? ????????? poorly-behaved ????? ?? ???? ????
???????????:
????????? ?????? ?? ??? ????????? ?? ????? ???? ?? ??? fetch ???? ?? ??? re-written ???? ?????? - ??? ?????? ???????/????? Deadlock ?? ???? ????? ?? ???
??? ???????? deadlock ???? ?? ?????? ???? ?????? deadlock ???? ?? detectable ????? RDBMS ????? ???????? ?? due to the fact ?? SQL ????? ????? ?? ???? ?? ?????? deadlock ??? ????? ??? Deadlock ?? ???? ??? ??????? ????????? ???? ??, ??? ?? SQL Server ?? ???????? ???? ??? ????? ?? ?? ?????? ??? ?? ?? ?? ?? ???? ??, ?? ???? ????? ????????? ??? ?? ???? ?? ???? ????
- ??????/????? ?? ??? ??? ??????? ?? ??? 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)
- ??????/????? ?? ??????? ?? ????? ??? ????? 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)
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.
- 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 ???????? ?? ?????? ????? ?? ????? ???????? ?? ??????
??? ???? ??, ?? ??????? ?? ??????? ?? ????? ??? ???????? ??? - ??? Orphaned ??????? ?? ???? ????? ?? ???
??? ??????? ????????? traps ?? ??????? ?????????? ???????? ????, ????? ?? ??? ??????? ?? ???? ?? ???? ????? ???? ???? ?? ???? ??? ????????? ?? ???????? ????? ?? ???????????? ???, ?? ????? ???? ?? ??? ??????? ??? ?? ????? ??, ?? ??? ?? locks ??????? ??? ?? ??? ??? ?? ?? ???? ??????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::137983
(http://support.microsoft.com/kb/137983/
)
??? SQL Server orphaned ??????? ???????? ?? ?????? ???? ????
???????????:
???? ???????? ?? appropriately ??? ??? ???? ??????? ????????? ?? ?????????? ?? ???? ??, ??? ?? SPID KILL ???? ?? ????? ?? ??? ?? ???? ???? KILL ???? ????? ?? ??? ??? SPID ??? ???? ??? ?????? ?? ???, SPID 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 ???????????? ?? ?? ???? ?? ?????? ???? ??? ??????
Windows Live
Facebook
Twitter
Linkedin
Digg it
Yahoo
Delicious
StumbleUpon
Yammer
Reddit
Technorati
FriendFeed
Email
???? ?????? ????????
????? ?? ???? ????
