數(shù)據(jù)庫系統(tǒng)原理與實踐 課件 第5章 數(shù)據(jù)庫管理

5.1數(shù)據(jù)庫管理概述理解數(shù)據(jù)庫管理目標(biāo)了解數(shù)據(jù)庫管理內(nèi)容了解數(shù)據(jù)庫管理工具理解數(shù)據(jù)庫DBMS軟件結(jié)構(gòu)【本節(jié)學(xué)習(xí)目標(biāo)】一、什么是數(shù)據(jù)庫管理數(shù)據(jù)庫管理(DatabaseManagement)是指為保證數(shù)據(jù)庫系統(tǒng)的正常運行和服務(wù)質(zhì)量必須進(jìn)行的系統(tǒng)管理工作。二、為什么需要數(shù)據(jù)庫管理數(shù)據(jù)庫系統(tǒng)隨規(guī)模增大,系統(tǒng)會變得異常復(fù)雜多用戶數(shù)據(jù)庫應(yīng)用帶來數(shù)據(jù)庫訪問復(fù)雜性數(shù)據(jù)安全和數(shù)據(jù)隱私對機(jī)構(gòu)和用戶都非常重要數(shù)據(jù)庫系統(tǒng)隨數(shù)據(jù)量增加和使用時間增長其性能會降低系統(tǒng)遭遇意外事件，數(shù)據(jù)庫損壞或數(shù)據(jù)丟失三、數(shù)據(jù)庫管理目標(biāo)保障數(shù)據(jù)庫系統(tǒng)正常穩(wěn)定運行充分發(fā)揮數(shù)據(jù)庫系統(tǒng)的軟硬件處理能力確保數(shù)據(jù)庫系統(tǒng)安全和用戶數(shù)據(jù)隱私性有效管理數(shù)據(jù)庫用戶及其角色權(quán)限解決數(shù)據(jù)庫系統(tǒng)性能優(yōu)化、系統(tǒng)故障與數(shù)據(jù)損壞等問題最大程度地發(fā)揮數(shù)據(jù)庫對其所屬機(jī)構(gòu)的作用四、數(shù)據(jù)庫管理內(nèi)容DBMS系統(tǒng)運行管理數(shù)據(jù)庫性能監(jiān)控數(shù)據(jù)庫索引管理數(shù)據(jù)庫查詢優(yōu)化數(shù)據(jù)庫事務(wù)并發(fā)控制數(shù)據(jù)庫角色管理數(shù)據(jù)庫用戶管理數(shù)據(jù)庫對象權(quán)限管理數(shù)據(jù)安全管理數(shù)據(jù)庫備份數(shù)據(jù)庫恢復(fù)。。。五、數(shù)據(jù)庫管理工具OracleSQLDeveloper——管理Oracle數(shù)據(jù)庫SQLServerManagementStudio——管理SQLServer數(shù)據(jù)庫pgAdmin——管理PostgreSQL數(shù)據(jù)庫MySQLWorkbench——管理MySQL數(shù)據(jù)庫。。。任何數(shù)據(jù)庫DBMS服務(wù)器系統(tǒng)，都必須有相應(yīng)的數(shù)據(jù)庫管理工具，以便用戶使用它們對數(shù)據(jù)庫服務(wù)器及其數(shù)據(jù)庫進(jìn)行管理控制。六、DBMS軟件系統(tǒng)層次結(jié)構(gòu)本節(jié)學(xué)習(xí)結(jié)束！5.2-1存儲管理了解數(shù)據(jù)庫存儲結(jié)構(gòu)理解數(shù)據(jù)庫數(shù)據(jù)文件的記錄組織方式【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫邏輯存儲結(jié)構(gòu)二、數(shù)據(jù)庫物理存儲結(jié)構(gòu)三、數(shù)據(jù)庫邏輯存儲結(jié)構(gòu)與物理存儲結(jié)構(gòu)的關(guān)系四、數(shù)據(jù)庫文件的數(shù)據(jù)記錄結(jié)構(gòu)定長數(shù)據(jù)記錄格式定長數(shù)據(jù)記錄在文件塊中的存儲格式1.定長記錄變長數(shù)據(jù)記錄格式變長數(shù)據(jù)記錄在文件塊中的存儲格式2.變長記錄五、數(shù)據(jù)庫文件的數(shù)據(jù)記錄組織堆文件中數(shù)據(jù)記錄組織順序文件中數(shù)據(jù)記錄組織多表聚簇文件中數(shù)據(jù)記錄組織HASH文件中數(shù)據(jù)記錄組織本節(jié)學(xué)習(xí)結(jié)束！5.2-2存儲管理了解數(shù)據(jù)庫元數(shù)據(jù)存儲結(jié)構(gòu)理解數(shù)據(jù)庫存儲引擎【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫元數(shù)據(jù)與數(shù)據(jù)字典數(shù)據(jù)庫元數(shù)據(jù)是指數(shù)據(jù)庫系統(tǒng)中定義數(shù)據(jù)庫及其對象的結(jié)構(gòu)數(shù)據(jù)，以及數(shù)據(jù)庫的運行管理數(shù)據(jù)。在數(shù)據(jù)庫系統(tǒng)中，數(shù)據(jù)庫元數(shù)據(jù)存放的系統(tǒng)表及其視圖稱為數(shù)據(jù)字典（datadictionary）,也稱為系統(tǒng)目錄（systemcatalog）二、PostgreSQL數(shù)據(jù)字典的系統(tǒng)表pg_tablespace屬性表pg_database屬性表三、PostgreSQL數(shù)據(jù)字典的系統(tǒng)視圖pg_indexes索引視圖pg_table表視圖四、數(shù)據(jù)庫存儲引擎結(jié)構(gòu)外存管理器——負(fù)責(zé)管理數(shù)據(jù)庫磁盤存儲空間的分配、數(shù)據(jù)文件的存儲管理。內(nèi)存緩沖區(qū)——用于存儲當(dāng)前系統(tǒng)訪問數(shù)據(jù)庫文件的磁盤塊數(shù)據(jù)副本。緩沖區(qū)管理器——負(fù)責(zé)將數(shù)據(jù)從磁盤數(shù)據(jù)文件讀取到內(nèi)存數(shù)據(jù)緩沖區(qū)，并將修改后的數(shù)據(jù)塊寫回磁盤數(shù)據(jù)文件。此外，緩沖區(qū)管理器還負(fù)責(zé)內(nèi)存緩沖區(qū)空間分配、緩沖區(qū)查找、存儲塊替換、共享訪問鎖管理等。索引管理器——對數(shù)據(jù)庫的索引對象進(jìn)行索引創(chuàng)建、索引檢索、索引定位、索引維護(hù)管理。本節(jié)學(xué)習(xí)結(jié)束！5.3索引管理了解數(shù)據(jù)庫索引結(jié)構(gòu)理解數(shù)據(jù)庫B+樹索引原理理解數(shù)據(jù)庫散列索引原理【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫索引機(jī)制原理二、B+樹索引結(jié)構(gòu)B+樹是一種廣泛用于數(shù)據(jù)庫索引的平衡多叉樹索引結(jié)構(gòu)。圖5-14B+樹索引結(jié)構(gòu)示例從根結(jié)點到葉子結(jié)點的每條路徑長度相同每個非葉結(jié)點不保存數(shù)據(jù)記錄，它只存儲搜索鍵值及下層結(jié)點指針葉子結(jié)點不僅包含搜索鍵字，還包括鍵值對應(yīng)的數(shù)據(jù)記錄指針和結(jié)點鏈表指針。三、B+樹索引特點四、B+樹索引操作在圖5-14B+樹中，查詢搜索鍵值等于41的數(shù)據(jù)記錄在圖5-14的B+樹中，插入鍵值為33的數(shù)據(jù)記錄在圖5-14的B+樹中，插入鍵值為33的數(shù)據(jù)記錄在圖5-14所示B+樹中，刪除鍵值為30的數(shù)據(jù)記錄在圖5-14所示B+樹中，刪除鍵值為30的數(shù)據(jù)記錄四、散列索引散列索引是一種根據(jù)鍵值在散列表中獲取數(shù)據(jù)記錄指針的數(shù)據(jù)結(jié)構(gòu)。散列函數(shù)計算得到的散列值應(yīng)是一個非負(fù)整數(shù)，并且應(yīng)在散列表偏移值范圍內(nèi)。如果輸入到散列函數(shù)的2個鍵值相等（如key1=key2），那么輸出的散列值也應(yīng)相等，即hash(key1)=hash(key2)。如果輸入到散列函數(shù)的2個鍵值不相等（如key1≠key2），那么輸出的散列值盡量做到不相等，即hash(key1)≠hash(key2)。五、散列函數(shù)選擇要求直接尋址法數(shù)字分析法平方取中法取隨機(jī)數(shù)法除留取余法六、典型的散列函數(shù)方法開放尋址法再哈希法公共溢出區(qū)鏈地址法七、散列函數(shù)值沖突解決方法例

