事務(wù)處理1.ppt

1、第13章 事務(wù)處理,事務(wù)的定義,事務(wù)的性質(zhì)-ACID,ACID 原子性A: 事務(wù)處理是組成對象的最小單元。要么整體成功；要么整體失敗。 一致性C：所有的改變都以一致的方式進(jìn)行，系統(tǒng)從一個(gè)有效狀態(tài)進(jìn)入另一個(gè)有效狀態(tài)。 隔離性I：在同一時(shí)間進(jìn)行的事務(wù)處理相互獨(dú)立。 持久性D：如果提交了一個(gè)事務(wù)處理，產(chǎn)生的效果將是不變的，即使系統(tǒng)失敗，也是如此。,事務(wù)處理控制語句,BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK TRANSACTION,事務(wù)缺陷-臟讀,臟讀：在事務(wù)提交前，它對數(shù)據(jù)所做的修改對其他事務(wù)來說是可見的。是危害最大的事務(wù)完整性缺陷。 Set Tra

2、nsaction Isolation Level Read Committed; Use DatabaseName - Transaction-1 Begin Transaction Update Customer Set Nickname=XXX Where CustomerID=1 -Transaction-2 Set Transaction Isolation Level Read UnCommitted Select Nickname From Customer Where CustomerID=1 -Transaction-1 Commit Transaction,事務(wù)缺陷-不可重復(fù)

3、讀,不可重復(fù)讀：如果隔離性是完全的，一個(gè)事務(wù)不應(yīng)看到該事務(wù)外的數(shù)據(jù)修改。 - Transaction-1 Set Transaction Isolation Level Read Committed; Use DatabaseName Begin Transaction Select Nickname From Customer Where CustomerID=1 -Transaction-2 Update Customer Set Nickname=XXX Where CustomerID=1 Commit Transaction -T-2提交后T-1再次檢索同一行，如能看到T-2更新后的

4、值，則說明存在不可重復(fù)讀缺陷。 Select Nickname From Customer Where CustomerID=1 Commit Transaction,事務(wù)缺陷-虛幻行,是危害最小的事務(wù)完整性缺陷。也是指事務(wù)的更新操作不僅影響結(jié)果集的數(shù)據(jù)值，還導(dǎo)致Select語句返回不同的記錄集。 - Transaction-2 Begin Transaction Select * From Customer Where Nickname=Missy -Transaction-1 Begin Transaction Update Customer Set Nickname=Missy Wher

5、e CustomerID=1 Commit Transaction -Transaction-2如果隔離性是完全的，T2再次執(zhí)行同樣的Select命令時(shí)，應(yīng)得到與以前相同的記錄集。 Select * From Customer Where Nickname=Missy Commit Transaction,隔離級別-1,數(shù)據(jù)庫可通過調(diào)整隔離級別來控制允許的事務(wù)缺陷。ANSI SQL-92定義了4個(gè)隔離級別。SQL Server使用鎖來實(shí)現(xiàn)隔離級別，默認(rèn)隔離級別為已提交讀。 級別1：未提交讀：最不嚴(yán)格的隔離級別，不能防止任何事務(wù)缺陷，根本沒有在事務(wù)之間提供隔離。這種模式只能確保數(shù)據(jù)不會(huì)受損，而不

6、能處理行爭用。 級別2：已提交讀： SQL Server默認(rèn)隔離級別。 級別3：可重復(fù)讀：能夠防止臟讀和不可重復(fù)讀。,隔離級別-2,級別4：可序列化：最嚴(yán)格的隔離級別，能夠防止所有事務(wù)缺陷。這種設(shè)置雖然提供了完全的事務(wù)隔離性，但將導(dǎo)致嚴(yán)重的鎖爭用并降低性能。 SQL Server 新增隔離： 快照隔離：它創(chuàng)建要更新的數(shù)據(jù)的快照拷貝(TempDB)。在更新期間，閱讀事務(wù)能夠繼續(xù)讀取原始數(shù)據(jù)，更新被提交后，將覆蓋原始數(shù)據(jù)?？煺崭綦x消除了讀取事務(wù)和寫入事務(wù)之間的爭用。但爭用并未完全消失，而是轉(zhuǎn)移到寫出事務(wù)和寫入事務(wù)之間。如果第二個(gè)寫入事務(wù)試圖更新正在被更新的資源，它將被阻塞。,隔離級別-3,已提交讀

