• 描述锁定机制以及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 必须提交或回退，才能更正检测到的死锁错误。如果执行提 交，则必须重新提交第二次更新才能完成事务处理。如果执行回退，则必须同时重新提交 这两个语句才能完成事务处理。