有一組鍵值key為{12,17,22,8,19,31,41,26,21}，其散列函數(shù)為F(key)=keyMOD7，使用鏈地址法所構(gòu)建的散列表如圖5-18所示。圖5-18鏈表地址法的散列表索引八、散列表操作例

在圖5-18散列表索引的關(guān)系表中，當(dāng)插入鍵值key=37的數(shù)據(jù)記錄時，其插入記錄鍵值的散列值F（37）=2。該值對應(yīng)的桶單元為空，則將新記錄的索引數(shù)據(jù)（37，記錄指針，∧）放入一個新鏈表結(jié)點中。插入該記錄后散列表見圖5-19所示。圖5-19插入記錄后的散列表例

在使用圖5-19散列表索引的關(guān)系表中，當(dāng)刪除鍵值key=22的數(shù)據(jù)記錄時，同步計算該刪除記錄的鍵值散列值F（22）=1。在散列表偏移值為1的桶單元中獲取鏈表指針，通過該指針找到含有鍵值22的結(jié)點，刪除該結(jié)點，并該結(jié)點的后繼結(jié)點指針放入該桶單元。刪除該記錄后，散列表索引結(jié)構(gòu)如圖5-20所示。圖5-20刪除記錄后的散列表本節(jié)學(xué)習(xí)結(jié)束！5.4事務(wù)管理了解在特定數(shù)據(jù)庫應(yīng)用處理中為什么需要事務(wù)機(jī)制理解數(shù)據(jù)庫事務(wù)的ACID特性理解在數(shù)據(jù)庫應(yīng)用中為什么需要事務(wù)程序并發(fā)運行掌握SQL事務(wù)程序編寫方法【本節(jié)學(xué)習(xí)目標(biāo)】一、為什么需要事務(wù)管理在數(shù)據(jù)庫應(yīng)用系統(tǒng)中，完成一個業(yè)務(wù)處理通常需要多個操作步驟才能完成處理。在每個操作步驟中，都可能遭遇失敗，若沒有一個處理機(jī)制，就可能造成操作數(shù)據(jù)混亂，從而破壞數(shù)據(jù)一致性。二、事務(wù)概念在數(shù)據(jù)庫中，事務(wù)（Transaction）是指由構(gòu)成單個業(yè)務(wù)處理單元的一組數(shù)據(jù)庫訪問操作，要求它們要么都成功執(zhí)行，要么都不執(zhí)行。例客戶在銀行系統(tǒng)中，從賬戶A轉(zhuǎn)賬1000元到賬戶B，其業(yè)務(wù)功能的操作程序如下：Read(A);A:=A-1000;Write(A);Read(B);B:=B+1000;Write(B)轉(zhuǎn)賬業(yè)務(wù)操作程序業(yè)務(wù)要求：轉(zhuǎn)賬業(yè)務(wù)處理程序（轉(zhuǎn)賬事務(wù)）的操作語句要么所有都正常執(zhí)行，要么所有操作語句都不執(zhí)行，以確保賬戶資金數(shù)據(jù)的正確狀態(tài)。在數(shù)據(jù)庫系統(tǒng)中，事務(wù)是DBMS執(zhí)行的最小任務(wù)單元。同時，事務(wù)也是DBMS最小的故障恢復(fù)任務(wù)單元和并發(fā)控制任務(wù)單元。其生命周期狀態(tài)變遷如圖所示。三、事務(wù)狀態(tài)四、事務(wù)特性事務(wù)ACID特性：原子性（Atomicity）：事務(wù)所有操作在數(shù)據(jù)庫中要么全部執(zhí)行，要么全部不執(zhí)行。一致性（Consistency）：事務(wù)多次執(zhí)行，其結(jié)果應(yīng)一致。隔離性（Isolation）：事務(wù)與事務(wù)之間隔離，并發(fā)執(zhí)行透明。持續(xù)性（Durability）：事務(wù)完成后，數(shù)據(jù)改變必須是永久的。為了確保關(guān)系數(shù)據(jù)庫共享訪問的數(shù)據(jù)正確性，要求DBMS的事務(wù)管理機(jī)制維護(hù)事務(wù)的ACID特性。五、事務(wù)并發(fā)執(zhí)行為什么事務(wù)需并發(fā)執(zhí)行？改善系統(tǒng)的資源利用率減少事務(wù)程序運行的平均等待時間事務(wù)并發(fā)執(zhí)行是指多個事務(wù)程序在數(shù)據(jù)庫系統(tǒng)中同一時段運行。六、事務(wù)SQL程序在關(guān)系數(shù)據(jù)庫系統(tǒng)中，可以利用SQL語言提供的相應(yīng)語句編寫事務(wù)程序。BEGIN或STARTTRANSACTION ；事務(wù)開始語句ROLLBACK ；事務(wù)回滾語句COMMIT ；事務(wù)提交語句 SAVEPOINT ；事務(wù)保存點語句1.事務(wù)SQL語句2.事務(wù)SQL程序基本框架BEGIN；SQL語句1；SQL語句2；…SQL語句n；COMMIT;BEGIN；SQL語句1；SQL語句2；…SQL語句n；ROLLBACK;BEGIN；SQL語句1；SQL語句2；…SAVEPOINT

