• 描述鎖定機制以及Oracle 如何管理資料并發處理 • 使用SQL 管理資料 • 識别和管理PL/SQL 對象 • 描述觸發器和觸發事件 • 監視和解決鎖定沖突
- 通過SQL 處理資料
在資料庫中使用基本資料操縱語言(DML) 語句來處理資料。
- INSERT 指令
• 一次建立一行。 • 插入另一個表中的多行。 使用基本的INSERT 語句一次可建立一行。如果使用所謂的子選擇，則可以使用INSERT 指令将一個表中的若幹行複制到另一個表。這種方法又稱為INSERT SELECT 語句。 如以下INSERT 指令： insert into dept_80 (select * from employees where department_id = 80); 這種情況下，dept_80表擁有與employees 表完全相同的結構。如果不是這種情況， 則可命名每個表中的列。SELECT 語句中選擇的各個值與要插入的表中的各個列相關聯。 列值按INSERT 和SELECT 語句中指定的順序進行比對。隻要資料類型完全比對即可。 例如： insert into just_names (first, last) (select first_name, last_name from employees); 其中just_names表中僅有的兩列的資料類型與employees 表中first_name和 last_name 列的資料類型相同。 使用INSERT SELECT 方法可将一個或多個表中的大量資料裝入到另一個表中。
- UPDATE 指令
使用UPDATE 指令可更改表的零行或多行。 UPDATE 指令用于修改表中的現有行。UPDATE 指令所修改的行數取決于WHERE條件。 如果省略了WHERE子句，則會更改所有行。如果所有行都不滿足WHERE條件，則不修改 任何行。
- DELETE 指令
使用DELETE 指令可從表中删除零行或多行。 DELETE 指令用于從表中删除現有行。DELETE 指令所修改的行數取決于WHERE條件。 如果省略了WHERE子句，則删除所有行。如果所有行都不滿足WHERE條件，則不删除任 何行。注意，未删除任何行，這并不表示出現了錯誤；傳回的消息僅表明從表中 删除了零行。
- MERGE 指令
使用MERGE指令可通過一個指令同時執行INSERT 和 UPDATE 操作。 MERGE INTO jobs j USING (SELECT * FROM jobs_acquisition) a ON (j.job_id=a.job_id) WHEN MATCHED THEN UPDATE SET j.job_title=a.job_title WHEN NOT MATCHED THEN INSERT (j.job_id,j.job_title,j.min_salary,j.max_salary) 使用MERGE指令可在一個指令中執行UPDATE 和INSERT 。可将一個源中的資料合并到 另一個源，因而可選擇插入新行和更新特定列（如果行已經存在）。 請考慮以下示例。JOBS 表中的某些資料如下所示： JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- -------------- ---------- ---------- AD_PRES President 20000 40000 FI_ACCOUNT Accountant 4200 9000 ST_CLERK Stock Clerk 2000 5000 IT_PROG Programmer 4000 10000
以下是JOBS_ACQUISITION 表的内容： JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY ---------- -------------- ---------- ---------- AD_PRES VP 20000 40000 DBA DB Admin 4200 9000 SA Sys Admin 2000 5000 使用MERGE指令可将具有新JOB_ID 的所有行插入到JOBS 表中，如果JOB_ID 已存在， 則使用JOB_TITLE 更新現有JOBS 行。結果“President ”職位更改為“VP”，并且添加 了新職務“SA”和“DBA”。
- COMMIT 和ROLLBACK 指令
下面的指令用于結束事務處理： • COMMIT ：使更改成為永久性更改 • ROLLBACK ：撤消更改 預設情況下，不會送出輸入的每個DML 指令。幾個工具（包括i SQL*Plus）提供了一些 選項，使用這些選項可以基于每個指令進行送出，或基于一組指令進行送出。 在發出COMMIT 或ROLLBACK 之前， 更改處于暫挂狀态。僅執行更改的使用者可以檢視更 改後的資料。其他使用者可選擇相同資料，但隻能看到更改之前的資料。其他使用者不能對别 的使用者已更改的資料發出DML。 預設情況下，當一個使用者嘗試更改另一個使用者正更改的行時，此使用者必須等待，直到執行 更改的使用者送出或回退更改為止。這由Oracle 資料庫的鎖定機制自動進行控制。由于鎖 定機制已經内置到行中，是以資料庫絕不會用完鎖。
- PL/SQL
Oracle 對SQL 的過程語言擴充(PL/SQL) 是第四代程式設計 語言(4GL) 。它提供了以下功能： • 對SQL 的過程擴充 • 平台和産品間的可移植性 • 更進階别的安全性和資料完整性保護 • 支援面向對象的程式設計 PL/SQL 是Oracle 專有的第四代程式設計語言，它提供了對SQL 的過程擴充。PL/SQL 為 Oracle 資料庫和應用程式提供了一種公共程式設計環境，适用于所有作業系統或硬體平台。 借助PL/SQL，可以使用SQL 語句處理資料，并且還可以使用過程結構（如IF-THEN、 CASE 和LOOP）控制程式設計流。另外，還可以聲明常量和變量，定義過程和函數，使用集 合和對象類型，以及使用陷阱來捕獲運作時錯誤。在PL/SQL 程式中還可調用使用其它語 言（如C、C++ 和Java ）編寫的程式。 PL/SQL 還提供了資料保護功能。 調用方不必知道要讀取或處理的資料結構便可進行調用。 另外，調用方不必具有通路這些對象的權限，隻需要具有執行PL/SQL 程式的權限就足夠 了。可以選擇使用另一種模式的權限來調用PL/SQL，這種情況下，調用方必須有權執行 調用程式運作期間執行的每個語句。 因為PL/SQL 代碼在資料庫内部運作，是以這種代碼在執行資料量巨大的操作時非常有效， 并且可最大程度地降低應用程式的網絡通信量。
- 管理PL/SQL 對象
資料庫管理者應可以： • 找出存在問題的PL/SQL 對象 • 建議适當的PL/SQL 用法 • 将PL/SQL 對象裝入到資料庫中 • 協助PL/SQL 開發人員診斷故障 作為DBA，通常不負責将PL/SQL 代碼裝入到資料庫中，也不負責協助開發人員診 斷故障。另外，通常不要求DBA 使用PL/SQL 來編寫應用程式，但作為DBA 應對不 同的PL/SQL 對象有足夠的了解，才能為應用開發人員提供建議，也才能找出存在問題的 對象。 在Database Control 中，單擊“Schema（方案）”中的“Administration（管理）”标簽可 通路PL/SQL 對象。單擊對象類型時，可檢視、修改和建立標明PL/SQL 對象的類型。
- PL/SQL 對象
PL/SQL 資料庫對象有多種類型： • 程式包：程式包是由邏輯上相關的一些過程和函數組成的集合。程式包的這一部分 又稱為說明，用于描述應用程式的接口；它聲明了可供使用的類型、變量、常量、 異常錯誤、遊标和子程式。 • 程式包體：程式包體完整地定義了遊标和子程式，是以實施了說明。程式包體包含 實施明細和專用聲明，這些内容不顯示給調用方。 • 類型主體：類型主體是由與使用者定義的資料類型相關聯的一些方法（過程和函數） 組成的集合。 • 過程：過程是用于執行特定操作的 PL/SQL 塊。 • 函數：函數是使用 RETURN PL/SQL 指令傳回單個值的PL/SQL 塊。它是具有傳回值 的過程。 • 觸發器：觸發器是當資料庫中發生特定事件時執行的 PL/SQL 塊。這些事件可以基于 表，如在表中插入行時。也可以是資料庫事件，如在使用者登入資料庫時。
- 函數
PL/SQL 函數通常用于計算值。有許多内置函數，如SYSDATE、SUM 、AVG 和TO_DATE。 開發人員還可在編寫應用程式時建立自己的函數。PL/SQL 函數的代碼中必須包含 RETURN 語句。 如使用以下SQL 指令建立的： CREATE OR REPLACE FUNCTION compute_tax (salary NUMBER) RETURN NUMBER AS BEGIN IF salary<5000 THEN RETURN salary*.15; ELSE RETURN salary*.33; END IF; END; /
- 過程
• 用于執行特定操作 • 使用參數清單傳入和傳出值 • 可以使用以下指令進行調用： – CALL 指令（屬于SQL 語句） – EXECUTE指令（屬于SQL*Plus 指令） PL/SQL 過程用于執行特定操作。與函數一樣，過程可接受輸入值，執行IF-THEN、 CASE 和LOOP 等條件語句。
- 程式包
程式包是由函數和過程組成的集合。每個程式包應由兩個 對象組成： • 程式包說明 • 程式包體 程式包是函數與過程的組合。将一些函數和過程組成一個程式包，性能和可維護性會有所 提高。每個程式包應由兩個獨立編譯的資料庫對象組成： • 程式包說明：這個對象（有時稱為程式標頭）的對象類型為 PACKAGE，其中隻包含 程式包中的過程、函數和變量的定義。 • 程式包體：這個對象的對象類型為PACKAGE BODY，包含程式包說明中定義的子程 序的實際代碼。 使用點符号可調用程式包中的過程和函數： package_name.procedure or function name
在圖顯示的程式包中，可按如下方式調用子程式： SQL> SELECT money.compute_tax(salary) FROM hr.employees WHERE employee_id=107; SQL> EXECUTE money.give_raise_to_all;
- 程式包說明和程式包體
程式包體： • 與程式包說明是分開的。是以，可以更改并重新編譯程式包體代碼，此時不會将與 程式包說明相關的其它對象标記為無效。 • 包含程式包說明中定義的子程式的代碼。這是負責完成工作的部分。程式包說明表 明了如何調用程式包中的子程式；程式包體是代碼段。 • 隻有在編譯了程式包說明之後才能編譯程式包體。可以在沒有程式包體的情況下創 建程式包說明，但不能在沒有程式包說明的情況下建立程式包體。 • 通過包裝可隐藏代碼明細。包裝是一個可打亂PL/SQL 源代碼的獨立程式，是以可 不暴露源代碼的情況下傳送PL/SQL 應用程式。
- 内置程式包
• Oracle 資料庫帶有350 多個内置PL/SQL 程式包， 這些程式包可用于： – 管理和維護實用程式 – 擴充功能 • 可使用DESCRIBE 指令檢視子程式。 随Oracle 資料庫一起提供的内置PL/SQL 程式包可用于通路擴充的資料庫功能，例如進階 隊列、加密和檔案輸入/ 輸出(I/O)，其中還包含許多管理和維護實用程式。 管理者可使用哪些程式包取決于資料庫為之提供服務的應用程式的類型。以下是一些比較 常用的管理和維護程式包： • DBMS_STATS：用于收集、檢視和修改優化程式統計資訊 • DBMS_OUTPUT：通過PL/SQL 生成輸出 • DBMS_SESSION ：通過PL/SQL 通路ALTER SESSION 和SET ROLE 語句 • DBMS_RANDOM：生成随機數字 • DBMS_UTILITY ：擷取時間、CPU 時間和版本資訊；計算散列值，以及執行許多 其它功能 • DBMS_SCHEDULER ：排程可從PL/SQL 調用的函數和過程 • DBMS_CRYPTO：對資料庫資料進行加密和解密 • UTL_FILE：通過PL/SQL 讀寫作業系統檔案
- 觸發器
觸發器是存儲在資料庫中的PL/SQL 代碼對象，它們會在某些事件發生時自動運作或“觸 發”。Oracle 資料庫允許許多操作充當觸發事件，包括插入到表中、使用者登入資料庫以及 嘗試删除表或更改審計設定等操作。 觸發器可以調用其它過程或函數。觸發器的代碼最好簡短一些，需要較長代碼的内容盡量 放置到單獨的程式包中。 DBA 可使用觸發器來協助執行基于值的審計 、強制設定複雜限制條件，以及自動處理很多任務。
- 觸發事件
有許多事件可用來觸發觸發器，這些事件分為三類。 •DML 事件觸發器在通過語句修改資料時觸發。 • DDL 事件觸發器在通過語句建立或以某種方式修改對象時觸發。 • 資料庫事件觸發器在資料庫中發生特定事件時觸發。 大多數觸發器可指定為在事件發生前或事件發生後觸發。對于DML 事件，可将觸發器 設計為在執行某一語句時觸發一次，或者在修改每行時觸發一次。
- 鎖
• 可防止多個會話同時更改同一資料 • 是在指定語句的最低可能級别自動擷取的 • 不會更新
會話必須先鎖定要修改的資料，之後資料庫才允許會話修改相應資料。鎖定後，會話擁有 對資料的獨占控制權，這樣在釋放鎖之前，其它任何事務處理都不能修改鎖定的資料。 事務處理可以鎖定單個資料行、多個資料行、甚至整個表。Oracle DB 支援手動鎖定和自 動鎖定。自動擷取的鎖總是選擇盡可能低的鎖定級别，以盡量減少與其它事務處理的潛在 沖突。 注：Oracle 執行個體使用許多類型的鎖來保持内部一緻性。
- 鎖定機制
• 進階資料并發處理： – 執行插入、更新和删除時使用行級鎖 – 查詢不需要任何鎖 • 自動隊列管理 • 在事務處理結束（使用COMMIT 或ROLLBACK 操作） 之前會一直保持鎖定
鎖定機制用于在資料庫中提供盡可能高的資料并發處理能力。事務處理修改資料時會擷取 行級鎖，而不是塊級或表級鎖。修改對象（如表移動）時會擷取對象級鎖，而不是整個數 據庫鎖或方案鎖。 資料查詢不需要鎖，即使有人鎖定了資料，查詢也能成功進行（總是顯示原始的、根據 還原資訊重新構造的鎖定之前的值）。 如果多個事務處理需要鎖定同一資源，則第一個請求鎖的事務處理會獲得鎖。其它事務處 理将等待，直到第一個事務處理完成為止。排隊機制是自動進行的，不需要管理者幹預。 事務處理完成（即發出COMMIT 或ROLLBACK ）時，将釋放所有鎖。如果事務處理失敗， 同一背景程序會自動回退失敗的事務處理所進行的所有更改，然後釋放失敗事務處理持有 的所有鎖。
- 資料并發處理
預設情況下，鎖定機制采用 細粒度行級鎖定模式。不同的事務處理可更新同一表内不同的 行，彼此互不幹擾。 盡管預設模式是行級鎖定，但Oracle DB 也允許根據需要在更進階别執行手動鎖定： SQL> LOCK TABLE employees IN EXCLUSIVE MODE; Table(s) Locked. 使用以上語句時，其他任何嘗試更新鎖定表中行的事務處理都必須等待，直到發出鎖定 請求的事務處理完成為止。EXCLUSIVE 是最嚴格的鎖模式。下面列出了其它一些鎖模式： • ROW SHARE：允許對鎖定的表進行并發通路，但禁止在會話中鎖定整個表進行獨占 通路。 • ROW EXCLUSIVE ：與ROW SHARE 相同，但是同時禁止以SHARE模式鎖定。更新、 插入或删除資料時會自動擷取ROW EXCLUSIVE 鎖。ROW EXCLUSIVE 鎖允許多個 程序執行讀取，但隻允許一個程序執行寫入。 • SHARE：允許并發查詢，但禁止更新鎖定的表。需要有SHARE鎖才能建立表的索引， 建立時會自動請求該鎖。但是，建立聯機索引的操作在建立索引時需要有ROW SHARE鎖。 共享鎖允許多個程序進行讀取，但不允許執行寫入。删除或更新某個父表中的行， 并且其子表在該父表上具有外鍵限制條件時，也會以透明方式使用共享鎖。
• SHARE ROW EXCLUSIVE：用于查詢整個表，允許其他人查詢表中的行，但禁止 其他人在SHARE模式下鎖定表或更新行。 • EXCLUSIVE：允許查詢鎖定表，禁止對鎖定表執行任何其它活動。需要有 EXCLUSIVE 鎖才能删除表。 與任何鎖定請求一樣，手動鎖定語句會一直等待，直到已經持有鎖（或先前請求鎖定） 的所有會話釋放鎖為止。LOCK 指令可接受用于控制等待行為的特殊參數NOWAIT 。 NOWAIT 會立即将控制權交給你，即使指定的表已經被另一會話鎖定： SQL> LOCK TABLE hr.employees IN SHARE MODE NOWAIT; LOCK TABLE hr.employees IN SHARE MODE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified 通常不必手動鎖定對象。自動鎖定機制提供了大多數應用程式所需的資料并發處理能力。 Oracle 建議盡量不要使用手動鎖定，特别是在開發應用程式時。使用不必要的高鎖定級别 時，經常會出現嚴重的性能問題。
- DML 鎖
每個DML 事務處理必須獲得兩個鎖： • 針對正在更新的一行或多行的EXCLUSIVE 行鎖 • 針對包含這些行的表的ROW EXCLUSIVE (RX) 模式 下的表鎖(TM)。 這可避免在進 行更改時另一會話鎖定整個表（可能會删除或截斷表）。這種模式也稱為子排它表 鎖(SX)。 對表執行ROW EXCLUSIVE 鎖定時，會禁止DDL 指令在未送出的事務處理進行到一半時 更改字典中繼資料。這樣便可在事務處理的有效期内保持字典完整性和讀取一緻性。
- 入隊機制
入隊機制用于跟蹤： • 等待鎖的會話 • 請求的鎖模式 • 會話請求鎖的順序
鎖定請求自動排隊。隻要持有某個鎖的事務處理一完成，隊列中的下一個會話就接收該鎖。 入隊機制會跟蹤請求鎖的順序及請求的鎖模式。 已經持有鎖的會話可請求轉換鎖，而不必排到隊尾。例如，假定某個會話對表持有SHARE 鎖。該會話可以請求将SHARE鎖轉換為EXCLUSIVE 鎖。如果沒有其它事務處理已經對 表持有EXCLUSIVE 或SHARE鎖，則持有SHARE鎖的會話就會被授予EXCLUSIVE 鎖， 而不必重新在隊列中等待。 注：等待入隊的程序分為兩類：沒有共享所有權的等待程序，以及有共享所有權、但沒有 選擇更新鎖級别的等待程序。第二類等待程序稱為轉換程序，這類程序的優先級始終高于 正常等待程序，即使其等待時間較短。
- 鎖沖突
鎖沖突經常發生，但通常會随着時間流逝通過入隊機制得到解決。隻有極少數情況下，鎖 沖突可能需要管理者幹預。如上圖所示，事務處理2 在9:00:00 擷取了對某一行的鎖且 忘記了送出，進而留下了鎖。事務處理1 在9:00:05 嘗試更新整個表，是以需要鎖定所有 行。但事務處理2 會阻塞事務處理1，直到16:30:01 事務處理2 送出為止。 這種情況下，使用者要嘗試執行事務處理1，就一定要與管理者聯系以獲得幫助，DBA 必須 檢測沖突并解決沖突。
- 鎖沖突的可能原因
• 未送出更改 • 長時間運作事務處理 • 不必要的高鎖定級别
鎖沖突的最常見原因是未送出更改，但還存在其它一些可能原因： • 長時間運作事務處理：許多應用程式使用批處理來執行批量更新。這些批作業通常 會安排在沒有使用者活動或者使用者活動少時執行，但是，有些情況下，批作業在使用者 活動少的期間内沒有完成或要占用過長的時間來運作。同時執行事務處理和批處理 時通常會發生鎖沖突。 • 不必要的高鎖定級别：并不是所有資料庫都支援行級鎖定（Oracle 在1988 年的發行 版6 中添加了對行級鎖定的支援）。某些資料庫仍然在頁級或表級上進行鎖定。開 發人員在編寫要在許多不同資料庫上運作的應用程式時，會人為地使用高鎖定級别， 以便使Oracle DB 與功能較少的資料庫系統的操作方式相同。如果開發人員不熟悉 Oracle，有時也會以高于Oracle DB 要求的鎖定級别編寫代碼，其實這是不必要的。
- 檢測鎖沖突
在“Performance （性能）”頁上選擇“Blocking Sessions （阻塞會話）”。
單擊“Session ID （會話ID ）”連結，檢視關于鎖定會話的 資訊，包括實際SQL 語句。
使用Enterprise Manager 中的“Blocking Sessions（阻塞會話）”頁可找出鎖沖突。有沖突 的鎖定請求以分層布局的形式顯示，其中持有鎖的會話位于頂部，下面是排隊請求鎖的所 有會話。 對于沖突中涉及的每個會話，會顯示使用者名、會話ID 和會話已等待的秒數。選擇會話 ID 可檢視會話目前正在執行或請求的實際SQL 語句。 自動資料庫診斷螢幕(ADDM) 還會自動檢測鎖沖突，并且會就低效的鎖定趨勢提出建議。
- 解決鎖沖突
為了解決鎖沖突，應該： • 送出或回退持有鎖的會話 • 終止持有鎖的會話（在緊急情況下）
要解決鎖沖突，持有鎖的會話必須釋放鎖。讓會話釋放鎖的最好方式是與使用者聯系，要求 使用者完成事務處理。 緊急情況下，管理者可以通過單擊“Kill Session （終止會話）”按鈕來終止持有鎖的會話。 ALTER SYSTEM KILL SESSION '130,651' IMMEDIATE
請記住，終止會話後，目前事務進行中的所有工作都會丢失（回退）。會話被終止的使用者 必須再次登入，然後重做被終止的會話自上次送出以來所做的所有工作。 如果使用者的會話已終止，使用者下次嘗試發出SQL 語句時會收到以下錯誤： ORA-03135: connection lost contact
[email protected]> update emp set sal=sal+100 where empno=7369; ERROR: ORA-03114: not connected to ORACLE
update emp set sal=sal+100 where empno=7369 * ERROR at line 1: ORA-03135: connection lost contact Process ID: 24763 Session ID: 130 Serial number: 651 注：如果會話出現空閑逾時，PMON 會話檢測程式會自動終止會話，這可以使用概要檔案 或資料總管來完成。
- 使用SQL 解決鎖沖突
可以使用SQL 語句來确定阻塞會話并終止該會話。
[email protected]> select SID, SERIAL#, USERNAME from V$SESSION where SID in (select BLOCKING_SESSION from V$SESSION);
SID SERIAL# USERNAME ---------- ---------- ------------------------------ 96 675 SCOTT
[email protected]> alter system kill session '96,675' immediate;
System altered.
與在Enterprise Manager 中執行的大多數其它任務一樣，會話操作也可以通過發出SQL 語 句來完成。V$SESSION 表包含所有已連接配接會話的詳細資訊。BLOCKING_SESSION 中的 值是阻塞會話的會話ID。如果查詢SID 和SERIAL#（其中SID 與阻塞會話ID 相比對）， 就會得到執行kill session操作所需的資訊。 注：可以使用資料庫資料總管自動登出阻塞其它會話的空閑會話。
- 死鎖
死鎖是鎖沖突的一種特殊情況。兩個或更多會話等待已被其中另一會話鎖定的資料時，就 會發生死鎖。因為每個會話都在等待另一個會話釋放鎖，是以任何一個會話都不能完成事 務處理，也就不能解決沖突。 Oracle DB 會自動檢測死鎖并終止發生錯誤的語句。面對這種錯誤的适當做法是執行送出 或回退，這樣做會釋放該會話中的其它所有鎖，以便其它會話可繼續完成其事務處理。 在示例中，事務處理1 必須送出或回退，才能更正檢測到的死鎖錯誤。如果執行提 交，則必須重新送出第二次更新才能完成事務處理。如果執行回退，則必須同時重新送出 這兩個語句才能完成事務處理。