Oracle Lock 监视和检测锁争用

Oracle  锁定机制

  • 自动管理
  • 高水平的数据并发性
    • 用于 DML 事务处理的行级锁
    • 查询不需要锁
  • 改变中的数据一致性级别
  • 专用锁模式和共享锁模式
  • 锁要一直保持到提交或回退发生之前

 

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)
    • 不允许 DML 或共享模式
    • 隐式地用于引用完整性
  • 专用 (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;

 

阻塞其它进程的任何进程,很可能正持有用户应用程序获得的锁。用户应用程序获取的锁为

  • 表锁 (TM)
  • 行级锁 (TX)

这里未列出的其它锁为系统锁 它们仅被短暂地持有。

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 目录下的一个跟踪文件中 。 为了确定应用
程序是否存在问题, 为了确定应用程序是否存在问题。 为了确定应用程序是否存在问题。

 

 

Comment

*

沪ICP备14014813号

沪公网安备 31010802001379号