保存點名；…SQL語句n；ROLLBACK

保存點名;例

在選課管理數(shù)據(jù)庫CurriculaDB中，使用事務(wù)程序?qū)崿F(xiàn)對學(xué)院信息表College的數(shù)據(jù)插入，其事務(wù)SQL程序如下：STARTTRANSACTION;INSERTINTOcollege(collegeID,collegename)VALUES('004','外語學(xué)院');INSERTINTOcollege(collegeID,collegename)VALUES('005','數(shù)學(xué)學(xué)院');INSERTINTOcollege(collegeID,collegename)VALUES('006','臨床醫(yī)學(xué)院');COMMIT;運行按鈕事務(wù)程序結(jié)果消息3.事務(wù)SQL程序執(zhí)行創(chuàng)建數(shù)據(jù)庫CREATEDATABASE修改數(shù)據(jù)庫ALTERDATABASE刪除數(shù)據(jù)庫DROPDATABASE恢復(fù)數(shù)據(jù)庫RESTOREDATABASE加載數(shù)據(jù)庫LOADDATABASE備份日志文件BACKUPLOG恢復(fù)日志文件RESTORELOG授權(quán)操作GRANT。。。4.事務(wù)程序中不能使用的SQL語句若用戶沒有顯式地定義事務(wù)時，DBMS按默認(rèn)事務(wù)方式處理，即每執(zhí)行一個SQL語句將自動構(gòu)成一個事務(wù)。若將多條SQL語句定義為一個事務(wù)時，才使用專門的事務(wù)SQL語句顯式地定義事務(wù)。七、DBMS默認(rèn)事務(wù)方式本節(jié)學(xué)習(xí)結(jié)束！5.5-1并發(fā)控制——事務(wù)調(diào)度了解數(shù)據(jù)庫并發(fā)事務(wù)控制目的了解并發(fā)事務(wù)調(diào)度控制需要解決的問題理解并發(fā)事務(wù)調(diào)度原理與策略【本節(jié)學(xué)習(xí)目標(biāo)】一、為什么需要并發(fā)控制當(dāng)多個事務(wù)程序在DBMS系統(tǒng)中同時運行時，可能會出現(xiàn)對一些共享數(shù)據(jù)同時進(jìn)行訪問操作，如一些事務(wù)修改數(shù)據(jù)，另一些事務(wù)讀取數(shù)據(jù)。這些并發(fā)的共享數(shù)據(jù)操作，如果在DBMS中沒有一定的約束控制情況下，可能會帶來數(shù)據(jù)不一致性或事務(wù)程序死鎖問題。因此，在多個事務(wù)并發(fā)運行時，必須進(jìn)行并發(fā)控制處理。二、并發(fā)控制需解決的問題A=16讀讀A=A-1A=A-1A=15A=15A=16A=16出售1出售1事務(wù)T1事務(wù)T2執(zhí)行結(jié)果分析：剩余機(jī)票數(shù)A值為15，該數(shù)據(jù)有錯，應(yīng)為14。1.丟失更新數(shù)據(jù)A=15售票點2機(jī)票數(shù)量A售票點1錯誤分析：T1、T2兩個事務(wù)并發(fā)執(zhí)行，它們均對數(shù)據(jù)庫共享數(shù)據(jù)A進(jìn)行了非鎖定資源的讀寫操作。當(dāng)事務(wù)T1和T2均讀入該共享數(shù)據(jù)A并修改，T2提交的結(jié)果破壞了T1提交的結(jié)果，導(dǎo)致T1的修改被丟失。2.不可重復(fù)讀取問題分析:

為什么出現(xiàn)不可重復(fù)讀取？事務(wù)T1讀取某一數(shù)據(jù)后，事務(wù)T2對其做了修改，當(dāng)事務(wù)T1再次讀該數(shù)據(jù)時，得到與前一次不同的值。不可重復(fù)讀取是指一個事務(wù)對一個共享數(shù)據(jù)重復(fù)多次讀取，但前后讀取的數(shù)據(jù)不一致。假如A的初始數(shù)據(jù)為100事務(wù)T1首次讀取A的值為100事務(wù)T1不知道其它事務(wù)修改A值事務(wù)T1第2次讀取A的值為200因此，事務(wù)T1前后讀取數(shù)據(jù)A的值不一致。(1)事務(wù)T1按一定條件從數(shù)據(jù)庫中讀取了某些數(shù)據(jù)記錄后，事務(wù)T2刪除了其中部分記錄，當(dāng)T1再次按相同條件讀取數(shù)據(jù)時，發(fā)現(xiàn)某些記錄消失了。也稱為不可重復(fù)讀取。(2)事務(wù)T1按一定條件從數(shù)據(jù)庫中讀取某些數(shù)據(jù)記錄后，事務(wù)T2在其中插入了一些記錄，當(dāng)T1再次按相同條件讀取數(shù)據(jù)時，發(fā)現(xiàn)多了一些記錄。稱為幻象讀取。同類問題:3.臟數(shù)據(jù)讀取問題分析：事務(wù)T2并不知道C值被T1恢復(fù)，因此，事務(wù)T2讀取了臟數(shù)據(jù)。最終結(jié)果：C恢復(fù)為100T2讀取C的中間結(jié)果為200臟數(shù)據(jù)讀取是指一個事務(wù)讀取了被取消持久化的共享數(shù)據(jù)。C初始值為100三、并發(fā)事務(wù)調(diào)度原理與策略并發(fā)事務(wù)調(diào)度就是控制多個事務(wù)的數(shù)據(jù)操作語句按照恰當(dāng)?shù)捻樞蛟L問共享數(shù)據(jù)，使這些事務(wù)執(zhí)行之后，避免造成數(shù)據(jù)的不一致性，即解決“丟失更新數(shù)據(jù)”、“不可重復(fù)讀”、“臟數(shù)據(jù)讀”等問題。1.事務(wù)調(diào)度原理在DBMS中，事務(wù)管理器將并發(fā)執(zhí)行事務(wù)的SQL數(shù)據(jù)操作請求提交給并發(fā)控制調(diào)度器。由并發(fā)控制調(diào)度器將各個事務(wù)的SQL數(shù)據(jù)操作請求按照一定順序進(jìn)行調(diào)度執(zhí)行，并完成對數(shù)據(jù)庫緩沖區(qū)的讀寫操作。例

銀行客戶A的賬戶當(dāng)前余款為1000元，客戶B的賬戶當(dāng)前余款為1500元?，F(xiàn)在有兩個事務(wù)T1和T2，其中T1事務(wù)將從客戶A轉(zhuǎn)賬200元到客戶B，T2事務(wù)也將從客戶A轉(zhuǎn)賬400元到客戶B。2.事務(wù)調(diào)度策略賬戶A余款=400元賬戶B余款=2100元賬戶A余款=400元賬戶B余款=2100元賬戶A余款=400元賬戶B余款=2100元賬戶A余款=800元賬戶B余款=1900元結(jié)論：在事務(wù)并發(fā)執(zhí)行中，只有當(dāng)事務(wù)中數(shù)據(jù)操作調(diào)度順序的執(zhí)行結(jié)果與事務(wù)任務(wù)串行執(zhí)行結(jié)果一樣時，該并發(fā)事務(wù)調(diào)度才能保證數(shù)據(jù)操作的正確性和一致性。符合這樣效果的調(diào)度稱為可串行化調(diào)度。DBMS并發(fā)事務(wù)調(diào)度目標(biāo)：使并發(fā)事務(wù)調(diào)度實現(xiàn)的處理結(jié)果與串行化調(diào)度處理結(jié)果一致。本節(jié)學(xué)習(xí)結(jié)束！5.5-2并發(fā)控制——鎖機(jī)制、控制協(xié)議、死鎖、事務(wù)隔離了解數(shù)據(jù)庫鎖機(jī)制了解并發(fā)控制協(xié)議理解兩階段鎖協(xié)議如何解決可串行調(diào)度問題了解并發(fā)事務(wù)在運行過程中出現(xiàn)死鎖條件掌握在DBMS中如何設(shè)置事務(wù)隔離級別【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫鎖機(jī)制為了解決多個事務(wù)并發(fā)對共享數(shù)據(jù)進(jìn)行新增、更新、刪除、查詢帶來的數(shù)據(jù)不一致性問題時，需要對共享數(shù)據(jù)進(jìn)行加鎖訪問?；阪i表的數(shù)據(jù)庫共享資源訪問排它鎖定(Lock-X)——鎖定后，不允許其它事務(wù)對共享數(shù)據(jù)再加鎖共享鎖定(Lock-S)——鎖定后，只允許其它事務(wù)對共享數(shù)據(jù)添加讀取鎖1.資源鎖定訪問在DBMS中，通過加入鎖表機(jī)制，來實現(xiàn)共享數(shù)據(jù)鎖定訪問，其加鎖方式包含如下類型。2.資源鎖定粒度數(shù)據(jù)庫——粒度最大表——粒度較大頁面——粒度中等行——粒度小3.資源鎖定實施方式隱式鎖定——DBMS缺省執(zhí)行顯式鎖定——加鎖命令顯式執(zhí)行BEGINWORK;LOCKTABLEfilmsINSHAREROWEXCLUSIVEMODE;DELETEFROMfilms_user_commentsWHEREidIN(SELECTidFROMfilmsWHERErating<5);DELETEFROMfilmsWHERErating<5;COMMITWORK;例PostgreSQL在films表上施加一個SHAREROWEXCLUSIVE鎖二、基于鎖機(jī)制的并發(fā)控制協(xié)議為了實現(xiàn)并發(fā)事務(wù)對共享數(shù)據(jù)訪問的串行化調(diào)度執(zhí)行，還必須約束它們對共享數(shù)據(jù)的操作訪問必須是以互斥方式進(jìn)行。這就需要用到基于數(shù)據(jù)庫鎖機(jī)制的并發(fā)控制協(xié)議。1.鎖操作的相容性排它鎖共享鎖無鎖排它鎖否否是共享鎖否是是無鎖是是是2.加鎖協(xié)議一級加鎖協(xié)議：任何事務(wù)在修改共享數(shù)據(jù)對象之前，必須對該數(shù)據(jù)執(zhí)行排它鎖定指令，直到該事務(wù)處理完成，才進(jìn)行解鎖指令執(zhí)行。例

