管理事務課件

1、管理事務課件1第五章第五章 管理事務管理事務事務的概念事務的概念事務的特性事務的特性事務的類型事務的類型事務的工作機制事務的工作機制鎖鎖管理事務課件2事務的由來事務的由來使用使用DELETE 命令或命令或UPDATE 命令對數(shù)據(jù)庫進行更命令對數(shù)據(jù)庫進行更新時新時，一次只能操作一個表一次只能操作一個表，這會帶來數(shù)據(jù)庫的數(shù)據(jù)這會帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題不一致的問題。例如企業(yè)取消了后勤部例如企業(yè)取消了后勤部，需要將后勤需要將后勤部從部從department表中刪除表中刪除，要修改要修改department 表表，而而employee 表中的部門編號與后勤部相對應的員工也應表中的部門編號與后勤部相

2、對應的員工也應刪除刪除。因此因此，兩個表都需要修改兩個表都需要修改，這種修改只能通過這種修改只能通過兩條兩條DELETE 語句進行語句進行。假設后勤部編號為假設后勤部編號為1012第一條第一條DELETE 語句修改語句修改department 表表delete from departmentwhere dept_id = 1012第二條第二條DELETE 語句修改語句修改employee 表表delete from employee where dept_id = 1012管理事務課件3在執(zhí)行第一條在執(zhí)行第一條DELETE 語句后語句后，數(shù)據(jù)庫中的數(shù)據(jù)庫中的數(shù)據(jù)已處于不一致的狀態(tài)數(shù)據(jù)已處于不一

3、致的狀態(tài)。因為此時已經(jīng)沒有因為此時已經(jīng)沒有后勤部了后勤部了，但但employee 表中仍然保存著屬于后表中仍然保存著屬于后勤部的員工記錄勤部的員工記錄；只有執(zhí)行了第二條只有執(zhí)行了第二條DELETE 語句后語句后，數(shù)據(jù)才重新處于一致狀態(tài)數(shù)據(jù)才重新處于一致狀態(tài)。但是如果但是如果執(zhí)行完第一條語句后執(zhí)行完第一條語句后，計算機突然出現(xiàn)故障計算機突然出現(xiàn)故障，無法再繼續(xù)執(zhí)行第二條無法再繼續(xù)執(zhí)行第二條DELETE 語句語句，則數(shù)則數(shù)據(jù)庫中的數(shù)據(jù)將處于永遠不一致的狀態(tài)據(jù)庫中的數(shù)據(jù)將處于永遠不一致的狀態(tài)。因此因此必須保證這兩條必須保證這兩條DELETE 語句同時執(zhí)行語句同時執(zhí)行。為為解決類似的問題解決類似的問題

4、，數(shù)據(jù)庫系統(tǒng)通常都引入了事數(shù)據(jù)庫系統(tǒng)通常都引入了事務務Transaction 的概念的概念。管理事務課件4事務的概念及特性事務：指作為單個邏輯工作單元執(zhí)行的事務：指作為單個邏輯工作單元執(zhí)行的一系列操作，而這些邏輯工作單元需要一系列操作，而這些邏輯工作單元需要具有原子性，一致性，隔離性和持久性具有原子性，一致性，隔離性和持久性四個屬性，統(tǒng)稱四個屬性，統(tǒng)稱ACIDACID特性。特性。原子性：指事務必須是原子工作單元，原子性：指事務必須是原子工作單元，即對于事務所進行數(shù)據(jù)修改，要么全都即對于事務所進行數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行執(zhí)行，要么全都不執(zhí)行管理事務課件5一致性：指事務在完成時，必須

5、使所有一致性：指事務在完成時，必須使所有的數(shù)據(jù)都保持一致性狀態(tài)，而且在相關的數(shù)據(jù)都保持一致性狀態(tài)，而且在相關數(shù)據(jù)庫中，所有規(guī)則都必須應用于事務數(shù)據(jù)庫中，所有規(guī)則都必須應用于事務的修改，以保持所有數(shù)據(jù)的完整性。事的修改，以保持所有數(shù)據(jù)的完整性。事務結束時，所有的內(nèi)部數(shù)據(jù)結構都必須務結束時，所有的內(nèi)部數(shù)據(jù)結構都必須是正確的。是正確的。管理事務課件6隔離性：指由并發(fā)事務所做的修改必須與隔離性：指由并發(fā)事務所做的修改必須與任何其他并發(fā)事務所做的修改相隔離。事任何其他并發(fā)事務所做的修改相隔離。事務查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是被務查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是被另一并發(fā)事務修改之前的狀態(tài)，要么是被另