7、快照：其行為與已提交讀相同，但寫入事務(wù)和讀取事務(wù)之間不存在爭用。,SQL Server 的鎖機(jī)制-鎖,可以認(rèn)為SQL Server中有兩個(gè)進(jìn)程：查詢處理器和鎖管理器。 鎖管理器的目標(biāo)是通過創(chuàng)建和釋放鎖盡可能高效地確保事務(wù)完整性。 每個(gè)鎖都有的三個(gè)屬性： 粒度(Granularity)：鎖的大小 模式(Mode)：鎖的類型 持續(xù)時(shí)間(Duration)：鎖的隔離模式,SQL Server 的鎖機(jī)制-鎖粒度,鎖控制的數(shù)據(jù)可以小到一行記錄，也可大到整個(gè)數(shù)據(jù)庫。根據(jù)鎖的粒度，可采用不同的鎖組合來滿足特定的鎖定需求。 可加鎖的對象：記錄、表、頁、區(qū)域和數(shù)據(jù)庫等。,SQL Server 的鎖機(jī)制-鎖管理器

8、,SQL Server 具有自動(dòng)和強(qiáng)制鎖功能。 SQL Server 的鎖管理器試圖在鎖大小和數(shù)量之間尋求平衡，以獲得最佳的性能。矛盾的焦點(diǎn)在并發(fā)性（較小的鎖可以允許更多的事務(wù)同時(shí)訪問數(shù)據(jù)）和性能（鎖越少速度越快）。為達(dá)到平衡，鎖管理器動(dòng)態(tài)地從一組鎖切換為另一組鎖。 動(dòng)態(tài)鎖的好處： 無需編程，就可自動(dòng)在性能和并發(fā)性之間取得最佳平衡。 隨著數(shù)據(jù)庫的增長，鎖管理器將不斷應(yīng)用合適的鎖粒度，確保數(shù)據(jù)庫保持良好的性能。 動(dòng)態(tài)鎖定簡化了管理工作。,SQL Server 的鎖機(jī)制-鎖切換,25個(gè)行鎖可能升級為一個(gè)頁鎖。 然后，如果在同一個(gè)區(qū)的其他4個(gè)頁面中包含另外25個(gè)被鎖定的行，頁鎖和這25個(gè)行鎖可能被升

9、級為一個(gè)區(qū)鎖，因?yàn)樵搮^(qū)中，有超過50%的頁面都受到了鎖定的影響。 如果有足夠的區(qū)被鎖住，這些鎖可能升級為一個(gè)表鎖。,SQL Server 的鎖機(jī)制-鎖模式/類型-1,鎖模式?jīng)Q定了鎖的用途。 共享鎖S：讀取鎖，表示“正在查看數(shù)據(jù)”。若事務(wù)T對數(shù)據(jù)對象X加上共享鎖，則其他事務(wù)只能對X再加共享鎖，不能加排他鎖，從而保證了其他事務(wù)可以讀X，但在T釋放X上的鎖之前不能對X做任何修改。 排他鎖X：寫鎖，若事務(wù)T對數(shù)據(jù)對象X加上排他鎖，則只允許事務(wù)T獨(dú)占數(shù)據(jù)項(xiàng)X，其他事務(wù)都不能對X加任何類型的鎖，直到T釋放X上的鎖，從而避免其他事務(wù)讀取不一致的數(shù)據(jù)。 更新鎖U：它并不是事務(wù)執(zhí)行更新時(shí)使用的鎖（此時(shí)應(yīng)使用排他