某航班剩余機(jī)票數(shù)據(jù)A的當(dāng)前值為100張?，F(xiàn)有分別來自不同售票點的兩個并發(fā)事務(wù)T1和T2，其中T1事務(wù)將售出1張機(jī)票，T2事務(wù)將售出2張機(jī)票。以下分別給出它們在不加鎖和按一級加鎖協(xié)議的并發(fā)事務(wù)調(diào)度執(zhí)行情況，見下圖所示。執(zhí)行結(jié)果A=99，結(jié)果有錯執(zhí)行結(jié)果A=97，結(jié)果正確二級加鎖協(xié)議：在一級加鎖協(xié)議基礎(chǔ)上，當(dāng)并發(fā)事務(wù)對共享數(shù)據(jù)進(jìn)行讀操作，必須對該數(shù)據(jù)執(zhí)行共享鎖定指令，讀完數(shù)據(jù)后即刻釋放共享鎖定。例

某航班剩余機(jī)票數(shù)據(jù)A的當(dāng)前值為100張?，F(xiàn)有分別來自不同售票點的兩個并發(fā)事務(wù)T1和T2，其中T1事務(wù)將售出1張機(jī)票，T2事務(wù)進(jìn)行機(jī)票空余數(shù)查詢。以下分別給出它們在按一級加鎖協(xié)議執(zhí)行和按二級加鎖協(xié)議的事務(wù)調(diào)度執(zhí)行情況，見下圖所示。一級加鎖協(xié)議特點：使用一級加鎖協(xié)議，可避免出現(xiàn)更新丟失問題。但不能解決“不可重復(fù)讀取”、“臟讀”等數(shù)據(jù)不一致問題。T2事務(wù)讀取了臟數(shù)據(jù)A=99T2事務(wù)讀取正確數(shù)據(jù)A=100三級加鎖協(xié)議：在一級加鎖協(xié)議基礎(chǔ)上，當(dāng)并發(fā)事務(wù)對共享數(shù)據(jù)進(jìn)行讀操作，必須對該數(shù)據(jù)執(zhí)行共享鎖定指令，直到該事務(wù)處理結(jié)束才釋放共享鎖定。例

某航班剩余機(jī)票數(shù)據(jù)A的當(dāng)前值為100張。現(xiàn)有分別來自不同售票點的兩個并發(fā)事務(wù)T1和T2，其中T1事務(wù)將售出1張機(jī)票，T2事務(wù)進(jìn)行機(jī)票空余數(shù)查詢。以下分別給出它們在按二級加鎖協(xié)議執(zhí)行和按三級加鎖協(xié)議的并發(fā)事務(wù)調(diào)度執(zhí)行情況，見下圖所示。二級加鎖協(xié)議特點：該加鎖協(xié)議不但可以防止“丟失更新”的數(shù)據(jù)不一致性問題，還可防止出現(xiàn)臟讀數(shù)據(jù)問題。但有可能會出現(xiàn)“不可重復(fù)讀取”的數(shù)據(jù)不一致問題。T2事務(wù)首次讀取A=100T2事務(wù)再次讀取A=99T2事務(wù)首次讀取A=100T2事務(wù)再次讀取A=100三級加鎖協(xié)議特點：該加鎖協(xié)議不但可以防止“丟失更新”、“臟讀”的數(shù)據(jù)不一致性問題，還可防止出現(xiàn)“不可重復(fù)讀取”的數(shù)據(jù)一致性問題。加鎖協(xié)議級別排它鎖共享鎖無丟失更新無臟讀可重復(fù)讀一級全程加鎖不加是否否二級全程加鎖開始時加鎖，讀完數(shù)據(jù)釋放鎖定是是否三級全程加鎖全程加鎖是是是3.不同級別鎖協(xié)議比較二階段鎖定協(xié)議規(guī)定每個事務(wù)必須分兩個階段提出加鎖和解鎖申請:增長階段，事務(wù)只能獲得鎖，但不能釋放鎖?？s減階段，事務(wù)只能釋放鎖，但不能獲得新鎖。事務(wù)T增長階段縮減階段三、兩階段鎖定協(xié)議保證并發(fā)事務(wù)調(diào)度可串行化的一個協(xié)議是：二階段鎖定協(xié)議問題探討：判斷下列事務(wù)是否可串行化調(diào)度？事務(wù)T1事務(wù)T2加鎖解鎖加鎖解鎖加鎖加鎖解鎖解鎖結(jié)論:若所有并發(fā)事務(wù)執(zhí)行都遵從兩階段鎖定協(xié)議，則這些事務(wù)的任何并發(fā)調(diào)度都是可串行化調(diào)度，即可以確保這些并發(fā)事務(wù)執(zhí)行后的數(shù)據(jù)正確性。不可串行化可串行化四、死鎖問題解決在基于鎖機(jī)制的并發(fā)事務(wù)執(zhí)行中，如果這些事務(wù)同時鎖定兩個以及以上資源時，可能會出現(xiàn)彼此都不能繼續(xù)執(zhí)行的狀態(tài)，即事務(wù)死鎖狀態(tài)。1.事務(wù)死鎖例

