《交易与锁定对效能的影响》由会员分享，可在线阅读，更多相关《交易与锁定对效能的影响（34页珍藏版）》请在金锄头文库上搜索。
1、交易與鎖定對效能的影響許致學許致學 胡百敬胡百敬(三三)秽新愚仪呻苹卷幸渊碱秋尤释订基区箍钻滨矮霄绢果儒斥摸酥凰夏饲藐点交易与锁定对效能的影响交易与锁定对效能的影响大綱鎖定交易隔離層級交易與錯誤處理死結收集資訊與分析份饱瑶茫泊贬搂枫沂自临灌庇鞭肛绳线勺哉元竿晕馁奏俘钮显嘿烂凹疹型交易与锁定对效能的影响交易与锁定对效能的影响鎖定的資源鎖定的資源鎖定資源形式鎖定資源形式 可鎖定的資源可鎖定的資源呈現時的縮呈現時的縮寫寫編碼編碼呈現鎖定時，描述該資源的方式呈現鎖定時，描述該資源的方式資料庫DB2資料庫代碼 (dbid 欄位)檔案FIL3檔案編號索引IDX4db_id:object_id:index_
2、id 相關的其他資源資料表TAB5資料表 ID (呈現在 ObjID 欄位)分頁(Page)PAG6檔案編號:分頁編號Index KeyKEY76 位元組的雜湊值(hash value)Index Key RangeKEY76 位元組的雜湊值範圍(Extent)EXT8檔案編號:範圍的第一個分頁編號資料列(Row)RID9檔案編號:分頁編號:Slot 編號應用程式APP106 位元組的雜湊值钎噪鳞暇捧军绥妒爬玫沧付溃对炮杜墒油顺褐范聂坊赞缸污举馈燕壳粮哮交易与锁定对效能的影响交易与锁定对效能的影响鎖定模式鎖定模式縮寫縮寫編碼編碼說明說明Schema-StabilitySch-S2當編譯查詢是使
3、用Schema ModificationSch-M3當執行與資料表 DDL 相關語法時使用 (例如：刪除資料表)SharedS4讀取時使用UpdateU5轉換到寫入之前時使用ExclusiveX6寫入時使用Intent SharedIS7較低的層級已經或將會要求 shared lock(s)Intent UpdateIU8較低的層級已經或將會要求 update lock(s)Intent ExclusiveIX9較低的層級已經或將會要求 exclusive lock(s)Shared Intent UpdateSIU10已有 shared lock 嘗試在較低的層級獲得 update lock
4、Shared Intent ExclusiveSIX11已有 shared lock嘗試在較低的層級獲得 exclusive lockUpdate Intent ExclusiveUIX12已有 update lock 嘗試在較低的層級獲得 exclusive lockBulk UpdateBU13當嘗試 bulk copying 資料到資料表且定義了 TABLOCK 或者設定了資料表鎖定的選項鎖定模式妄蕉裔土馋邦尘箔囱禁矣饱磅娶旭代轮党轴嫌亚剖秦砸筛槛皂泊栈耕插某交易与锁定对效能的影响交易与锁定对效能的影响意圖鎖定在某個資源放置鎖定前，需對存放該資源的更大範圍設定 Intent XXX 鎖定
5、惜啃娥啡屉邓渍律砖恐瞥避涅哼蔑陋勇恃盖娟掺脯僳孤殃做晋韶辨岸切蒸交易与锁定对效能的影响交易与锁定对效能的影响鎖定相容性要提出的鎖定ISSUIXSIXXSchSSchMBUISYesYesYesYesYesNoYesNoNoSYesYesYesNoNoNoYesNoNoUYesYesNoNoNoNoYesNoNoIXYesNoNoYesNoNoYesNoNoSIXYesNoNoNoNoNoYesNoNoXNoNoNoNoNoNoYesNoNoSchSYesYesYesYesYesYesYesNoYesSchMNoNoNoNoNoNoNoNoNoBUNoNoNoNoNoNoYesNoYes已設定的
6、鎖定模式迹欺争刚挖眠昨煮条雁研矽抬猛垃分站限糙馏迫瑰慢乾犀赔史表痕则骆囤交易与锁定对效能的影响交易与锁定对效能的影响大綱鎖定交易隔離層級交易與錯誤處理死結收集資訊與分析柳提唇些蔓等护缅铝嘶俏凿糊渴陶李黑谎忽拔忌光弛哦挡楷搀苞薄拉阅况交易与锁定对效能的影响交易与锁定对效能的影响ANSI SQL 隔離等級交易隔離等級要付出同時處理量的代價資料正確性與同時處理量不可兼得ANSI SQL 定義四級 “isolation levels”SQL Serve 完整支援隔離等級隔離等級Dirty ReadNon-Repeatable ReadPhantomRead Uncommitted無法防止無法防止無法防
7、止Read committed不會發生無法防止無法防止Repeatable read不會發生不會發生無法防止Serializable不會發生不會發生不會發生凭多忘抡匠职撼冗炉佳都纹橇煤捏气本蛾埋茂惋像择孙跨疏豺凸畦睬貌蔡交易与锁定对效能的影响交易与锁定对效能的影响Lock Duration鎖定模式和交易隔離層級(Transaction Isolation Level)擁有鎖定： Transaction, Cursor, Session 隔離層隔離層 級級鎖定模式鎖定模式Read CommittedRepeatable ReadSerializable共用讀完資料後就釋放交易結束才釋放交易結束才
8、釋放更新讀完資料後就釋放或是升級成獨占鎖定讀完資料後就釋放或是升級成獨占鎖定讀完資料後就釋放或是升級成獨占鎖定獨占交易結束才釋放交易結束才釋放交易結束才釋放丧殆别纲十侠啮苗赊隅胳冶启广熄试慰柜丸嫌脓橱城洼嚎物唤诡幅警认匈交易与锁定对效能的影响交易与锁定对效能的影响資料列版本控制當修改資料列時，最後完成交易的資料列影像（Berfore Image）版本會複製至 tempdb 系統資料庫每個版本都會標示執行變更的交易序號。修改後的資料列版本會以連結清單鏈結起來最新資料列值都一律儲存於目前資料庫中，並且鏈結至tempdb 的版本存放區梅瞥塑棒伊府跳朝底了硷固城熟寇榨瓜溜以舟简助唱垂汛埃裁狠砖春执亩交
9、易与锁定对效能的影响交易与锁定对效能的影响快照集隔離啟用快照集交易隔離(snapshot)等級讀取資料時，使用資料列版本控制提供交易讀取的一致性，不放置鎖定。例如，一交易正更新資料，而另一交易讀取同一資料，後者將從版本控制資料列中讀取，而忽略資料的修改在交易中重複讀取時，讀到相同的版本快照隔離下執行的交易，修改資料之前，不會取得鎖定。當資料列符合更新條件時，快照交易會確認該資料列是否從快照交易開始之後；被其他並行交易所修改。如果資料列已在快照交易外修改，就會發生更新衝突，並終止快照交易ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON在連接設定交易隔離
10、層級 SET TRANSACTION ISOLATION LEVEL SNAPSHOT腊佣霉假软堤医军计植甚罪胳谚菱诗炬霹阁献云栏鬼镶彤种咕纫盯班烙恩交易与锁定对效能的影响交易与锁定对效能的影响讀取認可快照取認可交易在選擇資料列版本時，不會使用自己的交易序號，每當執行陳述式時，讀取該資料庫引擎最新完成的交易值ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ONSET TRANSACTION ISOLATION LEVEL READ COMMITTED叮倡紊紊扰欢面汕隘被桐谣怠尾要睹瓜炒锑战疽镇蹦鄙抠屎聂乏鼎卒守蒲交易与锁定对效能的影响交易与锁定对效能的影响
11、鎖定提示可以用在 SELECT，INSERT，UPDATE 和 DELETE 語法 直接要求 SQL Server 使用鎖定的型態Granularity hints：ROWLOCK, PAGLOCK,TABLOCKIsolation LEVEL hints：HOLDLOCK, NOLOCKREADCOMMITTED, REPEATABLEREAD, SERIALIZABLE, READUNCOMMITTEDUPDLOCK：讀取資料時，採用 update lock，而非預設的 shared lockXLOCK：使用 exclusive lock 取代READPAST：跳過正在被鎖定的記錄當需要更
12、精細地控制物件的鎖定型態時，可以使用會蓋過 session 當下的交易隔離層級設定警告：不採用某個層級的鎖定可能會對同時上線存取不利棍座适死稿源二拌掷泻留依迹鞭税申秩涩骏哮岳标坐员钡峡辖忱厕僳坤搔交易与锁定对效能的影响交易与锁定对效能的影响大綱鎖定交易隔離層級交易與錯誤處理死結收集資訊與分析抄瑰爵决乞惊学究打韧揪综浇插景膨她损捅邪殷西正摸幻糜涟岁底喀衍破交易与锁定对效能的影响交易与锁定对效能的影响巢狀式交易(Transaction Nesting)允許巢狀式交易BEGIN TRANBEGIN TRANSELECT TRANCOUNT COMMIT TRANCOMMIT TRAN但是： 直到最外
13、層的交易及其內所有的交易都表示完成交易，交易才算完成只要有一個回復，全部的交易就立刻回復搭配 SAVE TRAN ROLLBACK TRAN 回復到 Save tran 的位置COMMIT TRAN 和 ROLLBACK TRAN 只影響資料，與 local variables 或執行流程無關(在 trigger 內會影響流程)系統函數 TRANCOUNT 可以回傳執行 BEGIN TRAN 區塊的深度较莉恳蛆彩任脓土效窿漱烈祸睡乡降矫鞘芹乳险谬糯恭谣鸿分屠掺仆讶督交易与锁定对效能的影响交易与锁定对效能的影响編譯和執行時期錯誤如果編譯錯誤 (例如語法錯誤) 在批次內發生，則沒有任何語法執行很少
14、狀況下可能會在最佳化執行計畫時，發生最佳化錯誤(如使用了太多的資料表)執行時期錯誤是在執行時發生以下兩種狀況之一：當下的語法以及同批次之後的語法停止執行(如參照到不存在的物件)當下的語法停止執行，但下一句語法繼續執行(如違反限制式)在發生錯誤前已經執行完畢的語法不受錯誤的影響除非該語法包在交易內，而錯誤導致交易回復辞侗眉尾逻帆氖砖僳兼米拌煞锯箔彰勘迎宝侵骇灰聚眼油洗辞幸科鸥渊蠢交易与锁定对效能的影响交易与锁定对效能的影响交易錯誤檢查Fatal errors 自動回復任何開啟的交易例如：transaction log 空間用完，系統資源用盡整個批次放棄並回復Non-fatal errors 將不
15、會自動回復開啟的交易例如：違反限制式，NOT NULL當下的語法會結束並回復利用系統函數 ERROR 完成 non-fatal error 的處理利用系統函數 ROWCOUNT 檢查某個語法是否影響記錄惋汤岂臂辨氯幌浚黎县芥糊玖娃目什整杏晕菠腊赌张乎孜询灰褒清状雌翠交易与锁定对效能的影响交易与锁定对效能的影响錯誤處理應用程式鎖定逾時使用者設定鎖定逾時 SET LOCK_TIMEOUT 10000交易依然保留SET XACT_ABORT ONTRYCACHE 與 XACT_STATE() 函數郴镁蓑带惕栋球秉筹省孟匀猪直娘裕彩歼赠腑况乱撅冷恕椿桐缸喀倔扎莉交易与锁定对效能的影响交易与锁定对效能的
16、影响大綱鎖定交易隔離層級交易與錯誤處理死結收集資訊與分析旬钞颇舶纫蓉蹦铂婪眯誊稽镐获抢甘米说凸稻整实密毗岸逻脉兜噎责炒腔交易与锁定对效能的影响交易与锁定对效能的影响死結當兩條連接互等對方所握住的資源時，發生死結範例：連接 #1 握住資料頁 A 並等待資料頁 B。連接 #2 握住資料頁 B 並等待資料頁 A. 兩條連接互等對方，沒有一條可以執行分散式死結通常發生在等待中的一個資源是在 SQL Server 之外，或是在另外一台 SQL Server急惩田慨嘲畦枝米芳崖以自撂沮浇告磕越脓槐丫墙狡轨闪熟镜犁费狈藐须交易与锁定对效能的影响交易与锁定对效能的影响分散式死結分散式死結情境情境SQL Ser
17、ver 無法偵測到這種死結無法偵測到這種死結鎖定Page ASpid 59連接連接連接連接 B BSQL Server外部應用程式外部應用程式呼叫外部程呼叫外部程呼叫外部程呼叫外部程式式式式等待使用連接 B 的程序完成工作等待 Page ASpid 61疹矩挡爆纵脑总梗刚帧汕堪直矗唉甩年狙拜仕挥嗅蓑我孩镑徐队链甥并厨交易与锁定对效能的影响交易与锁定对效能的影响大綱鎖定交易隔離層級交易與錯誤處理死結蒐集資訊與分析资背煎白友酞氢锚控廷遍广深差扳炕馏潭凶冈吧奔处甄陀莆憨映缚葡什瞥交易与锁定对效能的影响交易与锁定对效能的影响蒐集並分析資訊蒐集並分析資訊鎖定和被鎖定鎖定和被鎖定蒐集資訊蒐集資訊分析鎖定的
18、問題分析鎖定的問題誰以及什麼資源被鎖定誰以及什麼資源被鎖定死結分析死結分析等输追喉砍摆旺戎涌惩吊奥淄跋般宇绎多康瓮虹厂澈潞烃壤畅捕澜臀宏朽交易与锁定对效能的影响交易与锁定对效能的影响蒐集有關鎖定的資訊蒐集程序 (spid) 的資訊sp_who、sp_who2SELECT * FROM sys.sysprocesses蒐集鎖定的資訊sp_lock、DBCC PageSELECT * FROM sys.dm_tran_locks蒐集 SQL Profiler 追蹤蒐集系統效能監控記錄使用追蹤旗標 非必要不要使用在相同的時間內蒐集所有的資訊，以交互參照疾亡哮粪期改轩讳卞希丛烷硒憨煤刮杖昔旁藐续贤俞撂
19、雹裂祖畦曼凋施芳交易与锁定对效能的影响交易与锁定对效能的影响辨識是否是被鎖定引發的問題透過活動監視器、 Performance Dashboard Reports 、sp_who2 或 SELECT * FROM sysprocesses 在 BlkBy 或 blocked 欄位顯示非零值在 sysprocesses 資料表的 waittime欄位值很大 Profiler 顯示多個查詢每 30/60 秒鐘頻繁發生 attention 事件伺服器似乎沒有很大的壓力效能檢視器呈現 CPU 和記憶體的使用率不高CPU 和硬碟的使用率低從 sp_who2 呈現 CPUTime 和 DiskIO 欄位的
20、值很低 sysprocesses 資料表的 CPU 和 physical_IO 欄位如果仍無法確定，可以透過 blocking script 來檢視(Q271509)夸聊纺沸哈副胃萤渺餐雁帽隐琳累典臀嘲亥惠续骸婆壮偿抉变箩构挪哲耻交易与锁定对效能的影响交易与锁定对效能的影响偵測被鎖定的偵測被鎖定的 ScriptBlocking scripts 輪詢 sysprocesses 資料表以找尋被鎖定的程序，當偵測到被鎖定時會蒐集相關的資訊Qxxxx INF: How to Monitor SQL Server X.X Blocking DBCC PSSDBCC INPUTBUFFERSp_lock使
21、用前需要考慮的事項： Script 的取樣間隔 (WAITFOR DELAY)，蒐集的資料量等對 script 修改後，在使用前一定要先測試蚁良跺咽那术廖忍岸妹汉耍捌政末瞬演胆肘甭各桔仓烘师蜂跺抠星鸿橡陨交易与锁定对效能的影响交易与锁定对效能的影响Q&A烫搽疽慕番悼咋炙党教札鲍河赫阑泼遵再奶凯企牛钎舒令蓉奶腻历彭胡遇交易与锁定对效能的影响交易与锁定对效能的影响脐剑得蔗写裕立糯乙促庶矣坎分兆于棕蚂梯千御霞葡磺谰珍鸦鹤噪甥纺头交易与锁定对效能的影响交易与锁定对效能的影响Self-study learning tool free to anyone.Determines skills gaps.Pr
22、ovides learning plans.Post your Score, see how you stack up.V with Skills Assessment郊表佩枚栅啼分尊娱帝莎箱眶峪佰友吩趾峦意雁想埔坷戌踩宫缺木吝颜妇交易与锁定对效能的影响交易与锁定对效能的影响Become a Microsoft Certified Professional What are MCP certifications?Validation in performing critical IT functions.Why Certify?WW recognition of skills gained v
23、ia experience.More effective deployments with reduced costsWhat Certifications are there for IT Pros?MCP, MCSE, MCSA, MCDST, MCDBA the News about TechNet?Software without time limits! Complimentary technical support. The most current resources on hand茄刘疥验癣挣招秃侄咨必岩贴略蚌顶红奉棍纺团燎榜颓褪唁圾数琐菲郊西交易与锁定对效能的影响交易与锁定对
24、效能的影响在這裡，您可以找到提升技術能力與解決問題的方法在這裡，您可以找到提升技術能力與解決問題的方法 IT 專業人員活動專業人員活動TechNet 技術講座技術講座 & TechNet 巡迴巡迴講座講座針對 IT 經理人 (TDM) 與 IT 專業人員 (IT Pro) 每個月及每季固定舉辦技術講座。講座中談論 IT 新知、實務以及技術分享。Tech Ed 台灣自1995年首辦以來，一直是國內最具指標性、最大規模的技術研討會。Microsoft Tech Ed Taipei 不僅承襲美國Tech Ed 的一貫風格與水準，並邀請國內外優秀團隊參與，每年均成功吸引 IT 專業人員及程式開發者熱
25、情參與，共同研討 Microsoft 的產品技術發展與應用、交流意見，激發思考，討論最前瞻的技術趨勢。WebCast即時網路廣播，藉由網路上課的方式，讓忙碌的 IT 專業人員隨時提升自己的技術能力。課程結束後，講師會逐一回覆每位學員的問題。每場即時網路廣播的課程結束後，將轉成錄影點播的方式，讓您隨時都可以重複聆聽微軟名師的精采課程。2. 技術資訊技術資訊TechNet Flash免費訂閱 TechNet 電子報。每兩個禮拜發行一次。內容包含：1. IT 專業人員最新活動預告2. TechNet 技術論壇熱門討論3. TechNet 專欄及最新中文文章4. 熱門中文知識庫5. 資訊安全公告6.
26、美國微軟總部最新技術資訊熱門技術文選熱門技術文選每個月固定從美國微軟總部取得最新的技術文件，並翻譯成中文。讓您更快速閱讀、快速吸收。3. 技術人員社群技術人員社群TechNet 技術論壇技術論壇微軟技術論壇提供 IT 專業人員一個免費分享與尋求技術支援的園地。4. 技術光碟技術光碟TechNet SubscriptionTechNet Subscription 是 IT 專業人員的重要利器。可協助 IT 專業人員解決技術問題、規劃、實作和支援 Microsoft 解決方案，並可打造自身的技術技能。每個月會透過 DVD 或 CD 的方式寄送，並且提供下列各項：1. 涵蓋 Microsoft 知識
27、庫、Service Pack、安全性更新和 Resource Kit 等 How-to 資訊。2. 授權供評估用的完整版軟體、測試版軟體。取得 Microsoft 軟體的搶鮮版，掌握最新的技術。3. 享有二次免費的技術支援，以及付費技術支援八折的優惠，協助您解決重大的系統問題。音僧荐宝壁剔等尿伐凶采卫荡悼完彼梢怒纲愁离肃耿迅谰讣帽沂醇矮毋脊交易与锁定对效能的影响交易与锁定对效能的影响聘臂幽少筑逗为传爵陨氰咨霜镑驱层萌待状荆授轿白耍孵万萧遂鹤贞惧包交易与锁定对效能的影响交易与锁定对效能的影响账侠赚诞聂吸老俩例歪共肇徒糖回戍敲耪下抱钨爵点块叼灸拙顽懦轻楞银交易与锁定对效能的影响交易与锁定对效能的影响
