æ»éï¼ç®èè¨ä¹ï¼ä¸¤ä¸ªæè
å¤ä¸ªtransï¼åæ¶è¯·æ±å¯¹æ¹æ£å¨è¯·æ±çæ个对象ï¼å¯¼è´åæ¹äºç¸çå¾
ãç®åçä¾åå¦ä¸ï¼
trans1 trans2
------------------------------------------------------------------------
1.IDBConnection.BeginTransaction 1.IDBConnection.BeginTransaction
2.update table A 2.update table B
3.update table B 3.update table A
4.IDBConnection.Commit 4.IDBConnection.Commit
é£ä¹ï¼å¾å®¹æçå°ï¼å¦ætrans1åtrans2ï¼åå«å°è¾¾äºstep3ï¼é£ä¹trans1ä¼è¯·æ±å¯¹äºBçXéï¼trans2ä¼è¯·æ±å¯¹äºAçXéï¼èäºè
çéå¨step2ä¸å·²ç»è¢«å¯¹æ¹åå«ææäºãç±äºå¾ä¸å°éï¼åé¢çCommitæ æ³æ§è¡ï¼è¿æ ·åæ¹å¼å§æ»éã
好ï¼æ们çä¸ä¸ªç®åçä¾åï¼æ¥è§£éä¸ä¸ï¼åºè¯¥å¦ä½è§£å³æ»éé®é¢ã
-- Batch #1
CREATE DATABASE deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
DBCC TRACEON (1222, -1)
-- å¨SQL2005ä¸ï¼å¢å äºä¸ä¸ªæ°çdbccåæ°ï¼å°±æ¯1222ï¼åæ¥å¨2000ä¸ï¼æ们ç¥éï¼å¯ä»¥æ§è¡dbcc
--traceon(1204,3605,-1)çå°ææçæ»éä¿¡æ¯ãSqlServer 2005ä¸ï¼å¯¹äº1204è¿è¡äºå¢å¼ºï¼è¿å°±æ¯1222ã
GO
IF OBJECT_ID ('t1') IS NOT NULL DROP TABLE t1
IF OBJECT_ID ('p1') IS NOT NULL DROP PROC p1
IF OBJECT_ID ('p2') IS NOT NULL DROP PROC p2
GO
CREATE TABLE t1 (c1 int, c2 int, c3 int, c4 char(5000))
GO
DECLARE @x int
SET @x = 1
WHILE (@x <= 1000) BEGIN
INSERT INTO t1 VALUES (@x*2, @x*2, @x*2, @x*2)
SET @x = @x + 1
END
GO
CREATE CLUSTERED INDEX cidx ON t1 (c1)
CREATE NONCLUSTERED INDEX idx1 ON t1 (c2)
GO
CREATE PROC p1 @p1 int AS SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO
ä¸è¿°sqlå建ä¸ä¸ªdeadlockç示èæ°æ®åºï¼æå
¥äº1000æ¡æ°æ®ï¼å¹¶å¨è¡¨t1ä¸å»ºç«äºc1åçèéç´¢å¼ï¼åc2åçéèéç´¢å¼ãå¦å¤å建äºä¸¤ä¸ªspï¼åå«æ¯ä»t1ä¸selectæ°æ®åupdateæ°æ®ã
好ï¼æå¼ä¸ä¸ªæ°çæ¥è¯¢çªå£ï¼æ们å¼å§æ§è¡ä¸é¢çqueryï¼
-- Batch #2
USE deadlocktest
SET NOCOUNT ON
WHILE (1=1) EXEC p2 4
GO
å¼å§æ§è¡åï¼ç¶åæ们æå¼ç¬¬ä¸ä¸ªæ¥è¯¢çªå£ï¼æ§è¡ä¸é¢çqueryï¼
-- Batch #3
USE deadlocktest
SET NOCOUNT ON
CREATE TABLE #t1 (c2 int, c3 int)
GO
WHILE (1=1) BEGIN
INSERT INTO #t1 EXEC p1 4
TRUNCATE TABLE #t1
END
GO
å¼å§æ§è¡ï¼ååï¼å¾å¿«ï¼æ们çå°äºè¿æ ·çé误信æ¯ï¼
Msg 1205, Level 13, State 51, Procedure p1, Line 4
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
spid54åç°äºæ»éã
é£ä¹ï¼æ们该å¦ä½è§£å³å®ï¼
å¨SqlServer 2005ä¸ï¼æ们å¯ä»¥è¿ä¹åï¼
1.å¨trans3ççªå£ä¸ï¼éæ©EXEC p1 4ï¼ç¶åright clickï¼çå°äºèåäºåï¼éæ©Analyse Query in Database Engine Tuning Advisorã
2.注æå³é¢ççªå£ä¸ï¼wordloadæä¸ä¸ªéæ©ï¼è´è½½æ件ã表ãæ¥è¯¢è¯å¥ï¼å 为æ们éæ©äºæ¥è¯¢è¯å¥çæ¹å¼ï¼æ以就ä¸éè¦ä¿®æ¹è¿ä¸ªradio optionäºã
3.ç¹å·¦ä¸è§çStart Analysisæé®
4.æ½æ ¹çï¼åæ¥åçç»æå§ï¼åºç°äºä¸ä¸ªåæç»æçªå£ï¼å
¶ä¸ï¼å¨Index Recommendationsä¸ï¼æ们åç°äºä¸æ¡ä¿¡æ¯ï¼å¤§ææ¯ï¼å¨è¡¨t1ä¸å¢å ä¸ä¸ªéèéç´¢å¼ç´¢å¼ï¼t2+t1ã
5.å¨å½åçªå£çä¸æ¹èåä¸ï¼éæ©Actionèåï¼éæ©Apply Recommendationsï¼ç³»ç»ä¼èªå¨å建è¿ä¸ªç´¢å¼ã
éæ°è¿è¡batch #3ï¼åµåµï¼æ»é没æäºã
è¿ç§æ¹å¼ï¼æ们å¯ä»¥è§£å³å¤§é¨åçSql Serveræ»éé®é¢ãé£ä¹ï¼åçè¿ä¸ªæ»éçæ ¹æ¬åå æ¯ä»ä¹å¢ï¼ä¸ºä»ä¹å¢å ä¸ä¸ªnon clustered indexï¼é®é¢å°±è§£å³äºå¢ï¼ è¿æ¬¡ï¼æ们åæä¸ä¸ï¼ä¸ºä»ä¹ä¼æ»éå¢ï¼åå顾ä¸ä¸ä¸¤ä¸ªspçåæ³ï¼
CREATE PROC p1 @p1 int AS
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
GO
CREATE PROC p2 @p1 int AS
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
GO
å¾å¥æªå§ï¼p1没æinsertï¼æ²¡ædeleteï¼æ²¡æupdateï¼åªæ¯ä¸ä¸ªselectï¼p2ææ¯updateãè¿ä¸ªåæ们åé¢è¯´è¿çï¼trans1éé¢updata Aï¼update Bï¼trans2éé¢upate Bï¼update Aï¼æ ¹æ¬ä¸è´´è¾¹åï¼
é£ä¹ï¼ä»ä¹å¯¼è´äºæ»éï¼
éè¦ä»äºä»¶æ¥å¿ä¸ï¼çsqlçæ»éä¿¡æ¯ï¼
Spid X is running this query (line 2 of proc [p1], inputbuffer â⦠EXEC p1 4 â¦â):
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
Spid Y is running this query (line 2 of proc [p2], inputbuffer âEXEC p2 4â):
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.
é¦å
ï¼æ们ççp1çæ§è¡è®¡åãæä¹çå¢ï¼å¯ä»¥æ§è¡set statistics profile onï¼è¿å¥å°±å¯ä»¥äºãä¸é¢æ¯p1çæ§è¡è®¡å
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
|--Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)
æ们çå°äºä¸ä¸ªnested loopsï¼ç¬¬ä¸è¡ï¼å©ç¨ç´¢å¼t1.c2æ¥è¿è¡seekï¼seekåºæ¥çé£ä¸ªrowidï¼å¨ç¬¬äºè¡ä¸ï¼ç¨æ¥éè¿èéç´¢å¼æ¥æ¥æ¾æ´è¡çæ°æ®ãè¿æ¯ä»ä¹ï¼å°±æ¯bookmark lookupåï¼ä¸ºä»ä¹ï¼å 为æ们éè¦çc2ãc3ä¸è½å®å
¨ç被索å¼t1.c1带åºæ¥ï¼æ以éè¦ä¹¦ç¾æ¥æ¾ã
好ï¼æ们æ¥ççp2çæ§è¡è®¡åã
UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
|--Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
|--Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
|--Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN ...
|--Top(ROWCOUNT est 0)
|--Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)
éè¿èéç´¢å¼çseekæ¾å°äºä¸è¡ï¼ç¶åå¼å§æ´æ°ãè¿é注æçæ¯ï¼updateçæ¶åï¼å®ä¼ç³è¯·ä¸ä¸ªé对clustered indexçXéçã
å®é
ä¸å°è¿éï¼æ们就æç½äºä¸ºä»ä¹updateä¼å¯¹select产çæ»éãupdateçæ¶åï¼ä¼ç³è¯·ä¸ä¸ªé对clustered indexçXéï¼è¿æ ·å°±é»å¡ä½äºï¼æ³¨æï¼ä¸æ¯æ»éï¼ï¼selectéé¢æåçé£ä¸ªclustered index seekãæ»éçå¦ä¸åå¨åªéå¢ï¼æ³¨ææ们çselectè¯å¥ï¼c2åå¨äºç´¢å¼idx1ä¸ï¼c1æ¯ä¸ä¸ªèéç´¢å¼cidxãé®é¢å°±å¨è¿éï¼æ们å¨p2ä¸æ´æ°äºc2è¿ä¸ªå¼ï¼æ以sqlserverä¼èªå¨æ´æ°å
å«c2åçéèéç´¢å¼ï¼idx1ãèidx1å¨åªéï¼å°±å¨æ们åæçselectè¯å¥ä¸ãè对è¿ä¸ªç´¢å¼åçæ´æ¹ï¼æå³çç´¢å¼éåçæ个è¡æè
æäºè¡ï¼éè¦éæ°æåï¼èéæ°æåï¼éè¦ä¸ä¸ªXéã
SOâ¦â¦â¦ï¼é®é¢å°±è¿æ ·è¢«åç°äºã
æ»ç»ä¸ä¸ï¼å°±æ¯è¯´ï¼æ个query使ç¨éèéç´¢å¼æ¥selectæ°æ®ï¼é£ä¹å®ä¼å¨éèéç´¢å¼ä¸ææä¸ä¸ªSéãå½æä¸äºselectçåä¸å¨è¯¥ç´¢å¼ä¸ï¼å®éè¦æ ¹æ®rowidæ¾å°å¯¹åºçèéç´¢å¼çé£è¡ï¼ç¶åæ¾å°å
¶ä»æ°æ®ãèæ¤æ¶ï¼ç¬¬äºä¸ªçæ¥è¯¢ä¸ï¼updateæ£å¨èéç´¢å¼ä¸å¿ä¹ï¼å®ä½ãå éãä¿®æ¹çãä½å 为æ£å¨ä¿®æ¹çæ个åï¼æ¯å¦å¤ä¸ä¸ªéèéç´¢å¼çæ个åï¼æ以æ¤æ¶ï¼å®éè¦åæ¶æ´æ¹é£ä¸ªéèéç´¢å¼çä¿¡æ¯ï¼è¿å°±éè¦å¨é£ä¸ªéèéç´¢å¼ä¸ï¼å 第äºä¸ªXéãselectå¼å§çå¾
updateçXéï¼updateå¼å§çå¾
selectçSéï¼æ»éï¼å°±è¿æ ·åçé¸ã
é£ä¹ï¼ä¸ºä»ä¹æ们å¢å äºä¸ä¸ªéèéç´¢å¼ï¼æ»éå°±æ¶å¤±é¸ï¼æ们çä¸ä¸ï¼æç
§ä¸æä¸èªå¨å¢å çç´¢å¼ä¹åçæ§è¡è®¡åï¼
SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
|--Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] >= [@p1] AND [deadlocktest].[dbo].[t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
å¦ï¼å¯¹äºclustered indexçéæ±æ²¡æäºï¼å 为å¢å çè¦çç´¢å¼å·²ç»è¶³å¤æææçä¿¡æ¯é½selectåºæ¥ãå°±è¿ä¹ç®åã
å®é
ä¸ï¼å¨sqlserver 2005ä¸ï¼å¦æç¨profileræ¥æeventidï¼1222ï¼é£ä¹ä¼åºç°ä¸ä¸ªæ»éçå¾ï¼å¾ç´è§ç说ã
ä¸é¢çæ¹æ³ï¼æå©äºå°æ»éåè³æå°ï¼è¯¦ç»æ
åµï¼è¯·çSQLServerèæºå¸®å©ï¼æç´¢ï¼å°æ»éåè³æå°å³å¯ã
æåä¸é¡ºåºè®¿é®å¯¹è±¡ã
é¿å
äºå¡ä¸çç¨æ·äº¤äºã
ä¿æäºå¡ç®ç并å¤äºä¸ä¸ªæ¹å¤çä¸ã
使ç¨è¾ä½çé离级å«ã
使ç¨åºäºè¡çæ¬æ§å¶çé离级å«ã
å° READ_COMMITTED_SNAPSHOT æ°æ®åºé项设置为 ONï¼ä½¿å¾å·²æ交读äºå¡ä½¿ç¨è¡çæ¬æ§å¶ã
使ç¨å¿«ç
§é离ã
使ç¨ç»å®è¿æ¥ã
温馨提示:答案为网友推荐,仅供参考