兩個事務(wù)T1和T2，它們都需要加鎖訪問數(shù)據(jù)庫表Table1和Table2，其事務(wù)程序見圖6-20a所示。當(dāng)這兩個事務(wù)程序調(diào)度執(zhí)行時，可能會出現(xiàn)死鎖狀態(tài)，見圖6-20b所示。允許用戶一次發(fā)出當(dāng)前所需全部資源的鎖定，使用完成后，再釋放給其它用戶訪問。規(guī)定所有應(yīng)用程序鎖定資源的順序必須完全相同。2.死鎖出現(xiàn)的必要條件3.防范死鎖的策略互斥條件請求和保持條件不剝奪條件環(huán)路等待條件當(dāng)發(fā)生死鎖時，回滾其中的一個事務(wù)，并取消它對數(shù)據(jù)庫所做的改動。4.解決死鎖的辦法五、事務(wù)隔離級別說明：事務(wù)隔離級別設(shè)置是在DBMS中執(zhí)行SETTRANSACTION命令來實現(xiàn)，也可通過管理工具設(shè)置。事務(wù)隔離級別設(shè)置越高，出現(xiàn)數(shù)據(jù)不一致的可能性越小，但系統(tǒng)吞吐量也越小。

關(guān)系數(shù)據(jù)庫管理系統(tǒng)為了防范事務(wù)并發(fā)運行帶來數(shù)據(jù)不一致問題，可以設(shè)置特定數(shù)據(jù)庫事務(wù)隔離級別。本節(jié)學(xué)習(xí)結(jié)束！5.6-1安全管理——存取安全模型了解數(shù)據(jù)庫系統(tǒng)可能面臨的安全風(fēng)險理解數(shù)據(jù)庫系統(tǒng)安全體系理解數(shù)據(jù)庫存取控制安全模型【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫系統(tǒng)面臨的安全風(fēng)險

黑客利用系統(tǒng)漏洞，攻擊系統(tǒng)運行、竊取和篡改系統(tǒng)數(shù)據(jù)。

內(nèi)部人員非法地泄露、篡改、刪除系統(tǒng)的用戶數(shù)據(jù)。

系統(tǒng)運維人員操作失誤導(dǎo)致數(shù)據(jù)被刪除或數(shù)據(jù)庫服務(wù)器系統(tǒng)宕機(jī)。

系統(tǒng)故障導(dǎo)致數(shù)據(jù)庫的數(shù)據(jù)損壞、數(shù)據(jù)丟失、數(shù)據(jù)庫實例無法啟動。

意外災(zāi)害事件（火災(zāi)、水災(zāi)、地震等自然災(zāi)害）導(dǎo)致系統(tǒng)被破壞。

。。。二、數(shù)據(jù)庫系統(tǒng)安全模型身份驗證：從應(yīng)用系統(tǒng)層面確認(rèn)登錄用戶是否是合法使用者權(quán)限控制：從DBMS系統(tǒng)層面通過存取權(quán)限機(jī)制控制用戶對數(shù)據(jù)的訪問系統(tǒng)防護(hù)：從操作系統(tǒng)層面提供的安全機(jī)制防范非法系統(tǒng)訪問加密存儲：從數(shù)據(jù)存儲層面通過加密算法對數(shù)據(jù)庫中數(shù)據(jù)進(jìn)行加密存儲三、數(shù)據(jù)庫存取權(quán)限控制安全模型例

在3.7.1節(jié)的工程項目管理系統(tǒng)中，假定系統(tǒng)用戶有三類角色：員工、經(jīng)理和系統(tǒng)管理員。它們對數(shù)據(jù)庫各個表對象的擁有權(quán)限見下表所示。表員工經(jīng)理系統(tǒng)管理員DEPARTMENT讀取讀取、插入、修改、刪除賦予權(quán)限、修改結(jié)構(gòu)EMPLOYEE讀取、插入、修改讀取、插入、修改、刪除賦予權(quán)限、修改結(jié)構(gòu)PROJECT讀取讀取、插入、修改、刪除賦予權(quán)限、修改結(jié)構(gòu)ASSIGNMENT讀取讀取、插入、修改、刪除賦予權(quán)限、修改結(jié)構(gòu)工程項目管理系統(tǒng)數(shù)據(jù)庫存取權(quán)限控制安全模型設(shè)計“員工”角色的用戶存取訪問權(quán)限設(shè)計問題探討:

在選課管理系統(tǒng)中，有學(xué)生、教師和教務(wù)管理員角色。如何設(shè)計各角色的數(shù)據(jù)庫表對象的訪問操作權(quán)限?數(shù)據(jù)庫表學(xué)生(StudentRole)教師(TeacherRole)教務(wù)管理員(AcademicRole)CollegeCourseTeacherStudentPlanRegister選課管理系統(tǒng)中，學(xué)生、教師和教務(wù)管理員角色的數(shù)據(jù)庫表對象訪問權(quán)限設(shè)計數(shù)據(jù)庫表學(xué)生(StudentRole)教師(TeacherRole)教務(wù)管理員(AcademicRole)College

查詢

查詢

查詢、插入、修改、刪除Course

查詢

查詢

查詢、插入、修改、刪除Teacher

查詢

查詢、修改

