????? ??????????????????????
??????????????????????????
??????1?? ????????????????????????????????á?
??????2?? ?????????????????????????????????????????????????????????
??????3?? ??????????:????????????????????????????????а???
??????4?? ??????????:??????????γ?????β??????????????????
?????? ???????
?????????????????????
??????????????????????????????????????????????????????????
????MS-SQL Server ????????????????
???????? ????
???????? (S) ????????????????????????????????????????????? SELECT ???
???????? (U) ??(??????????????????????????????????????????????????????????????????????
??????????????????????????ù??????????????????????????????????UPDLOCK ????????????????????????????????????????????????????????????ζ?????????????б??????????????UPDLOCK???????????????????????????????????????????????????????????????????????????????????????????????????????
????BEGIN TRANSACTION --??????????
????SELECT Qty
????FROM myTable WITH (UPDLOCK)
????WHERE Id in (1??2??3)
????UPDATE myTable SET Qty = Qty - A.Qty
????FROM myTable  AS A
????INNER JOIN  @_Table AS B ON A.ID = B.ID
????COMMIT TRANSACTION --??????
????????????????????????????????Щ??????????????????ID????????????????????????????????????????????????????????????????????Щ?????????????.???????????????????.
???????? (X)??д???? ???????????????????? INSERT??UPDATE ?? DELETE???????????????????ж???????
?????????? ????????????ν???????????????????????? (IS)?????????? (IX) ????????????????? (SIX)??
????????? ??????????????????????á????????????????????? (Sch-M) ????????? (Sch-S)??
?????????????? (BU) ????д??????????????????? TABLOCK ??????á?
??????????
???????? (S) ??????????????? (SELECT) ??????????????????? (S) ??????κ????????????????????????????????????????????????????? (S) ?????????????????????????????????????????????????????????????????????????? (S) ????
??????????
???????? (U) ????????????????????????????????????????????????????????????????????У?????? (S) ???????????У??????????????????? (X) ????????????????????????????????????????????????????????????????????????? (X) ??????????????????????????????????????????????????????????????????????????????????????????????????????????? (X) ??????и????????????????????????? (X) ???????????????????????????????????????????????????
??????????????????????????????????? (U) ?????????????????????????????? (U) ???????????????????????? (U) ?????????? (X) ??????????????????????
??????????
???????? (X) ???????????????????????з????????????????????????? (X) ?????????????
??????????
????????????? SQL Server ??????ν??е??Щ?????????????? (S) ???????? (X) ???????磬?????????????????????????????????е??????????ù??? (S) ?????????????????????????????????????????????????????? (X) ????????????????????????? SQL Server ????????????????????????????????????????????????????????е???л??????????????????????????????????
???????????????????? (IS)?????????? (IX) ????????????????? (SIX)??
???????????
??????????????е???壺????????????????е???????????в??????????????????????????????????ò??????????????????????????
??????????????????????
????????????(Mutual exclusion)?????????????????????????????á?
????????????????(Hold and wait)???????????????????????????μ??????
?????????????(No pre-emption)???????????????????????????б????????
??????????????(Circular wait)????????????????·?????·????????????????????????????????
?????????SQL Server?У??????????????????У?????????????????????????????????????????????????Щ?????????????????????????Щ??????????????(RID?????е????)???????е??(KEY??????)???(PAG??8KB)??????(EXT????????8?)?????B??(HOBT) ????(TAB???????????????)?????(File??????????)????ó?????????(APP)???????(METADATA)???????(Allocation_Unit)???????????(DB)??????????????????????
?????????T1??T2???????????R1??R2???????????????????????????(??R1->T1??R2->T2)?????????????????????У?????????????????(??T1->S2??T2->S1)???????????????????????????
????????????????????????????????
????(1).???????S1??S2???????????????????????????????
????(2).??????????????T1????S1??????????S2??T2????S2????????S1??
????(3).???????????T1?????T2?????S2??T2??????T1?????S1??
????(4).????????????????е????????·??????????????
???????????
????(1). ???SQL Server?????洢????sp_who??sp_lock????????????????е????????????????objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)???????????????????dbcc ld(@blk)??????????????????SQL Server??Sql???
????CREATE Table #Who(spid int??
????ecid int??
????status nvarchar(50)??
????loginname nvarchar(50)??
????hostname nvarchar(50)??
????blk int??
????dbname nvarchar(50)??
????cmd nvarchar(50)??
????request_ID int);
????CREATE Table #Lock(spid int??
????dpid int??
????objid int??
????indld int??
????[Type] nvarchar(20)??
????Resource nvarchar(50)??
????Mode nvarchar(10)??
????Status nvarchar(10)
????);
????INSERT INTO #Who
????EXEC sp_who active  --????????????????blk
????INSERT INTO #Lock
????EXEC sp_lock  --?????????????id??objid
????DECLARE @DBName nvarchar(20);
????SET @DBName='NameOfDataBase'
????SELECT #Who.* FROM #Who WHERE dbname=@DBName
????SELECT #Lock.* FROM #Lock
????JOIN #Who
????ON #Who.spid=#Lock.spid
????AND dbname=@DBName;
????--?????SQL Server?????
????DECLARE crsr Cursor FOR
????SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>0;
????DECLARE @blk int;
????open crsr;
????FETCH NEXT FROM crsr INTO @blk;
????WHILE (@@FETCH_STATUS = 0)
????BEGIN;
????dbcc inputbuffer(@blk);
????FETCH NEXT FROM crsr INTO @blk;
????END;
????close crsr;
????DEALLOCATE crsr;
????--?????????
????SELECT #Who.spid??hostname??objid??[type]??mode??object_name(objid) as objName FROM #Lock
????JOIN #Who
????ON #Who.spid=#Lock.spid
????AND dbname=@DBName
????WHERE objid<>0;
????DROP Table #Who;
????DROP Table #Lock;
????(2). ??? SQL Server Profiler ????????: ?? Deadlock graph ?????????????????????????????漰???????????? XML ???????????е? TextData ?????С?SQL Server ???????? ????? XML ???????????? XML (.xdl) ????У??????? SQL Server Management Studio ?в????????
????????????
????????1???г???????????????????????????????????е??????????????????????????????????????????????????(FROM Sql Server 2005????????)??
????(1).?????????????(?????????????)
????(2).?????????е??????????(????????????????????????????)
????(3).?????????????????????????С?(????(2)?????????????????)
????(4).??y????????(?????y????????????????????????y????????????????л??????й????????????????????????)
????(5).???????а汾??????????2005???????????????????READ_COMMITTED???????????????а汾??????????????д?????????????????????????
????SET ALLOW_SNAPSHOT_ISOLATION ON –?????????? SNAPSHOT ?????????;
????SET READ_COMMITTED_SNAPSHOT ON –??? READ_COMMITTED ???????????????а汾???????????????????????(??п?????????м?with nolock???)??SELECT???????????????S??(??????)???????????????SELECT???????????????S????
??????????? READ_COMMITTED_SNAPSHOT ???????????????????????? ALTER DATABASE ???????????? ALTER DATABASE ?????????????о??????????????????????????????????????????С?
????(6).??e??????(??????????????????????????????????Э?????????????????????????????????????????(????????????????????????????)????????????????????????????????????????????ó???????????д?????????????????????????????ó????д?????????????????п???????(begin tran)??????exec sp_getbindtoken @Token out;?????Token???????????????????EXEC sp_bindsession @Token?????а?(??????????????????)??
?????????????????
????(1). ????2??????sql???????spid????wait?????????kill spid?????(??????????????????????:??????)??????????????????????????????????????????????????????????????????????Kill sp??????????????????????????
????(2). ???SET LOCK_TIMEOUT timeout_period(??λ?????)???趨????????????????￡????????г??????(timeout_period??-1????????SELECT @@LOCK_TIMEOUT???????????????????)??????????????timeout_period????????????timeout_period??0??????????????????????????????????????????????????????????????????(????????????)??
??????????: ??? 1222?????? 16???? 50???? 1
????????????????????Ρ?
????(3). SQL Server????????????????????????????飬??????????????????????????????????????????????????????????????????????????????????ж???Щ???????????????????????????????????????????????????????????? ??????л??????С??????????????????????????1205 ????????????????????????????????е?????????????????????????????????????????С?
????????????????????????
????5.1 SQL????
????(1). ???????????????
????CREATE TABLE Lock1(C1 int default(0));
????CREATE TABLE Lock2(C1 int default(0));
????INSERT INTO Lock1 VALUES(1);
????INSERT INTO Lock2 VALUES(1);
????(2). ????????????????????????????sql
????–Query 1
????Begin Tran
????Update Lock1 Set C1=C1+1;
????WaitFor Delay ‘00:01:00’;
????SELECT * FROM Lock2
????Rollback Tran;
????–Query 2
????Begin Tran
????Update Lock2 Set C1=C1+1;
????WaitFor Delay ‘00:01:00’;
????SELECT * FROM Lock1
????Rollback Tran;
?????????SQL???????WaitFor Delay ‘00:01:00’????????1????????????????????