6、一并發(fā)事務修改之前的狀態(tài)，要么是被另一事務修改之后的狀態(tài)，即事務不會查另一事務修改之后的狀態(tài)，即事務不會查看正在由另一個并發(fā)事務正在修改的數(shù)據(jù)?？凑谟闪硪粋€并發(fā)事務正在修改的數(shù)據(jù)。持久性：指事務完成之后，它對于系統(tǒng)的持久性：指事務完成之后，它對于系統(tǒng)的影響是永久性的，即使出現(xiàn)系統(tǒng)故障也是影響是永久性的，即使出現(xiàn)系統(tǒng)故障也是如此。如此。管理事務課件7事務的類型事務的類型隱式事務隱式事務 ：每次執(zhí)行：每次執(zhí)行SQL Server的任何的任何數(shù)據(jù)修改語句時，它都是一個隱式事務。數(shù)據(jù)修改語句時，它都是一個隱式事務。例如：下列例如：下列SQL語句是一個獨立事務：語句是一個獨立事務：Insert tal

7、be values(1,abc)Update table set col1=5 where col1=1Delete from table1 where col1=5g go管理事務課件8顯式事務顯式事務通常在程序中用通常在程序中用BEGIN TRANSACTION BEGIN TRANSACTION 命令來標命令來標識一個事務的開始識一個事務的開始，用用COMMIT TRANSACTION COMMIT TRANSACTION 命命令標識事務結束令標識事務結束；這兩個命令之間的所有語句這兩個命令之間的所有語句被視為一體被視為一體。只有執(zhí)行到只有執(zhí)行到COMMIT TRANSACTION CO

8、MMIT TRANSACTION 命令時命令時，事務中對數(shù)據(jù)庫的更新操作才算確認事務中對數(shù)據(jù)庫的更新操作才算確認。和和BEGINEND BEGINEND 命令類似命令類似。這兩個命令也可以進這兩個命令也可以進行嵌套行嵌套，即事務可以嵌套執(zhí)行即事務可以嵌套執(zhí)行。管理事務課件9這兩個命令的語法如下這兩個命令的語法如下BEGIN TRANSACTION transaction_name | tran_name_variableCOMMIT TRANSACTION transaction_name | tran_name_variable 其中其中BEGIN TRANSACTION 可以縮寫為可以縮寫

9、為BEGIN TRAN 。COMMIT TRANSACTION可以縮寫為可以縮寫為COMMIT TRAN 或或COMMIT 。transaction_name：指定事務的名稱指定事務的名稱，只有前只有前32 個字符個字符會被系統(tǒng)識別會被系統(tǒng)識別。tran_name_variable：用變量來指定事務的名稱變量用變量來指定事務的名稱變量。只能聲明為只能聲明為CHAR VARCHAR NCHAR 或或NVARCHAR 類型類型管理事務課件10例例： 刪除后勤部刪除后勤部declare transaction_name varchar(32)select transaction_name = my_