查詢、插入、修改、刪除Student

查詢、修改

查詢

查詢、插入、修改、刪除Plan

查詢

查詢

查詢、插入、修改、刪除Register

查詢、修改

查詢

查詢、插入、修改、刪除本節(jié)學(xué)習(xí)結(jié)束！5.6-2安全管理——用戶、角色、權(quán)限管理掌握數(shù)據(jù)庫用戶管理方法掌握數(shù)據(jù)庫權(quán)限管理方法掌握數(shù)據(jù)庫角色管理方法【本節(jié)學(xué)習(xí)目標(biāo)】一、用戶管理用戶要訪問數(shù)據(jù)庫，必須先在DBMS中創(chuàng)建其賬號，并成為數(shù)據(jù)庫的用戶。此后，用戶每次訪問數(shù)據(jù)庫，都需要在DBMS進(jìn)行身份驗證，只有合法用戶才能進(jìn)入系統(tǒng)，訪問操作數(shù)據(jù)庫對象。實現(xiàn)用戶管理方式：數(shù)據(jù)庫服務(wù)器執(zhí)行SQL語句管理用戶通過管理工具GUI操作管理用戶1.用戶創(chuàng)建SQL語句CREATEUSER<用戶賬號名>[[WITH]option[…]];用戶管理——在數(shù)據(jù)庫安全管理中，DBMS需要對每個用戶進(jìn)行管理，如用戶創(chuàng)建、用戶修改、用戶刪除管理等。CREATEUSER"userA"WITHLOGINNOSUPERUSERNOCREATEDBNOCREATEROLEINHERITNOREPLICATIONCONNECTIONLIMIT-1PASSWORD'123456';例

創(chuàng)建一個新用戶，其賬號名字為“userA”，密碼為“123456”。該用戶具有登錄權(quán)限（Login）和角色繼承權(quán)限（Inherit）系統(tǒng)權(quán)限，但它不是超級用戶（SuperUser），不具有創(chuàng)建數(shù)據(jù)庫權(quán)限（CreateDB）、創(chuàng)建角色權(quán)限（CreateRole）、數(shù)據(jù)庫復(fù)制權(quán)限（Replication），此外數(shù)據(jù)庫連接數(shù)（ConnectionLimit）不受限。用戶創(chuàng)建SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息2.用戶修改SQL語句ALTERUSER<用戶名>[[WITH]option[...]]; --修改用戶的屬性ALTERUSER<用戶名>RENAMETO<新用戶名>; --修改用戶的名稱ALTERUSER<用戶名>SET<參數(shù)項>{TO|=}{value|DEFAULT}; --修改用戶的參數(shù)值

ALTERUSER<用戶名>RESET<參數(shù)項>; --重置用戶參數(shù)值例

修改用戶“userA”的賬號密碼為“gres123”。同時也限制該用戶的數(shù)據(jù)庫連接數(shù)為10。ALTERUSER"userA"

CONNECTIONLIMIT10

PASSWORD'gres123';用戶修改SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息3.用戶刪除SQL語句DROPUSER<用戶名>;例

在數(shù)據(jù)庫中，刪除用戶“userA”。可以通過執(zhí)行如下用戶刪除SQL語句實現(xiàn)用戶刪除。DROPUSERuserA;用戶刪除SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息二、權(quán)限管理權(quán)限管理基本操作：授予權(quán)限收回權(quán)限拒絕權(quán)限數(shù)據(jù)庫權(quán)限管理是指DBA管理員或數(shù)據(jù)庫對象擁有者控制其它角色或用戶對其所擁有對象進(jìn)行限制訪問。權(quán)限類別：數(shù)據(jù)庫系統(tǒng)權(quán)限數(shù)據(jù)庫對象訪問操作權(quán)限數(shù)據(jù)庫對象定義操作權(quán)限例

在3.7.1節(jié)的工程項目管理系統(tǒng)中，DBA管理員賦予員工用戶（userA）對部門表（Department）、員工表（Employee）、項目表（Project）和任務(wù)表（Assignment）的讀取數(shù)據(jù)權(quán)限。GRANTSELECTONDepartmentTO

userA;GRANTSELECTONEmployeeTO

userA;GRANTSELECTONProjectTO

userA;GRANTSELECTONAssignmentTO

userA;對用戶“userA”實現(xiàn)授權(quán)SQL程序如下用戶授權(quán)SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息三、角色管理角色管理實現(xiàn)方式：執(zhí)行SQL語句管理角色通過GUI操作管理角色在DBMS中，為了方便對眾多用戶及其權(quán)限進(jìn)行管理，通常將一組具有相同權(quán)限的用戶定義為角色(Role)。角色管理內(nèi)容：創(chuàng)建角色修改角色刪除角色1.角色管理SQL語句CREATEROLE<角色名>[[WITH]option[...]]; --創(chuàng)建角色ALTERROLE<角色名>[[WITH]option[...]]; --修改角色屬性ALTERROLE<角色名>RENAMETO<新角色名>; --修改角色名稱

ALTERROLE<角色名>SET<參數(shù)項>{TO|=}{value|DEFAULT}; --修改角色參數(shù)值A(chǔ)LTERROLE<角色名>RESET<參數(shù)項>; --復(fù)位角色參數(shù)值DROPROLE<角色名>; --刪除指定角色2.角色管理實例例

在工程項目管理系統(tǒng)中，假定需要在ProjectDB數(shù)據(jù)庫內(nèi)創(chuàng)建經(jīng)理角色Role_Manager。該角色具有登錄權(quán)限（Login）和角色繼承權(quán)限（Inherit）系統(tǒng)權(quán)限，但它不是超級用戶（SuperUser），不具有創(chuàng)建數(shù)據(jù)庫權(quán)限（CreateDB）、創(chuàng)建角色權(quán)限（CreateRole）、數(shù)據(jù)庫復(fù)制權(quán)限（Replication），此外數(shù)據(jù)庫連接數(shù)（ConnectionLimit）不受限。CREATEROLE"Role_Manager"WITHLOGINNOSUPERUSERNOCREATEDBNOCREATEROLEINHERITNOREPLICATIONCONNECTIONLIMIT-1;角色創(chuàng)建SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息3.角色權(quán)限授予例