10、鎖）。更新鎖意味著事務(wù)即將使用排他鎖，它當(dāng)前正在掃描數(shù)據(jù)，以確定要使用排他鎖鎖定哪行。用來預(yù)定對某對象施加X鎖，它允許其他事務(wù)讀，但不允許再施加U鎖和X鎖。當(dāng)被讀取的對象要被更新時(shí)，升級為X鎖。U鎖直到事務(wù)結(jié)束時(shí)才能釋放。,SQL Server 的鎖機(jī)制-鎖模式/類型-2,意向鎖：是一種用于警示的鎖，告訴其他事務(wù)即將要發(fā)生一些事情。主要目的是提高性能。所以類型和粒度都可以使用意向鎖。如：IS、SIX、IX。 意向鎖是共享鎖或派他鎖的預(yù)先請求，解決了兩個(gè)性能問題：層次鎖定和永久鎖阻塞。 SQL Server 使用意向鎖向更高的數(shù)據(jù)層次傳播鎖。 如：當(dāng)事務(wù)1獲得一個(gè)行鎖后，它將同時(shí)對該行所屬的頁和

11、表放置意向鎖。,SQL Server 的鎖機(jī)制-鎖模式/類型-3,通過允許獲得鎖的事務(wù)在更大范圍設(shè)置意向鎖，將檢查鎖的事務(wù)的工作交給了建立鎖的事務(wù)來完成。這樣，雖然建立鎖的事務(wù)需要同時(shí)加上三個(gè)鎖，但以后其他事務(wù)在檢查鎖時(shí)就可以少做數(shù)百次的搜索。 意向鎖還避免了嚴(yán)重的共享鎖爭用問題，稱為永久鎖阻塞。如：事務(wù)獲得意向排他鎖IX后，其他事務(wù)就無法獲得共享鎖。意向排他鎖并非完全的排他鎖，但它表示即將獲得排他鎖。,SQL Server 的鎖機(jī)制-鎖模式/類型-2,架構(gòu)鎖（Sch-M, Sch-S） 用于保護(hù)數(shù)據(jù)庫架構(gòu)。 在任何查詢執(zhí)行期間，SQL Server都將使用架構(gòu)穩(wěn)定鎖（Sch-S）來禁止執(zhí)行任

12、何數(shù)據(jù)定義語言（DDL）命令。 僅當(dāng)SQL Server調(diào)整數(shù)據(jù)庫的物理結(jié)構(gòu)時(shí)才使用架構(gòu)修改鎖（Sch-M）。如果SQL Server向表中添加列，在該架構(gòu)修改操作期間，架構(gòu)鎖將禁止其他所有事務(wù)查看或修改相應(yīng)的數(shù)據(jù)。,SQL Server 的鎖機(jī)制-鎖的兼容性,SQL Server 的鎖機(jī)制-鎖的持續(xù)時(shí)間,它由事務(wù)的隔離級別決定。隔離級別越嚴(yán)，鎖的持續(xù)時(shí)間越長。,監(jiān)視鎖定和阻塞,使用Management Studio的“摘要”-“報(bào)表”。 使用活動(dòng)監(jiān)視器：“對象資源管理器”-“管理”-“活動(dòng)監(jiān)視器”，它每5秒自動(dòng)刷新一次。 使用Profiler:跟蹤事件Error and WarningsBl

13、ocked Process Report 必須啟動(dòng)高級選項(xiàng)并設(shè)置Blocked Process Threshold如： sp_Configure show advanced options,1; Go Reconfigure; Go sp_Configure blocked process threshold,1; /*1秒*/ Go Reconfigure;,死鎖,死鎖發(fā)生時(shí)， SQL Server 選擇完成工作量最少的事務(wù)作為死鎖犧牲品，以讓其他事務(wù)繼續(xù)執(zhí)行。 事務(wù)也可自愿成為死鎖犧牲品，使用Set Deadlock Priority LOW 避免死鎖應(yīng)遵循的原則： 事務(wù)應(yīng)簡明扼要，不包括任何不應(yīng)放在事務(wù)中的代碼。以使事務(wù)應(yīng)盡量小且應(yīng)盡快提交。 絕不要編寫依賴用戶輸入的事務(wù)。 編寫批處理和過程時(shí)，盡可能讓它們以相同的順序獲得鎖。 規(guī)劃物理架構(gòu)和小心選擇聚集索引，確?？赡鼙煌瑫r(shí)查詢的數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)頁盡可能近的地方。減小鎖的影響范圍有助于防止鎖升級，且鎖粒度越小，越有助于避免鎖爭用。 除非確實(shí)需要，否則不要提高隔離級別，因?yàn)楦綦x級別越高，鎖的持續(xù)時(shí)間越長