13G-事務(wù)處理

1、第13章 事務(wù)處理事務(wù)的定義1事務(wù)的性質(zhì)-ACIDACID原子性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)失敗，也是如此。2事務(wù)處理控制語句BEGIN TRANSACTIONCOMMIT TRANSACTIONROLLBACK TRANSACTION3事務(wù)缺陷-臟讀臟讀：在事務(wù)提交前，它對數(shù)據(jù)所做的修改對其他事務(wù)來說是可見的。是危害最大的事務(wù)完整性缺陷。Set Transaction Is

2、olation Level Read Committed;Use DatabaseName- Transaction-1Begin TransactionUpdate Customer Set Nickname=XXX Where CustomerID=1-Transaction-2Set Transaction Isolation Level Read UnCommittedSelect Nickname From Customer Where CustomerID=1-Transaction-1Commit Transaction4事務(wù)缺陷-不可重復(fù)讀不可重復(fù)讀：如果隔離性是完全的，一個(gè)事

3、務(wù)不應(yīng)看到該事務(wù)外的數(shù)據(jù)修改。- Transaction-1Set Transaction Isolation Level Read Committed;Use DatabaseNameBegin TransactionSelect Nickname From Customer Where CustomerID=1-Transaction-2 Update Customer Set Nickname=XXX Where CustomerID=1 Commit Transaction-T-2提交后T-1再次檢索同一行，如能看到T-2更新后的值，則說明存在不可重復(fù)讀缺陷。Select Nickna

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

5、nsaction-2如果隔離性是完全的，T2再次執(zhí)行同樣的Select命令時(shí)，應(yīng)得到與以前相同的記錄集。Select * From Customer Where Nickname=MissyCommit Transaction6隔離級別-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ì)受損，而不能處理行爭用。級別2：已提交讀： SQL Server默認(rèn)隔離級別。級別3：可重復(fù)讀

6、：能夠防止臟讀和不可重復(fù)讀。7隔離級別-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ù)試圖更新正在被更新的資源，它將被阻塞。8隔離級別-3已提交讀快照：其行為與已提交讀相同，但寫入事務(wù)和讀取事務(wù)之間不存在爭用。9SQL Server 的鎖機(jī)制

7、-鎖可以認(rèn)為SQL Server中有兩個(gè)進(jìn)程：查詢處理器和鎖管理器。鎖管理器的目標(biāo)是通過創(chuàng)建和釋放鎖盡可能高效地確保事務(wù)完整性。每個(gè)鎖都有的三個(gè)屬性：粒度(Granularity)：鎖的大小模式(Mode)：鎖的類型持續(xù)時(shí)間(Duration)：鎖的隔離模式10SQL Server 的鎖機(jī)制-鎖粒度鎖控制的數(shù)據(jù)可以小到一行記錄，也可大到整個(gè)數(shù)據(jù)庫。根據(jù)鎖的粒度，可采用不同的鎖組合來滿足特定的鎖定需求?？杉渔i的對象：記錄、表、頁、區(qū)域和數(shù)據(jù)庫等。鎖的大小描述行鎖只鎖一行。最小鎖，SQL Server 無列鎖。頁鎖鎖定一個(gè)頁面，即8KB，在一個(gè)頁面可有一行或多行。區(qū)(extent)鎖鎖定8個(gè)頁面，

8、即64KB。表鎖鎖定整個(gè)表。數(shù)據(jù)庫鎖鎖定整個(gè)數(shù)據(jù)庫，通常是在修改數(shù)據(jù)庫架構(gòu)時(shí)使用該鎖。鍵鎖（范圍鎖）鎖定索引中的節(jié)點(diǎn)。11SQL Server 的鎖機(jī)制-鎖管理器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)鎖定簡化了管理工作。12SQL

9、Server 的鎖機(jī)制-鎖切換25個(gè)行鎖可能升級為一個(gè)頁鎖。然后，如果在同一個(gè)區(qū)的其他4個(gè)頁面中包含另外25個(gè)被鎖定的行，頁鎖和這25個(gè)行鎖可能被升級為一個(gè)區(qū)鎖，因?yàn)樵搮^(qū)中，有超過50%的頁面都受到了鎖定的影響。如果有足夠的區(qū)被鎖住，這些鎖可能升級為一個(gè)表鎖。13SQL 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，其他