在創(chuàng)建好經(jīng)理角色Role_Manager后，還需要賦予該角色對數(shù)據(jù)庫表Department、Employee、Project、Assignment的插入、修改、刪除、查詢權(quán)限。GRANTSELECT,INSERT,UPDATE,DELETEONDepartmentTO"Role_Manager";GRANTSELECT,INSERT,UPDATE,DELETEONEmployeeTO"Role_Manager";GRANTSELECT,INSERT,UPDATE,DELETEONProjectTO"Role_Manager";GRANTSELECT,INSERT,UPDATE,DELETEONAssignmentTO"Role_Manager";角色授權(quán)SQL語句執(zhí)行運行按鈕SQL語句結(jié)果消息本節(jié)學(xué)習(xí)結(jié)束！5.7數(shù)據(jù)庫備份與恢復(fù)了解數(shù)據(jù)庫備份方法與類型掌握PostgreSQL數(shù)據(jù)庫備份方法了解數(shù)據(jù)庫恢復(fù)方法與類型掌握PostgreSQL數(shù)據(jù)庫恢復(fù)方法【本節(jié)學(xué)習(xí)目標(biāo)】一、數(shù)據(jù)庫系統(tǒng)故障原因數(shù)據(jù)庫服務(wù)器硬件故障系統(tǒng)軟件故障用戶誤操作系統(tǒng)意外斷電。。。二、數(shù)據(jù)庫備份與恢復(fù)數(shù)據(jù)庫備份——是指將數(shù)據(jù)庫當(dāng)前數(shù)據(jù)和狀態(tài)進(jìn)行副本復(fù)制，以便當(dāng)數(shù)據(jù)庫受到破壞或丟失數(shù)據(jù)時可以進(jìn)行修復(fù)。數(shù)據(jù)庫恢復(fù)——是指數(shù)據(jù)庫中數(shù)據(jù)丟失或被破壞時，從備份副本將數(shù)據(jù)庫從錯誤狀態(tài)恢復(fù)到某一正確狀態(tài)。三、備份內(nèi)容與備份角色備份內(nèi)容——包括數(shù)據(jù)文件、日志文件等。備份角色——可以是服務(wù)器管理員（sysadmin）、數(shù)據(jù)庫所有者（db_owner）、數(shù)據(jù)庫備份員（db_backupoperator）角色之一。四、備份介質(zhì)與備份時機(jī)備份介質(zhì)——包括磁盤陣列、磁帶庫、光盤庫等。備份時機(jī)——當(dāng)系統(tǒng)數(shù)據(jù)庫重要數(shù)據(jù)被修改、日志被清理、用戶數(shù)據(jù)庫加載等事件出現(xiàn)時。五、數(shù)據(jù)庫備份方法備份方法：完全數(shù)據(jù)庫備份差異數(shù)據(jù)庫備份事務(wù)日志備份文件備份備份方式：冷備份熱備份實例：

備份LibDB數(shù)據(jù)庫到一個G磁盤的根目錄文件LibDB中。pg_dump-h127.0.0.1-UpostgresLibDB>G:\LibDB.bak備份實現(xiàn)方式：執(zhí)行實用程序命令實現(xiàn)備份（pg_dump或pg_dumpall）操作GUI工具實現(xiàn)備份六、數(shù)據(jù)庫備份實現(xiàn)七、數(shù)據(jù)庫恢復(fù)方法1.通過備份文件進(jìn)行恢復(fù)實例：

從存儲備份文件中恢復(fù)LibDB數(shù)據(jù)庫。

psql-h127.0.0.1-UpostgresLibDB<G:\LibDB.bak當(dāng)使用備份數(shù)據(jù)庫文件將數(shù)據(jù)庫恢復(fù)到一個已知節(jié)點后,然后重新處理當(dāng)時的所有工作。本方法特點：恢復(fù)技術(shù)簡單，易于實現(xiàn)只能恢復(fù)到上一備份時刻數(shù)據(jù)狀態(tài)，備份周期內(nèi)改變的數(shù)據(jù)會丟失恢復(fù)實現(xiàn)方式：執(zhí)行實用程序命令實現(xiàn)數(shù)據(jù)庫恢復(fù)(psql或pg_restore)操作GUI工具實現(xiàn)數(shù)據(jù)庫恢復(fù)2.利用事務(wù)日志按前滾或回滾方式進(jìn)行數(shù)據(jù)庫恢復(fù)八、基于GUI的PostgreSQL數(shù)據(jù)庫系統(tǒng)備份與恢復(fù)1.數(shù)據(jù)庫備份2.數(shù)據(jù)庫恢復(fù)本節(jié)學(xué)習(xí)結(jié)束！5.8PostgreSQL數(shù)據(jù)庫管理項目實踐掌握數(shù)據(jù)庫系統(tǒng)角色創(chuàng)建方法掌握賦予數(shù)據(jù)庫角色對象訪問權(quán)限方法掌握創(chuàng)建數(shù)據(jù)庫用戶及其角色賦予方法掌握系統(tǒng)數(shù)據(jù)庫備份方法掌握系統(tǒng)數(shù)據(jù)庫恢復(fù)方法【本節(jié)學(xué)習(xí)目標(biāo)】一、項目案例——成績管理系統(tǒng)本節(jié)結(jié)合一個“成績管理系統(tǒng)”項目案例的數(shù)據(jù)庫管理，學(xué)習(xí)PostgreSQL數(shù)據(jù)庫管理操作，理解