Oracle 锁定机制
- 自动管理
- 高水平的数据并发性
- 改变中的数据一致性级别
- 专用锁模式和共享锁模式
- 锁要一直保持到提交或回退发生之前
Oracle 服务器自动管理锁定 Oracle 服务器的缺省锁定机制以最低的限制级别
锁定数据 以便在允许最大程度的数据并发性时 保证数据的一致性
数据并发性
根据设计 锁定允许高级别的数据并发性 也就是说 多个用户可以同时安全地访问相同的数据 数据并发性涉及两个级别的锁定 行级或不锁定
• 数据操纵语言 (DML) 锁定是行级锁定
示例
事务处理 1
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
事务处理 2
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24878;
1 row updated.
除非用户指定 否则查询不持有锁
示例
事务处理 1
SQL> UPDATE s_emp
2 SET salary = salary*1.1;
13120 rows updated.
事务处理 2
SQL> select salary
2 from s_emp
3 where id= 10;
SALARY
---------
1000
数据一致性
Oracle 服务器也提供不同级别的数据一致性 也就是说 即使其他用户正在更改数据 用户仍会看到数据的静态图
持续时间
一直持有锁 直到发生 COMMIT 或 ROLLBACK 或者一个事务处理终止为止 如果一个事务处理异常终止 则 PMON 清除锁
锁定模式
排它锁模式 防止相关的资源被其它事务处理共享 直到排它锁被释放为止示例 对于 DML 事务处理 排它锁被设置为行级
事务处理
1
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
事务处理
2
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
Transaction 2 waits.
在共享锁模式 下 几个事务处理可以在同一资源上获取共享锁
示例 对于 DML 事务处理 共享锁被设置为表级
事务处理
1
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
事务处理
2
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24878;
1 row updated.
两个事务处理更新同一个表中的行
锁的持续时间
事务处理一直持有锁 直到它们提交或回退为止
示例
事务处理
1
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
SQL> commit;
Commit complete.
事务处理
2
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
Transaction 2 waits.
1 row updated.
事务处理 1 一提交 事务处理 2 就可以更新行 因为事务处理获取了所请求的锁。
锁的类型
DML 锁
多个用户并发地访问数据时 可以使用 DML 锁以保证数据的完整性 锁防止由同时的 冲突的 DML 和 DDL 操作引起的破坏性干扰 DML 锁提供两个级别的锁
表级锁 TM 类型 为任何修改表的 DML 事务处理设置 INSERT UPDATE DELETE SELECT...FOR UPDATE 或 LOCK TABLE 表锁防止 与该事务处理冲突的 DDL 操作
示例
事务处理
1
SQL> UPDATE s_emp
2 SET salary=salary*1.1;
13120 rows updated.
事务处理
2
SQL> DROP TABLE s_emp;
ERROR at line 1:
ORA-00054:resource busy and
acquire with NOWAIT specified
为 INSERT UPDATE DELETE 或 SELECT...FOR UPDATE 更改的每个行 自动地获得行级 锁 TX 类型 行级锁定确保没有其他用户可以同时更改相同的行 因此 不存在用户修改这样的行的危险 其他用户正在修改
该行且仍未提交
示例
事务处理
1
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
事务处理
2
SQL> update scott.s_emp
2 set salary=salary*1.1
3 where id= 24877;
Transaction 2 waits.
DDL 锁
在一个正在进行的 DDL 操作执行或引用方案对象的同时， DDL 锁保护该方案对象的定义。 Oracle 服务器自动地获取 DDL 锁，以防止任何对其它 DDL 操作的破坏性干涉，这些操作可能修改或引用同一方案对象。
DML 锁
- 一个 DML 事务处理至少需要两个锁:
- 这种入队机制保持跟踪:
DML 事务处理至少获取两个锁
有两种锁结构用于 DML 语句 INSERT UPDATE DELETE SELECT...FOR UPDATE
事务处理获取在表上的共享锁 无论是什么类型的共享锁模式 ，都称为
TM 锁。
事务处理在其正在更改的行上获取一个排它锁， 称为
TX 锁 。每个行会使该行标题中的一个锁字节开启 该标题指向由事务处理使用的， 感兴趣的事务处理列表 (ITL) 插槽。 行级锁定模式只能是排它的
入队机制
Oracle 服务器将所有的锁作为入队 来维护 入队机制可以跟踪
• 等待由其他用户持有的锁的用户
• 这些用户需要的锁定模式
• 用户请求锁的顺序
如果三个用户同时希望更新同一个行 他们都会获得共享表锁 但是只有一个
用户,即第一个用户,可以获得行锁 .表锁定机制会跟踪谁持有行锁,以及谁在等待行锁。
通过增加参数 DML_LOCKS 和 ENQUEUE_RESOURCES，可增加可用于例程的总锁数。 这在并行服务器配置时是必要的
表锁定模式
自动获取:
• 行专用
(RX): INSERT, UPDATE, DELETE
• 行共享
(RS): SELECT...FOR UPDATE
自动表锁定模式自动表锁定模式通常看到由 DML 事务处理持有的两个 TM 表锁定模式， RX 和 RS 。这些是由Oracle 服务器自动分配给 DML 事务处理的表锁定模式。
表锁定模式的限制， 决定了在同一表中获得和持有其它表锁的模式。
专用行 (RX)
- 允许其它事务处理并发地查询、插入、 更新、 删除或锁定在相同表中的其他行。
- 阻止其它事务处理手动地锁定表 以便进行独占式读写。
示例
事务处理
1 持有
RX 表锁
SQL> update s_emp
2 set salary=salary*1.1
3 where id= 24877;
1 row updated.
事务处理 2 持有 RX 表锁
SQL> update s_emp
2 set salary=salary*1.1
3 where id= 24878;
1 row updated
Row Share (RS)
可以使用 SELECT...FOR UPDATE 语句 在查询期间选择锁定行 这阻止其它 事务处理手动地锁定表 以便进行独占式写入访问
示例
Transaction 1 (RS table lock held)
SQL> select id,salary
2 from s_emp
3 where id=24877
4 for update;
ID SALARY
--------- ---------
24877 1100
SQL> commit;
Commit complete.
Transaction 2 (X table lock requested)
SQL> lock table s_emp
2 in exclusive mode;
Transaction 2 waits.
Table(s) Locked.
手动锁定模式
通过显式 LOCK TABLE 命令 手动地分配三个其它表锁定模式
SQL> LOCK TABLE s_emp IN exclusive MODE;
Table(s) Locked.
使用显式锁定往往有较好的应用理由 但如果遇到锁争用 则可能需要与开发人员联系
后台开发人员 (而非 Oracle 开发人员),有时使用的锁定级别过高 这是不必要的。
共享 (S)
• 仅允许其它事务处理查询表 (SELECT ...FOR UPDATE)
• 防止对表的任何修改
隐式获得 共享 锁的 SQL 语句受到引用完整性约束条件的限制 在子表中
如果没有对外键列的索引 那么
• 父级上的任何 DELETE 都持有子级上的 共享 锁
• 父级引用列上的任何 UPDATE 都持有子级的 共享 锁
该行为的原因是，当子行持有在任何相关的表中时，其父行决不能被删除 (也决不能更新父行的主键 )锁定子表是为了防止破坏该规则的更新和插入。
示例
事务处理
1 在 （
s_dept 上持有的
RX 表锁 在
s_emp 上持有的
S 表锁）
SQL> delete from s_dept
2 where id=60;
1 row deleted.
SQL> commit;
Commit complete.
事务处理
2 (请求
RX 表锁）
SQL> update s_emp
2 set salary=salary*1.1
3 where id=24877;
Transaction 2 waits.
1 row updated.
为了避免该行为 ，请在引用 （子） 表中设置外键列索引。
如果在子表中索引外键， 则通过锁定索引中被更改的值 ，Oracle 服务器可以防止对子行的更改
示例
事务处理
1 （持有
RX 表锁)
SQL> delete from s_dept
2 where id=60;
1 row deleted.
事务处理
2 (持有
RX 表锁)
SQL> create index i on s_emp
2 (dept_id);
Index created.
SQL> update s_emp
2 set salary=salary*1.1
3 where id=24877;
1 row updated.
表锁定模式
- 在 LOCK 语句中手动获取:
- 共享行专用 (SRX)
- 专用 (X)
共享行排它
(SRX)
这种锁定模式防止其它语句获取 DML 或手动共享锁。
再次隐式地获得 共享行排它 锁的 SQL 语句涉及引用完整性 。在下述情况下，当在父表中执行 DELETE 时 需要子级的 共享行排它 锁定
- 外键约束条件包含 ON DELETE CASCADE
- 在子表中 没有对外键列的索引
示例
事务处理
1 （持有
s_dept 上的
RX 表锁 持有
s_emp 上的
SRX 表锁）
SQL> delete from s_dept
2 where id=60;
1 row deleted.
SQL> commit;
Commit complete.
事务处理
2 （请求
RX 表锁）
SQL> update s_emp
2 set salary=salary*1.1
3 where id=24877;
Transaction 2 waits.
1 row updated.
同样 该解决方案用于在子表中索引外键列。
排它
(X)
这是表锁的最高级别 因此是限制最多的模式 排它锁：
- 只允许其它事务处理查询表
- 防止任何类型的 DML 语句和任何手动锁定模式
示例
事务处理
1 （持有
X 表锁）
SQL> lock table s_dept in exclusive
mode;
Table(s) Locked.
事务处理
2 （请求
RS 表锁）
SQL> select * from s_dept
2 for update;
Transaction 2 waits.
块中的行级锁定
事务处理提交时 ，不清除块中的行级锁定的锁定信息 ，而是在下一个查询读取块时清除 。这称为延迟的块清除。
执行清除的查询 ，必须检查事务处理的状态 ，以及事务处理表中的系统更改序号 (SCN) 事务处理表持有在回退段标题中。
在块中 Oracle ，服务器在块标题中为每个活动的事务处理持有标识符 。在行级 锁字节为包含事务处理的插槽存储一个标识符。
DDL 锁
- 专用 DDL 锁:
- DROP TABLE 语句
- ALTER TABLE 语句
- 共享 DDL 锁:
- CREATE PROCEDURE 语句
- AUDIT 语句
- 可分开的分析锁: 使共享 SQL 区域无效
DDL 锁
不大可能看到 DDL 锁的争用，因为它们仅短暂地被持有，并且应在 NOWAIT模式中请求这种锁。共有三种类型的 DDL 锁。
排它
DDL 锁
一些 DDL 语句 ，如 CREATE、 ALTER 及 DROP ，必须在它们正在工作的对象上获取一个排它锁。
一些 DDL 语句 如 CREATE ALTER 及 DROP 必须在它们正在工作的对象上获取一个排它锁， 用户就无法在该表上获取排它锁，所以， 如果有用户在表上有未提交的事务处理， ALTER TABLE 语句就会
失败。
示例
事务处理
1
SQL> UPDATE s_emp
2 SET salary=salary*1.1;
3120 rows updated.
事务处理
2
SQL> ALTER TABLE s_emp
2 DISABLE primary key;
ORA-00054:resource busy and
acquire with NOWAIT specified
注 ：使用本地管理的表空间而不是字典管理表空间 ， 可以消除对 ST 空间事务处理 锁的争用， 因为在请求空间配置时， 本地管理的表空间不更新数据字典中的表。
共享 DDL 锁
共享
DDL 锁 不阻止类似的 DDL 语句或任何 DML 但阻止其他用户改变或删除引用的对象。
一些语句 如 GRANT 和 CREATE PACKAGE 需要在它们引用的对象上有共享 DDL 锁。
易碎的分析锁
易碎的分析锁 用于检查对象更改时语句是否应失效。
在库高速缓存中的一个语句或 PL/SQL 对象， 在库高速缓存中的一个语句或 PL/SQL 对象， 直到语句从共享池中超龄释放为止。
可以把该锁看作一个指针 。 可以把该锁看作一个指针 。
锁争用
Oracle 服务器锁花费不多但效率高 ， 多数站点没有锁定的问题。 如果锁导致争用， 经常是因为：
- 开发人员用不必要的高锁定级别编写代码
- 开发人员不必要的长的事务处理编写代码
- 用户在应提交更改时未提交
- 应用程序联合使用 Oracle 服务器和其它使用较高锁定级别的产品
V$LOCK 视图
V$LOCK 视图提供有关例程中当前持有的锁的信息
锁类型
ID1
TX 回退段编号和插槽编号
TM 正在被修改的表的 ID
示例 若要查找 V$LOCK 视图的某个特定 资源 ID 1 相应的表名称：
SQL> SELECT owner, object_id, object_name, object_type,
2 v$lock.type
3 FROM dba_objects, v$lock
4 WHERE object_id = v$lock.id1 and object_name =
table_name;
阻塞其它进程的任何进程，很可能正持有用户应用程序获得的锁。用户应用程序获取的锁为
这里未列出的其它锁为系统锁 它们仅被短暂地持有。
V$LOCKED_OBJECT 视图
锁类型
ID1
XIDUSN 回退段编号
OBJECT_ID 正被修改的对象的 ID
SESSION_ID 锁定对象的会话的 ID
ORACLE_USERNAME
LOCKED_MODE
示例 若要查找与 V$LOCKED_OBJECT 视图的某个特定 OBJECT_ID 相应的表名称:
SQL> select xidusn, object_id, session_id, locked_mode
2 from v$locked_object;
XIDUSN OBJECT_ID SESSION_ID LOCKED_MODE
--------- --------- ---------- -----------
3 2711 9 3
0 2711 7 3
SQL> select object_name
2 from dba_objects
3 where object_id = 2711;
OBJECT_NAME
-------------
S_EMP
如果 XIDUSN 的值为 0，相应的 SESSION_ID 正在请求和等待 SESSION_ID 持有的锁， 对于 SESSION_ID XIDUSN 有非 0 的值。
锁管理的原则解决争用
终止会话
如果一个用户持有由另一个用户所请求的锁 则可以：
- 与持有者联系并要求该用户提交或回退事务处理
- 作为最后的手段 终止该 Oracle 用户会话 这样将回退事务处理并释放锁
上述的任何监视方法都将给出用户的会话标识符。
可以使用以下语句终止用户会话：
The ALTER SYSTEM KILL SESSION SQL command :
SQL> select sid,serial#,username
2 from v$session
3 where type='USER';
SID SERIAL# USERNAME
--------- --------- ------------------------------
8 122 SYSTEM
10 23 SCOTT
SQL> alter system kill session '10,23';
System altered.
解决死锁
当两个或更多用户等待彼此锁定的数据时 会出现死锁。
Oracle 服务器会自动检测死锁 并通过回退检测到有死锁的语句来解决死锁。
假设 事务处理 1 的第二个更新检测到死锁 ， Oracle 服务器回退该语句并返回消息 。 尽管导致死锁的语句回退， 但事务处理并不回退 ， 您将收到 ORA-00060 错误 。 下一个操作应为回退剩余的事务处理。
技术注释
在事务处理明显地覆盖 Oracle 服务器的缺省锁定时， 会频繁导致死锁 。 一旦检测到 ， 一旦检
测到 。
跟踪文件
死锁情况记录在 USER_DUMP_DEST 目录下的一个跟踪文件中 。 为了确定应用
程序是否存在问题， 为了确定应用程序是否存在问题。 为了确定应用程序是否存在问题。