10、transaction_delete begin transaction transaction_namego use samplegodelete from department where dept_id = 1012godelete from employee where dept_id = 1012gocommit transaction my_transaction_deletego管理事務課件11事務回滾事務回滾事務回滾事務回滾( (Transaction Rollback) 是指當事務中的是指當事務中的某一語句執(zhí)行失敗時某一語句執(zhí)行失敗時, ,將對數(shù)據(jù)庫的操作恢復到事將對數(shù)據(jù)庫的

11、操作恢復到事務執(zhí)行前或某個指定位置務執(zhí)行前或某個指定位置。事務回滾使用事務回滾使用ROLLBACK TRANSACTION 命令命令，其語法如下其語法如下ROLLBACK TRANSACTION transaction_name | tran_name_variable| savepoint_name | savepoint_variable 其中其中savepoint_name 和和savepoint_variable 參數(shù)參數(shù)用于指定回滾到某一指定位置用于指定回滾到某一指定位置。如果要讓事務回滾到指定位置如果要讓事務回滾到指定位置，則需要在事務中則需要在事務中設定保存點設定保存點Save

12、Point 。管理事務課件12所謂保存點是指定其所在位置之前的事務語句所謂保存點是指定其所在位置之前的事務語句不能回滾的語句不能回滾的語句，即此語句前面的操作被視為即此語句前面的操作被視為有效有效。其語法如下其語法如下：SAVE TRANSACTION savepoint_name | savepoint_variable各參數(shù)說明如下各參數(shù)說明如下：savepoint_name：指定保存點的名稱指定保存點的名稱。同事務同事務的名稱一樣的名稱一樣，只有前只有前32 個字符會被系統(tǒng)識別個字符會被系統(tǒng)識別。savepoint_variable：用變量來指定保存點的用變量來指定保存點的名稱變量名稱變

13、量，只能聲明為只能聲明為CHAR、VARCHAR、 NCHAR 或或NVARCHAR 類型類型。管理事務課件13例例： 刪除后勤部再將后勤部的職工劃歸到經(jīng)理室刪除后勤部再將后勤部的職工劃歸到經(jīng)理室begin transaction my_transaction_deleteuse samplegodelete from department where dept_id = 1012save transaction after_deleteupdate employee set dept_id = 1001 where dept_id = 1012if error!=0 or rowcount=

14、0 thenbegin rollback tran after_delete /* 回滾到保存點回滾到保存點after_delete 如果使用如果使用rollback my_transaction_delete 則會回滾到事務開始前則會回滾到事務開始前 */ Commit tran print 更新員工信息表時產(chǎn)生錯誤更新員工信息表時產(chǎn)生錯誤 Returnendcommit transaction my_transaction_deletego管理事務課件14事務工作機制事務工作機制例如：例如：begin tranInsert table1 values(1,abc)Update table1

15、 set col1=5 where col1=1Delete from table1 where col1=5Commit tran管理事務課件151、當當begin tran語句到達數(shù)據(jù)庫時，語句到達數(shù)據(jù)庫時，sql server分析出這是事務的開始，分析出這是事務的開始，SQL server找到下一個找到下一個可用的內(nèi)存日志頁面，并給新事務分配一個事務可用的內(nèi)存日志頁面，并給新事務分配一個事務ID。2、接著運行插入語句，新的行被記錄到事務日志接著運行插入語句，新的行被記錄到事務日志中，數(shù)據(jù)頁面在內(nèi)存中進行修改，若所需頁面不中，數(shù)據(jù)頁面在內(nèi)存中進行修改，若所需頁面不在內(nèi)存中，則從磁盤調(diào)出。在

16、內(nèi)存中，則從磁盤調(diào)出。3 3、update語句以類似方式運行。語句以類似方式運行。4 4、當、當sql server 收到收到commint tran時，日志頁時，日志頁面被寫道數(shù)據(jù)庫的日志設備上，這樣才能保證日面被寫道數(shù)據(jù)庫的日志設備上，這樣才能保證日志頁面可被恢復。由于日志變化寫入了硬盤，它志頁面可被恢復。由于日志變化寫入了硬盤，它保證事務是可恢復的，即使掉電了或在數(shù)據(jù)頁寫保證事務是可恢復的，即使掉電了或在數(shù)據(jù)頁寫入磁盤時數(shù)據(jù)庫崩潰了，也能進行事務恢復。入磁盤時數(shù)據(jù)庫崩潰了，也能進行事務恢復。管理事務課件16檢查點處理檢查點處理問題：系統(tǒng)何時將數(shù)據(jù)頁寫入磁盤呢？問題：系統(tǒng)何時將數(shù)據(jù)頁寫入磁

17、盤呢？日志頁面是在日志頁面是在commit tran時寫入磁盤時寫入磁盤的，那么何時將數(shù)據(jù)頁面寫入磁盤呢？的，那么何時將數(shù)據(jù)頁面寫入磁盤呢？在處理檢查點時。在處理檢查點時。檢查點的概念：是檢查點的概念：是sql server將數(shù)據(jù)頁面將數(shù)據(jù)頁面從內(nèi)存拷貝到磁盤時的內(nèi)部處理點。從內(nèi)存拷貝到磁盤時的內(nèi)部處理點。檢查點幫助確?；謴鸵烟峤皇聞斩粫z查點幫助確保恢復已提交事務而不會花費額外的時間。當檢查點出現(xiàn)時，將花費額外的時間。當檢查點出現(xiàn)時，將寫入一個日志項以指示所有修改了的頁寫入一個日志項以指示所有修改了的頁面已寫入磁盤。面已寫入磁盤。管理事務課件17鎖鎖- -鎖的概念鎖的概念鎖鎖（Lock)

18、是在多用戶環(huán)境下對資源訪問的一是在多用戶環(huán)境下對資源訪問的一種限制機制種限制機制。當對一個數(shù)據(jù)源加鎖后當對一個數(shù)據(jù)源加鎖后，此數(shù)據(jù)此數(shù)據(jù)源就有了一定的訪問限制源就有了一定的訪問限制，我們就稱對此數(shù)據(jù)我們就稱對此數(shù)據(jù)源進行了鎖定源進行了鎖定。在在SQL Server中可以對以下的中可以對以下的對象進行鎖定對象進行鎖定: :數(shù)據(jù)行數(shù)據(jù)行：數(shù)據(jù)頁中的單行數(shù)據(jù)數(shù)據(jù)頁中的單行數(shù)據(jù)索引行索引行：索引頁中的單行數(shù)據(jù)即索引的鍵值索引頁中的單行數(shù)據(jù)即索引的鍵值頁頁：頁是頁是SQL Server 存取數(shù)據(jù)的基本單位其大存取數(shù)據(jù)的基本單位其大小為小為8KB盤區(qū)盤區(qū)：一個盤區(qū)由一個盤區(qū)由8 個連續(xù)的頁組成個連續(xù)的頁組

