????(3). ???????
??????????????WaitFor????????е????????????????????????
????Query1?У?????Lock1?е????(??????????????)??????????(RID:X)???????и???????????????????(PAG:IX)???????????????(TAB:IX)??Query2?У?????Lock2?е????(??????????????)??????????(RID:X)???????и???????????????????(PAG:IX)???????????????(TAB:IX)??
?????????Waitfor??Query1???Lock2??????????????S???????????????Query2??????X????Query2???Lock1??????????????S???????????????Query1??????X???????????????????????????????????????????
????(4). ?????
????a). SQL Server?????????SQL?????????????????????????????????????????????SQL????????????????????SQL?????′???
??????????: ??? 1205?????? 13???? 50???? 1
???????????? ID xx?????????????????????? lock ???????????????????????????????????????и?????
?????????????????н??????????????????
????b). ???????????????????????SQL?е?Update??SELECT???????
???????????????SQL?????￡?
????–Query2
????Begin Tran
????SELECT * FROM Lock1–??Lock1??????S??
????WaitFor Delay ‘00:01:00’;
????Update Lock2 Set C1=C1+1;–Lock2:RID:X
????Rollback Tran;
??????????????????д?????????μ????SQL???????????????SQL?????????????????????Query1??Query2????1????????????????Query1????????????Query2????Query2???2????????????????????????????????????????????????
????c). SELECT????With(NoLock)?????????????SELECT????????????????S??(??????)??S????X??(??????)?????????????With(NoLock)??SELECT?????????????????(?????Sch-S????Sch-S?????????κ???????)?????????????????SQL??????????????????????????????????????д???????????????????With(NoLock)????????????
????SELECT * FROM Lock2 WITH(NOLock)
????SELECT * FROM Lock1 WITH(NOLock)
????d). ??y????????SQL Server 2000??????????????????(TIL)????????READ UNCOMMITTED??READ COMMITTED??REPEATABLE READ??SERIALIZABLE??SQL Server 2005????????SNAPSHOT TIL?????????￡?SQL Server???READ COMMITTED TIL????????????????????SQL??????????SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED????????TIL???????????????????????READ UNCOMMITTED TIL?????????е?SELECT?????????????????Sch-S???????????S???????????????????????READ UNCOMMITTED TIL?????????????????????TIL??????????????SQL????й????в??????????н???????????1?????????2???????????????????????????????????????
????e). ??SQL???SET LOCK_TIMEOUT timeout_period???????????????趨??timeout_period????????????SQL????У??????????????????
????f). ???????а汾??????????(SQL Server 2005???)???????????????SELECT???????????????S???????????????Sch-S?????????????д?????????????????????????????????????????
????SET ALLOW_SNAPSHOT_ISOLATION ON
????SET READ_COMMITTED_SNAPSHOT ON
????g). ??e?????(??÷???????????????)
????5.2 ????????(SQL????)
????????????????????????????????????????????????????????????????????????C#?????г?????????????????????е????????????????????????ж???????????????
????//?????????code
????SqlConnection conn = new SqlConnection(connectionString);
????conn.Open();
????SqlTransaction tran = conn.BeginTransaction();
????string sql1 = “Update Lock1 SET C1=C1+1”;
????string sql2 = “SELECT * FROM Lock1”;
????ExecuteNonQuery(tran?? sql1); //???????:??????Lock??Table
????ExecuteNonQuery(null?? sql2); //??????connection?????Table
????public static void ExecuteNonQuery(SqlTransaction tran?? string sql)
????{
????SqlCommand cmd = new SqlCommand(sql);
????if (tran != null)
????{
????cmd.Connection = tran.Connection;
????cmd.Transaction = tran;
????cmd.ExecuteNonQuery();
????}
????else
????{
????using (SqlConnection conn = new SqlConnection(connectionString))
????{
????conn.Open();
????cmd.Connection = conn;
????cmd.ExecuteNonQuery();
????}
????}
????}
??????е?ExecuteNonQuery(null?? sql2)????SQL??г???????????????????????????????
?????????????????У???1???????Lock1??X????????????н????????1??????X????????????2???select?????????????Lock1???S??????S????X?????????????????2???????????????????У??????л????????????г???????????????????????????沢??г??????????????SELECT??????????????????????????????????????SQL Server??????????????????????
???????????C#?????????????????
????C#??????????Lock1???X???????????????SqlConnection?????????????????S?????????????????·?????????????????????????????????
??????????????????????????????????????????????μ???????????????????????????????????????????????????????????????????????????￡?
????a). ??SELECT????Update??????SELECT?????????У?????????????S????
????b). ??SELECT???????????У?ExecuteNonQuery(tran?? sql2);
????c). SELECT??With(NOLock)?????????????????
????d). ?????????????SELECT??????SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED??????????????????
????e). ???????а汾???????????????????
????g). ??e????????????????????token????????????connection?У????EXEC sp_bindsession @Token???????????????exec sp_bindsession null;????????????????????????
????(1). ?????????????connection?????????????????????????????????????????????
????(2). ?????sql3???????“exec sp_bindsession null”????“commit tran”????“rollback tran”????????????????????C#????tran.Commit()????????????(??л????????????????????-??-)??
????(3). ????????(begin tran)??????????exec sp_getbindtoken @Token out?????Token????????????????connection?н???????е????????????connection close???????????“exec sp_bindsession null”???????????????????????connectoin close?????????????(commit/tran)??
????(4). (Sql server 2005 ????????)?????汾?? Microsoft SQL Server ??????ù????????????μ?????????????ù??????????????????????ù??????ó??? ?????????????? (MARS) ?????????
????tran = connection.BeginTransaction();
????string sql1 = “Update Lock1 SET C1=C1+1”;
????ExecuteNonQuery(tran?? sql1); //???????:??????Lock??????Lock1
????string sql2 = @”DECLARE @Token varchar(255);
????exec sp_getbindtoken @Token out;
????SELECT @Token;”;
????string token = ExecuteScalar(tran?? sql2).ToString();
????string sql3 = “EXEC sp_bindsession @Token;Update Lock1 SET C1=C1+1;exec sp_bindsession null;”;
????SqlParameter parameter = new SqlParameter(“@Token”?? SqlDbType.VarChar);
????parameter.Value = token;
????ExecuteNonQuery(null?? sql3?? parameter); //??????connection???????????Lock1
????tran.Commit();