【文章內(nèi)容簡介】 L中的事務(wù) ? SQL中的安全機(jī)制和用戶認(rèn)證 64 什么是 SQL環(huán)境 ? SQL環(huán)境是 SQL中數(shù)據(jù)所在的框架，為數(shù)據(jù)操作提供支持，即一個(gè)運(yùn)行環(huán)境 (如 DBMS的運(yùn)行環(huán)境 )。 ? 數(shù)據(jù)庫的所有元素，包括表、視圖、觸發(fā)器等，都是在 SQL環(huán)境中定義的。這些元素組成了 層次性結(jié)構(gòu) ： ?模式 ?目錄 ?簇 65 1. 模式 ? 模式是組織的基本單元。 ? 模式是表、視圖、斷言、觸發(fā)器等模式元素的集合。 ? 模式聲明的形式 CREATE SCHEMA模式名 模式元素聲明 ? 模式聲明示例 66 1. 模式 (續(xù) ) ? CREATE SCHEMA 提供了在 SQL語句中創(chuàng)建表、視圖以及授予對象權(quán)限的方法，并區(qū)別重名，即屬于不同模式的模式元素可以重名，可用“模式名 .模式元素名”來引用某個(gè)模式元素。 ? 沒有必要一次就聲明完所有的模式。可以使用合適的CREATE、 DROP或 ALTER語句來修改或增加模式 ? 使用 SET SCHEMA語句改變“當(dāng)前”的模式。 ?例如： SET SCHEMA MovieSchama 。 將使例 67 2. 目錄 ? 模式的集合。每個(gè)目錄有一個(gè)或多個(gè)模式，目錄中的模式名必須唯一，每個(gè)目錄包含一個(gè)叫INFORMATION_SCHEMA的特殊模式，這個(gè)模式包含了該目錄中所有模式的信息。 ? 沒有 CREATE CATALOG語句，只規(guī)定了 設(shè)置當(dāng)前目錄的語句： ? SET CATALOG 目錄名 ； ?對模式的操作 (新建 /修改模式等 )都是針對當(dāng)前目錄 68 模式元素的全名 ? 目錄名 . 模式名 . 模式元素名 ?例如： 目錄 MovieCatalog中的模式 MovieSchema的表Movies的引用如下： MovieCatalog . MovieSchema . Movies ? 如果目錄是缺省的或是當(dāng)前的目錄，則可省去目錄名 ? 如果模式也是缺省的或當(dāng)前的模式，那么模式部分也可以省去，這樣只留下元素自己的名稱。 ? 然而，當(dāng)需要訪問當(dāng)前模式或目錄以外的元素時(shí)，就不得不使用完全名。 69 3. 簇 ? 簇是目錄的集合。每個(gè)用戶有一個(gè)關(guān)聯(lián)的簇：用戶可訪問的所有目錄的集合。簇是被提交的查詢的最大范圍，故在一定程度上，簇是特定用戶所看到的“數(shù)據(jù)庫”。 70 SQL環(huán)境中的客戶端和服務(wù)器 ? 依照 SQL標(biāo)準(zhǔn)， SQL環(huán)境有兩種特殊的進(jìn)程： ? SQL 服務(wù)器： 支持對數(shù)據(jù)庫元素的操作，充當(dāng)數(shù)據(jù)庫服務(wù)器的角色。 ? SQL客戶端： 允許用戶連接到服務(wù)器上，對數(shù)據(jù)庫進(jìn)行操作 ? 例如： ?服務(wù)器運(yùn)行在一個(gè)大的存儲(chǔ)數(shù)據(jù)庫的主機(jī)上，而客戶端運(yùn)行在其他主機(jī)上，也許是遠(yuǎn)離服務(wù)器的個(gè)人工作站，也許是和服務(wù)器運(yùn)行在同一臺(tái)主機(jī)上。 71 連接 ? 如果在 SQL客戶端主機(jī)上運(yùn)行包含 SQL 的程序，則可通過下面的 SQL語句打開客戶端和服務(wù)器間的連接： CONNECT TO 服務(wù)器名 AS 連接名 AUTHORIZATION 用戶名和密碼 ? 服務(wù)器名 依賴于安裝。可用名稱 DEFAULT將用戶連接到任何被作為“缺省服務(wù)器”安裝的 SQL服務(wù)器。如果僅是在SQL客戶端主機(jī)上執(zhí)行 SQL語句，也可為自己建立一個(gè)缺省連接 ? 連接名 可在以后用于引用連接。原因是 SQL允許用戶打開好幾個(gè)連接，但任何時(shí)候只有一個(gè)連接有效 ? AUTHORIZATION授權(quán)子句 后跟隨著用戶名和密碼。 72 引用連接 ? 將 conn1切換為有效連接： SET CONNECTION conn1。 ? 任何當(dāng)前有效的連接進(jìn)入休眠狀態(tài)后，只有用下列語句顯式地調(diào)用才能將其激活： SET CONNECTION conn1。 ? 斷開連接時(shí) conn1被中止，不是休眠，也不能被激活 : DISCONNECT conn1。 ? 注：如果連接創(chuàng)建后再也不被引用， CONNECT TO子句中的 AS和連接名可以省略 73 會(huì)話 ? 連接有效時(shí)，執(zhí)行的 SQL操作形成了一個(gè)會(huì)話 ? 會(huì)話和創(chuàng)建它的連接具有相同的生命周期 。 例如 : ?當(dāng)連接處于休眠狀態(tài)時(shí)，它的會(huì)話也處于休眠態(tài) ? SET CONNECTION語句可激活連接，同時(shí)激活相應(yīng)會(huì)話。連接會(huì)話是客戶和服務(wù)器間鏈路的兩個(gè)方面 ? 每個(gè)會(huì)話有一個(gè)當(dāng)前目錄和該目錄中的一個(gè)當(dāng)前模式 ?由語句 SET SCHEMA和 SET CATALOG進(jìn)行設(shè)置 ?每個(gè)會(huì)話都有一個(gè)授權(quán)用戶 74 模塊 ? 模塊是對應(yīng)用程序而言的， SQL標(biāo)準(zhǔn)提出三種模塊： ? 普通 SQL界面： 用戶可以鍵入 SQL服務(wù)器執(zhí)行的 SQL語句。這種模式下，每個(gè)查詢或其他語句本身是一個(gè)模塊。 ? 嵌套 SQL： 預(yù)處理器將這個(gè)嵌套的 SQL語句轉(zhuǎn)變?yōu)?SQL系統(tǒng)的對應(yīng)函數(shù)或過程調(diào)用。編譯后的宿主語言程序 (包括這些函數(shù)調(diào)用 )是一個(gè)模塊。 ? 真模塊： 含有存儲(chǔ)函數(shù)或過程集合的模塊。這些函數(shù) |過程部分是宿主語言代碼，部分是 SQL語句，它們之間可通過參數(shù)或共享變量進(jìn)行通訊。 ? 僅要求 SQL的實(shí)現(xiàn)中至少提供一種類型給用戶。 75 SQL客戶端 服務(wù)器交互 ? 模塊的執(zhí)行被稱為 SQL代理 ? 模塊 (代碼 )和 SQL代理 (代碼的執(zhí)行 )作為一個(gè)單元，通過訪問 SQL客戶端建立與數(shù)據(jù)庫的連接 76 主要內(nèi)容 ? 編程環(huán)境下的 SQL ? 模式中的存儲(chǔ)過程 ? SQL 環(huán)境 ? 使用調(diào)用層接口 ? Java數(shù)據(jù)庫連接 ? SQL中的事務(wù) ? SQL中的安全機(jī)制和用戶認(rèn)證 77 主要內(nèi)容 ? 編程環(huán)境下的 SQL ? 模式中的存儲(chǔ)過程 ? SQL 環(huán)境 ? 使用調(diào)用層接口 ? Java數(shù)據(jù)庫連接 ? SQL中的事務(wù) ? SQL中的安全機(jī)制和用戶認(rèn)證 78 1. 問題的提出 ? 像銀行業(yè)務(wù)或機(jī)票預(yù)定這樣的應(yīng)用，每秒鐘都會(huì)有上百個(gè)操作在數(shù)據(jù)庫上執(zhí)行。 ? 如果 DBMS完全沒有約束對數(shù)據(jù)庫操作的順序 ,將會(huì)出現(xiàn)錯(cuò)誤。 79 (1) 可串行性 ? 示例 ： 假設(shè)航空公司數(shù)據(jù)庫里有這樣一個(gè)關(guān)系 Flights (fltNo, fltDate, seatNo, seatStatus) ? Step1: 查詢所需航班的空位 ? Step2: 若找到空位，則占用這個(gè)座位，更新數(shù)據(jù)庫 SELECT seatNo FROM Flights WHERE fltNo=123 AND fltDate=DATE ‘ 20231225’ AND seatStatus=‘ available’ 。 UPDATE Flights SET seatStatus=‘ occupied’ WHERE fltNo=123 AND fltDate=DATE ‘ 20231225’ AND seatNo=’ 22A’ 。 80 用戶 1發(fā)現(xiàn)座位空 用戶 2發(fā)現(xiàn)座位空 用戶 1占用 用戶 2占用 不同用戶占用同一座位 不同用戶在不同售票點(diǎn)按上述 2個(gè)步驟購票 問題 81 解決方法 ? 用戶 1的操作執(zhí)行完，再執(zhí)行用戶 2的操作。 ? 將一組操作組成一個(gè) 事務(wù) ，事務(wù)中進(jìn)行封鎖，保證事務(wù)的 可串行性 。 ? 可串行性： 一個(gè)時(shí)刻只有一個(gè)事務(wù)，相互之間沒有重疊。 82 (2) 原子性 ? 示例 假設(shè)銀行數(shù)據(jù)庫有關(guān)系 Accounts (acctNo, balance) ? 考慮從賬戶 123向賬戶 456轉(zhuǎn)帳 $100。可以先檢查賬戶123中是否至少有 $100，若有，則執(zhí)行以下兩個(gè)步驟： ? 1. 通過如下 SQL修改語句向賬戶 456加上 $100 ? 2. 通過如下 SQL修改語句從賬戶 123減去 $100 UPDATE Accounts SET balance=balance+100 WHERE acctNo=456。 UPDATE Accounts SET balance=balance100 WHERE acctNo=123。 83 問題 ? 設(shè)想： 金額轉(zhuǎn)入賬戶 2 之后，系統(tǒng)故障。 ? 數(shù)據(jù)庫狀態(tài)： 賬戶 2 增加金額，賬戶 1未減金額 對銀行造成損失 ? 問題 ： 數(shù)據(jù)處于不一致狀態(tài) ? 結(jié)論 ： 數(shù)據(jù)庫操作的某些組合，需 原子地 完成 即要么都做 要么都不做。 84 2. 事務(wù) ? 事務(wù)： 必須被原子地執(zhí)行的一個(gè)或多個(gè)數(shù)據(jù)庫操作的集合， (如：一組 SQL語句 ,一條 SQL語句或一段程序 ) ?要么所有操作都執(zhí)行，要么所有操作都不執(zhí)行。 ?另外 SQL要求事務(wù)缺省地以可串行化方式執(zhí)行。 ? 在 SQL語言中定義事務(wù)的相關(guān)語句 ? START TRANSACTION 事務(wù)開始 ? COMMIT TRANSACTION 提交，事務(wù)成功結(jié)束 ? ROLLBACK TRANSACTION 撤消 (夭折、回滾 ) 85 課本示例 42 ? 假設(shè)要把例 。 ? 在訪問數(shù)據(jù)庫之前要執(zhí)行 START TRANSACTION。 ? 如果發(fā)現(xiàn)資金不夠轉(zhuǎn)帳，則執(zhí)行 ROLLBACK命令。 ? 但是，如果有足夠資金，那么執(zhí)行那兩條修改語句，然后執(zhí)行 COMMIT。 86 保證事務(wù)正確執(zhí)行的 ACID性質(zhì) ? A：原子性 ?事務(wù)中的操作或者都執(zhí)行或者都不執(zhí)行。 ? C：一致性 ?事務(wù)執(zhí)行前后數(shù)據(jù)庫滿足所有約定的一致性條件。 ? I ：獨(dú)立性 ?兩個(gè)事務(wù)同時(shí)執(zhí)行不相互干擾。 ? D：持久性 ?事務(wù)完成后即使系統(tǒng)故障，事務(wù)的結(jié)果長期保存。 例如：賬戶余額不能是負(fù)數(shù) 87 3. 只讀事務(wù) ? 航班預(yù)定和銀行轉(zhuǎn)帳的例子，都包含一個(gè)先讀 然后 (可能 )向數(shù)據(jù)庫中寫一些數(shù)據(jù)的事務(wù)。這種事務(wù)容易出現(xiàn)可串行化問題。 ? 然而當(dāng)一個(gè)事務(wù)只讀數(shù)據(jù)而不寫數(shù)據(jù)時(shí)，就可以更自由地讓該事務(wù)與別的事務(wù)并發(fā)執(zhí)行。 88 示例 ? 例：讀數(shù)據(jù)判定座位是否有效 ? 一次可以執(zhí)行多個(gè)查詢程序的調(diào)用，不用擔(dān)心會(huì)對數(shù)據(jù)庫造成永久性的傷害。 ? 可能出現(xiàn)最壞的情況是，讀取某個(gè)座位是否空閑的數(shù)據(jù)時(shí)，該座位正在被某個(gè)別的程序預(yù)定或釋放。當(dāng)前查詢得到的答案只在當(dāng)前時(shí)刻有效。 SELECT seatNo FROM Flights WHERE fltNo=123 AND fltDate=DATE ‘ 20231225’ AND seatStatus=‘ available’ 。 89 只讀事務(wù)的設(shè)置 ? 如果 SQL執(zhí)行系統(tǒng)被告知當(dāng)前事務(wù)只讀，則許多訪問同一數(shù)據(jù)的只讀事務(wù)可以并發(fā)地運(yùn)行，但是寫同一數(shù)據(jù)的事務(wù)不可以。 ? 只讀、讀寫的設(shè)置 ? SET TRANSACTION READ ONLY ? SET TRANSACTION READ WRITE (默認(rèn) ) ? 只讀事務(wù) 可以 更自由地與其他事務(wù)并行 90 4. 讀臟數(shù)據(jù) ? 臟數(shù)據(jù)（ dirty data） ：未提交事務(wù)所寫的數(shù)據(jù) ? 臟讀?。?dirty read） ：對臟數(shù)據(jù)的讀取 ? 讀臟數(shù)據(jù)的風(fēng)險(xiǎn)： ?寫數(shù)據(jù)的事務(wù)夭折，臟數(shù)據(jù)被移走，