19、成表表數(shù)據(jù)庫數(shù)據(jù)庫管理事務課件18鎖的類別鎖的類別在在SQL Server 中中， 從數(shù)據(jù)庫系統(tǒng)的角度從數(shù)據(jù)庫系統(tǒng)的角度來看來看，鎖分為以下三種類型鎖分為以下三種類型：獨占鎖獨占鎖Exclusive Lock：獨占鎖鎖定的資源獨占鎖鎖定的資源只允許進行鎖定操作的程序使用只允許進行鎖定操作的程序使用，其它任其它任何對它的操作均不會被接受何對它的操作均不會被接受。執(zhí)行數(shù)據(jù)更執(zhí)行數(shù)據(jù)更新命令新命令，即即INSERT、UPDATE 或或DELETE 命令時命令時SQL Server 會自動使用會自動使用獨占鎖獨占鎖。但當對象上有其它鎖存在時但當對象上有其它鎖存在時，無無法對其加獨占鎖法對其加獨占鎖，獨

20、占鎖一直到事務結束獨占鎖一直到事務結束才能被釋放才能被釋放。管理事務課件19共享鎖共享鎖Shared Lock：共享鎖鎖定的資源可以共享鎖鎖定的資源可以被其它用戶讀取被其它用戶讀取，但其它用戶不能修改它但其它用戶不能修改它。在在SELECT 命令執(zhí)行時命令執(zhí)行時，SQL Server 通常會對通常會對對象進行共享鎖鎖定對象進行共享鎖鎖定，通常加共享鎖的數(shù)據(jù)頁通常加共享鎖的數(shù)據(jù)頁被讀取完畢后被讀取完畢后，共享鎖就會立即被釋放共享鎖就會立即被釋放更新鎖更新鎖Update Lock：更新鎖是為了防止死鎖更新鎖是為了防止死鎖而設立的而設立的。當當SQL Server 準備更新數(shù)據(jù)時準備更新數(shù)據(jù)時，它它

21、首先對數(shù)據(jù)對象作更新鎖鎖定首先對數(shù)據(jù)對象作更新鎖鎖定，這樣數(shù)據(jù)將不這樣數(shù)據(jù)將不能被修改能被修改，但可以讀取但可以讀取。等到等到SQL Server 確定確定要進行更新數(shù)據(jù)操作時要進行更新數(shù)據(jù)操作時，它會自動將更新鎖換它會自動將更新鎖換為獨占鎖為獨占鎖。但當對象上有其它鎖存在時但當對象上有其它鎖存在時，無法無法對其作更新鎖鎖定對其作更新鎖鎖定。管理事務課件20查看鎖查看鎖可以通過企業(yè)管理器或存儲過程來查看鎖可以通過企業(yè)管理器或存儲過程來查看鎖用用Enterprise Manager 查看鎖查看鎖：在企業(yè)管理器中在企業(yè)管理器中選擇目錄樹窗口中選擇目錄樹窗口中“管理管理” 文件夾下文件夾下 “當前活

22、動當前活動” 中中的的鎖鎖 /進程進程 ID 節(jié)點節(jié)點，則可以查看當前鎖定的進程則可以查看當前鎖定的進程。選擇選擇同級的同級的鎖鎖 /對象對象節(jié)點下的相應字節(jié)點節(jié)點下的相應字節(jié)點，則可以查看當前鎖則可以查看當前鎖定的對象定的對象。若右鍵單擊任務板窗口中的對象若右鍵單擊任務板窗口中的對象，從快捷菜從快捷菜單中選擇屬性選項單中選擇屬性選項，則會出現(xiàn)鎖的進程細節(jié)對話框則會出現(xiàn)鎖的進程細節(jié)對話框，在在此可以刷新或殺死鎖的進程此可以刷新或殺死鎖的進程。殺死進程還可以用如下殺死進程還可以用如下Transact-SQL 命令來進行命令來進行KILL spidspid 是是System Process ID，

23、 即系統(tǒng)進程編號的縮寫即系統(tǒng)進程編號的縮寫。管理事務課件21用系統(tǒng)存儲過程用系統(tǒng)存儲過程Sp_lock 查看鎖查看鎖存儲過程存儲過程Sp_lock 的語法如下的語法如下sp_lock spid SQL Server 的進程編號的進程編號spid 可以在可以在master.dbo.sysprocesses 系統(tǒng)表中查到系統(tǒng)表中查到，spid 是是INT類型類型的數(shù)據(jù)的數(shù)據(jù)，如果不指定如果不指定spid 則顯示所有的鎖則顯示所有的鎖。例例： 顯示當前系統(tǒng)中所有的鎖顯示當前系統(tǒng)中所有的鎖use masterexec sp_lock例：例：use masterexec sp_lock 52管理事務課件22死鎖及其防