OCP认证考试指南（17）：处理锁定

1、共享锁与排他锁

Oracle数据库中锁定的标准级别保证了最大可能的并发级别。就是说，如果某个会话正在更新一条记录，那么只有这条记录会被锁定。此外，锁定这条记录只是为了防止其他会话对其进行更新，其他会话可以随时执行读取操作。只有在使用COMMIT或ROLLBACK命令结束事务后，锁定才会被解除。这种锁定是“排他（exclusive）”锁：在指定记录上请求排他锁的第一个会话会得到这个锁定，其他请求对该记录进行写访问的会话则必须等待。

对于一条记录或一个完整表上的一个排他锁来说，每次只能有一个会话可以获得这个排他锁，不过许多会话可以同时获得相同对象上的“共享（shared）”锁。在一个表上放置共享锁的目的是为了防止另一个会话获得这个表上的排他锁。

2、DML锁与DDL锁

所有DML语句都至少需要两种锁定：受影响记录上的排他锁，以及包含受影响记录的表上的共享锁。排他锁能够防止其他会话干预指定的记录，而共享锁则能够阻止其他会话使用DDL语句修改表的定义。

执行DDL命令需要使用所涉及对象上的排他锁。只有在针对指定表的所有DML事务结束，且记录上的排他锁以及表上的共享锁都被解锁后，才可以获得执行DDL命令所需的排他锁。

^?[Copy to clipboard]View Code SQL

######################################### 第一个用户连接，建表修改记录后，不提交######################################## SQL> conn system/oracleConnected.SQL> create table t1 (c1 number); Table created. SQL> insert into t1 values (1); 1 row created. SQL> commit; Commit complete. SQL> update t1 set c1 = 2 where c1 = 1; 1 row updated. ######################################### 第二个用户连接，给表增加列，失败！# 第一个用户的DDL语句放了排他锁与第二用户DML语句放置共享锁冲突# 相似的情况下，DDL语句会被挂起######################################## $ sqlplus system/oracle SQL*Plus: Release 10.2.0.1.0 - Production on Thu Oct 9 20:15:29 2008 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options SQL> alter table t1 add (c2 date);alter table t1 add (c2 date)*ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified ######################################### 第一个用户提交######################################## SQL> commit; Commit complete. ######################################### 第二个用户再执行增加列SQL，成功######################################## SQL> alter table t1 add (c2 date); Table altered. ######################################### 再来，第一个用户锁定整个表######################################## SQL> lock table t1 in exclusive mode; Table(s) Locked. ######################################### 第二个用户增加记录，会被挂起######################################## SQL> insert into t1 values (2, sysdate); ######################################### 第一个用户COMMIT或ROLLBACK，表解锁######################################## SQL> commit; Commit complete. ######################################### 第二个用户执行成功######################################## 1 row created. SQL> commit; Commit complete.

3、排除机制

请求锁定需要排除。如果某个会话请求一个锁定，但由于其他会话已经锁定指定记录或对象，那这个会话将会等待。可能有多个会话都在等待的情况下，Oracle会跟踪这些会话的请求顺序，使用锁定的会话解除锁定时，下一个会话又将在记录或对象上放置锁定。这种机制称为“排队（enqueue）”机制。

如果不希望排队，避免排除的唯一方式是使用SELECT …FOR UPDATE命令的WAIT或NOWAIT子句。

^?[Copy to clipboard]View Code SQL

######################################### 第一个用户锁定T1表######################################## SQL> select * from t1 for update;         C1 C2---------- ---------         2         2 09-OCT-08 ######################################### 第二个用户NOWAIT关键字锁定T1表# 再次锁定T1表，指定10秒等待时间######################################## SQL> select * from t1 for update nowait;select * from t1 for update nowait              *ERROR at line 1:ORA-00054: resource busy and acquire with NOWAIT specified SQL> select * from t1 for update wait 10;select * from t1 for update wait 10              *ERROR at line 1:ORA-30006: resource busy; acquire with WAIT timeout expired ######################################### 第二个用户再次锁定T1表，指定10秒等待时间######################################## SQL> select * from t1 for update wait 10; ######################################### 在10秒的等待时间内，第一用户解锁######################################## SQL> commit; Commit complete. ######################################### 第二用户马上查询成功########################################         C1 C2---------- ---------         2         2 09-OCT-08

4、锁定争用

当某个会话请求一条记录或一个对象上的锁定，但是由于其他会话已经获得该记录或对象上的排他锁而无法获得锁定时，这个会话将被挂起。这种现象称为锁定争用。锁定争用会导致数据库的性能急剧恶化。

4.1、锁定争用的原因

• 运行时间很长的事务会导致某些问题。
• 批量处理进程在被不恰当地设计为长事务时也会导致某些问题。
• 第三方用户进程产品会带来过高的锁定级别。

4.2、检测锁定争用

锁定争用是由许多同时访问相同数据的用户导致的。我们可以通过EM或ALTER SYSTEM KILL SESSION命令终止一个会话。

4.3、解决锁定争用

DBA能够通过终止过长时间拥有过多锁定的会话来解决锁定争用问题。

^?[Copy to clipboard]View Code SQL

######################################### 第一个用户锁定表######################################## SQL> select * from t1 for update;         C1 C2---------- ---------         2         2 09-OCT-08 ######################################### 第一个会话修改表######################################## SQL> update t1 set c2 = sysdate where c1 =2;

这时候会被挂起，我们用EM来查看，方法见上一节：检测锁定争用。

可以看到Session ID：134，Session Serial Number 301阻塞会话。把它KILL掉，就不会有争用了。

^?[Copy to clipboard]View Code SQL

######################################### 第三个会话登录######################################## SQL> alter system kill session '134, 301'; System altered. ######################################### 第二个会话执行成功######################################## 2 rows updated.

5、死锁

两个会话相互阻塞，这两个会话都被挂起，每个会话都在等待另一个会话释放其锁定。这种场景称为“死锁（deadlock）”。死锁问题是由Oracle数据库自动解决的。