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