10、事務(wù)都不能對X加任何類型的鎖，直到T釋放X上的鎖，從而避免其他事務(wù)讀取不一致的數(shù)據(jù)。更新鎖U：它并不是事務(wù)執(zhí)行更新時(shí)使用的鎖（此時(shí)應(yīng)使用排他鎖）。更新鎖意味著事務(wù)即將使用排他鎖，它當(dāng)前正在掃描數(shù)據(jù)，以確定要使用排他鎖鎖定哪行。用來預(yù)定對某對象施加X鎖，它允許其他事務(wù)讀，但不允許再施加U鎖和X鎖。當(dāng)被讀取的對象要被更新時(shí)，升級為X鎖。U鎖直到事務(wù)結(jié)束時(shí)才能釋放。14SQL Server 的鎖機(jī)制-鎖模式/類型-2意向鎖：是一種用于警示的鎖，告訴其他事務(wù)即將要發(fā)生一些事情。主要目的是提高性能。所以類型和粒度都可以使用意向鎖。如：IS、SIX、IX。意向鎖是共享鎖或排他鎖的預(yù)先請求，解決了兩個(gè)性能問

11、題：層次鎖定和永久鎖阻塞。SQL Server 使用意向鎖向更高的數(shù)據(jù)層次傳播鎖。如：當(dāng)事務(wù)1獲得一個(gè)行鎖后，它將同時(shí)對該行所屬的頁和表放置意向鎖。15SQL Server 的鎖機(jī)制-鎖模式/類型-3通過允許獲得鎖的事務(wù)在更大范圍設(shè)置意向鎖，將檢查鎖的事務(wù)的工作交給了建立鎖的事務(wù)來完成。這樣，雖然建立鎖的事務(wù)需要同時(shí)加上三個(gè)鎖，但以后其他事務(wù)在檢查鎖時(shí)就可以少做數(shù)百次的搜索。意向鎖還避免了嚴(yán)重的共享鎖爭用問題，稱為永久鎖阻塞。如：事務(wù)獲得意向排他鎖IX后，其他事務(wù)就無法獲得共享鎖。意向排他鎖并非完全的排他鎖，但它表示即將獲得排他鎖。16SQL Server 的鎖機(jī)制-鎖模式/類型-2架構(gòu)鎖（S

12、ch-M, Sch-S）用于保護(hù)數(shù)據(jù)庫架構(gòu)。在任何查詢執(zhí)行期間，SQL Server都將使用架構(gòu)穩(wěn)定鎖（Sch-S）來禁止執(zhí)行任何數(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ù)。17SQL Server 的鎖機(jī)制-鎖的兼容性T2請求T1有IS鎖S鎖U鎖IX鎖SIX鎖X鎖意向共享鎖(IS)是是是是是是共享鎖(S)是是是否否否更新鎖(U)是是否否否否意向排他鎖(IX)是否否是否否意向排他共享鎖(SIX)是否否否否否排他鎖(X)否否否否否

13、否18SQL Server 的鎖機(jī)制-鎖的持續(xù)時(shí)間它由事務(wù)的隔離級別決定。隔離級別越嚴(yán)，鎖的持續(xù)時(shí)間越長。隔離級別共享鎖持續(xù)時(shí)間排他鎖持續(xù)時(shí)間未提交讀無持續(xù)時(shí)間只確保數(shù)據(jù)不受損，在其他情況下，不使用排他鎖。已提交讀在事務(wù)讀取數(shù)據(jù)期間保持鎖定保持到事務(wù)提可重復(fù)讀在事務(wù)提交前保持鎖定保持到事務(wù)提可序列化在事務(wù)提交前保持鎖定保持到事務(wù)提，同時(shí)使用鍵鎖防止插入?？煺崭綦xn/an/a19監(jiān)視鎖定和阻塞使用Management Studio的“摘要”-“報(bào)表”。使用活動(dòng)監(jiān)視器：“對象資源管理器”-“管理”-“活動(dòng)監(jiān)視器”，它每5秒自動(dòng)刷新一次。使用Profiler:跟蹤事件Error and Warnin

14、gsBlocked Process Report必須啟動(dòng)高級選項(xiàng)并設(shè)置Blocked Process Threshold如：sp_Configure show advanced options,1;GoReconfigure;Gosp_Configure blocked process threshold,1; /*1秒*/GoReconfigure;20死鎖死鎖發(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í)需要，否則不要提高隔