第05章 管理事務(wù).ppt

1 第五章管理事務(wù) 事務(wù)的概念事務(wù)的特性事務(wù)的類型事務(wù)的工作機(jī)制鎖 2 事務(wù)的由來 使用DELETE命令或UPDATE命令對(duì)數(shù)據(jù)庫進(jìn)行更新時(shí) 一次只能操作一個(gè)表 這會(huì)帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題 例如企業(yè)取消了后勤部 需要將后勤部從department表中刪除 要修改department表 而employee表中的部門編號(hào)與后勤部相對(duì)應(yīng)的員工也應(yīng)刪除 因此 兩個(gè)表都需要修改 這種修改只能通過兩條DELETE語句進(jìn)行 假設(shè)后勤部編號(hào)為1012第一條DELETE語句修改department表deletefromdepartmentwheredept id 1012 第二條DELETE語句修改employee表deletefromemployeewheredept id 1012 3 在執(zhí)行第一條DELETE語句后 數(shù)據(jù)庫中的數(shù)據(jù)已處于不一致的狀態(tài) 因?yàn)榇藭r(shí)已經(jīng)沒有后勤部了 但employee表中仍然保存著屬于后勤部的員工記錄 只有執(zhí)行了第二條DELETE語句后 數(shù)據(jù)才重新處于一致狀態(tài) 但是如果執(zhí)行完第一條語句后 計(jì)算機(jī)突然出現(xiàn)故障 無法再繼續(xù)執(zhí)行第二條DELETE語句 則數(shù)據(jù)庫中的數(shù)據(jù)將處于永遠(yuǎn)不一致的狀態(tài) 因此必須保證這兩條DELETE語句同時(shí)執(zhí)行 為解決類似的問題 數(shù)據(jù)庫系統(tǒng)通常都引入了事務(wù)Transaction的概念 4 事務(wù)的概念及特性 事務(wù) 指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作 而這些邏輯工作單元需要具有原子性 一致性 隔離性和持久性四個(gè)屬性 統(tǒng)稱ACID特性 原子性 指事務(wù)必須是原子工作單元 即對(duì)于事務(wù)所進(jìn)行數(shù)據(jù)修改 要么全都執(zhí)行 要么全都不執(zhí)行 5 一致性 指事務(wù)在完成時(shí) 必須使所有的數(shù)據(jù)都保持一致性狀態(tài) 而且在相關(guān)數(shù)據(jù)庫中 所有規(guī)則都必須應(yīng)用于事務(wù)的修改 以保持所有數(shù)據(jù)的完整性 事務(wù)結(jié)束時(shí) 所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的 6 隔離性 指由并發(fā)事務(wù)所做的修改必須與任何其他并發(fā)事務(wù)所做的修改相隔離 事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài) 要么是被另一并發(fā)事務(wù)修改之前的狀態(tài) 要么是被另一事務(wù)修改之后的狀態(tài) 即事務(wù)不會(huì)查看正在由另一個(gè)并發(fā)事務(wù)正在修改的數(shù)據(jù) 持久性 指事務(wù)完成之后 它對(duì)于系統(tǒng)的影響是永久性的 即使出現(xiàn)系統(tǒng)故障也是如此 7 事務(wù)的類型 隱式事務(wù) 每次執(zhí)行SQLServer的任何數(shù)據(jù)修改語句時(shí) 它都是一個(gè)隱式事務(wù) 例如 下列SQL語句是一個(gè)獨(dú)立事務(wù) Inserttalbevalues 1 abc Updatetablesetcol1 5wherecol1 1Deletefromtable1wherecol1 5go 8 顯式事務(wù) 通常在程序中用BEGINTRANSACTION命令來標(biāo)識(shí)一個(gè)事務(wù)的開始 用COMMITTRANSACTION命令標(biāo)識(shí)事務(wù)結(jié)束 這兩個(gè)命令之間的所有語句被視為一體 只有執(zhí)行到COMMITTRANSACTION命令時(shí) 事務(wù)中對(duì)數(shù)據(jù)庫的更新操作才算確認(rèn) 和BEGIN END命令類似 這兩個(gè)命令也可以進(jìn)行嵌套 即事務(wù)可以嵌套執(zhí)行 9 這兩個(gè)命令的語法如下BEGINTRAN SACTION transaction name tran name variable COMMIT TRAN SACTION transaction name tran name variable 其中BEGINTRANSACTION可以縮寫為BEGINTRAN COMMITTRANSACTION可以縮寫為COMMITTRAN或COMMIT transaction name 指定事務(wù)的名稱 只有前32個(gè)字符會(huì)被系統(tǒng)識(shí)別 tran name variable 用變量來指定事務(wù)的名稱變量 只能聲明為CHARVARCHARNCHAR或NVARCHAR類型 10 例 刪除后勤部declare transaction namevarchar 32 select transaction name my transaction delete begintransaction transaction namegousesamplegodeletefromdepartmentwheredept id 1012 godeletefromemployeewheredept id 1012 gocommittransactionmy transaction deletego 11 事務(wù)回滾 事務(wù)回滾 TransactionRollback 是指當(dāng)事務(wù)中的某一語句執(zhí)行失敗時(shí) 將對(duì)數(shù)據(jù)庫的操作恢復(fù)到事務(wù)執(zhí)行前或某個(gè)指定位置 事務(wù)回滾使用ROLLBACKTRANSACTION命令 其語法如下ROLLBACK TRAN SACTION transaction name tran name variable savepoint name savepoint variable 其中savepoint name和 savepoint variable參數(shù)用于指定回滾到某一指定位置 如果要讓事務(wù)回滾到指定位置 則需要在事務(wù)中設(shè)定保存點(diǎn)SavePoint 12 所謂保存點(diǎn)是指定其所在位置之前的事務(wù)語句不能回滾的語句 即此語句前面的操作被視為有效 其語法如下 SAVETRAN SACTION savepoint name savepoint variable 各參數(shù)說明如下 savepoint name 指定保存點(diǎn)的名稱 同事務(wù)的名稱一樣 只有前32個(gè)字符會(huì)被系統(tǒng)識(shí)別 savepoint variable 用變量來指定保存點(diǎn)的名稱變量 只能聲明為CHAR VARCHAR NCHAR或NVARCHAR類型 13 例 刪除后勤部再將后勤部的職工劃歸到經(jīng)理室begintransactionmy transaction deleteusesamplegodeletefromdepartmentwheredept id 1012 savetransactionafter deleteupdateemployeesetdept id 1001 wheredept id 1012 if error 0or rowcount 0thenbeginrollbacktranafter delete 回滾到保存點(diǎn)after delete如果使用rollbackmy transaction delete則會(huì)回滾到事務(wù)開始前 Committranprint 更新員工信息表時(shí)產(chǎn)生錯(cuò)誤 Returnendcommittransactionmy transaction deletego 14 事務(wù)工作機(jī)制 例如 begintranInserttable1values 1 abc Updatetable1setcol1 5wherecol1 1Deletefromtable1wherecol1 5Committran 15 1 當(dāng)begintran語句到達(dá)數(shù)據(jù)庫時(shí) sqlserver分析出這是事務(wù)的開始 SQLserver找到下一個(gè)可用的內(nèi)存日志頁面 并給新事務(wù)分配一個(gè)事務(wù)ID 2 接著運(yùn)行插入語句 新的行被記錄到事務(wù)日志中 數(shù)據(jù)頁面在內(nèi)存中進(jìn)行修改 若所需頁面不在內(nèi)存中 則從磁盤調(diào)出 3 update語句以類似方式運(yùn)行 4 當(dāng)sqlserver收到comminttran時(shí) 日志頁面被寫道數(shù)據(jù)庫的日志設(shè)備上 這樣才能保證日志頁面可被恢復(fù) 由于日志變化寫入了硬盤 它保證事務(wù)是可恢復(fù)的 即使掉電了或在數(shù)據(jù)頁寫入磁盤時(shí)數(shù)據(jù)庫崩潰了 也能進(jìn)行事務(wù)恢復(fù) 16 檢查點(diǎn)處理 問題 系統(tǒng)何時(shí)將數(shù)據(jù)頁寫入磁盤呢 日志頁面是在committran時(shí)寫入磁盤的 那么何時(shí)將數(shù)據(jù)頁面寫入磁盤呢 在處理檢查點(diǎn)時(shí) 檢查點(diǎn)的概念 是sqlserver將數(shù)據(jù)頁面從內(nèi)存拷貝到磁盤時(shí)的內(nèi)部處理點(diǎn) 檢查點(diǎn)幫助確?；謴?fù)已提交事務(wù)而不會(huì)花費(fèi)額外的時(shí)間 當(dāng)檢查點(diǎn)出現(xiàn)時(shí) 將寫入一個(gè)日志項(xiàng)以指示所有修改了的頁面已寫入磁盤 17 鎖 鎖的概念 鎖 Lock 是在多用戶環(huán)境下對(duì)資源訪問的一種限制機(jī)制 當(dāng)對(duì)一個(gè)數(shù)據(jù)源加鎖后 此數(shù)據(jù)源就有了一定的訪問限制 我們就稱對(duì)此數(shù)據(jù)源進(jìn)行了鎖定 在SQLServer中可以對(duì)以下的對(duì)象進(jìn)行鎖定 數(shù)據(jù)行 數(shù)據(jù)頁中的單行數(shù)據(jù)索引行 索引頁中的單行數(shù)據(jù)即索引的鍵值頁 頁是SQLServer存取數(shù)據(jù)的基本單位其大小為8KB盤區(qū) 一個(gè)盤區(qū)由8個(gè)連續(xù)的頁組成表數(shù)據(jù)庫 18 鎖的類別 在SQLServer中 從數(shù)據(jù)庫系統(tǒng)的角度來看 鎖分為以下三種類型 獨(dú)占鎖ExclusiveLock 獨(dú)占鎖鎖定的資源只允許進(jìn)行鎖定操作的程序使用 其它任何對(duì)它的操作均不會(huì)被接受 執(zhí)行數(shù)據(jù)更新命令 即INSERT UPDATE或DELETE命令時(shí)SQLServer會(huì)自動(dòng)使用獨(dú)占鎖 但當(dāng)對(duì)象上有其它鎖存在時(shí) 無法對(duì)其加獨(dú)占鎖 獨(dú)占鎖一直到事務(wù)結(jié)束才能被釋放 19 共享鎖SharedLock 共享鎖鎖定的資源可以被其它用戶讀取 但其它用戶不能修改它 在SELECT命令執(zhí)行時(shí) SQLServer通常會(huì)對(duì)對(duì)象進(jìn)行共享鎖鎖定 通常加共享鎖的數(shù)據(jù)頁被讀取完畢后 共享鎖就會(huì)立即被釋放更新鎖UpdateLock 更新鎖是為了防止死鎖而設(shè)立的 當(dāng)SQLServer準(zhǔn)備更新數(shù)據(jù)時(shí) 它首先對(duì)數(shù)據(jù)對(duì)象作更新鎖鎖定 這樣數(shù)據(jù)將不能被修改 但可以讀取 等到SQLServer確定要進(jìn)行更新數(shù)據(jù)操作時(shí) 它會(huì)自動(dòng)將更新鎖換為獨(dú)占鎖 但當(dāng)對(duì)象上有其它鎖存在時(shí) 無法對(duì)其作更新鎖鎖定 20 查看鎖 可以通過企業(yè)管理器或存儲(chǔ)過程來查看鎖用EnterpriseManager查看鎖 在企業(yè)管理器中選擇目錄樹窗口中 管理 文件夾下 當(dāng)前活動(dòng) 中的鎖 進(jìn)程ID節(jié)點(diǎn) 則可以查看當(dāng)前鎖定的進(jìn)程 選擇同級(jí)的鎖 對(duì)象節(jié)點(diǎn)下的相應(yīng)字節(jié)點(diǎn) 則可以查看當(dāng)前鎖定的對(duì)象 若右鍵單擊任務(wù)板窗口中的對(duì)象 從快捷菜單中選擇屬性選項(xiàng) 則會(huì)出現(xiàn)鎖的進(jìn)程細(xì)節(jié)對(duì)話框 在此可以刷新或殺死鎖的進(jìn)程 殺死進(jìn)程還可以用如下Transact SQL命令來進(jìn)行KILLspidspid是SystemProcessID 即系統(tǒng)進(jìn)程編號(hào)的縮寫 21 用系統(tǒng)存儲(chǔ)過程Sp lock查看鎖存儲(chǔ)過程Sp lock的語法如下sp lockspidSQLServer的進(jìn)程編號(hào)spid可以在master dbo sysprocesses系統(tǒng)表中查到 spid是INT類型的數(shù)據(jù) 如果不指定spid則顯示所有的鎖 例 顯示當(dāng)前系統(tǒng)中所有的鎖usemasterexecsp lock例 usemasterexecsp lock52 22 死鎖及其防止 死鎖是在多用戶或多進(jìn)程狀況下 為使用同一資源而產(chǎn)生的無法解決的爭用狀態(tài) 通俗地講 就是兩個(gè)用戶各占用一個(gè)資源 兩人都想使用對(duì)方的資源但同時(shí)又不愿放棄自己的資源 就一直等待對(duì)方放棄資源 如果不進(jìn)行外部干涉就將一直耗下去 死鎖會(huì)造成資源的大量浪費(fèi) 甚至?xí)瓜到y(tǒng)崩潰 在SQLServer中解決死鎖的原則是犧牲一個(gè)比