Oracle 9i/10g/11g编程艺术（2e）学习笔记【第6章】

第六章 锁和闩

1、锁定问题

（1）丢失更新：使用Oracle某种锁定策略（悲观锁定/乐观锁定）可以避免丢失更新。

（2）Oracle提供两种锁定策略：悲观锁定、乐观锁定。

（3）悲观锁定：一般使用for update nowait实现。如SQLPLUS命令上执行以下命令：

select empno, ename, sal from emp where deptno = 10;
variable empno number
variable ename varchar2(20)
variable sal number
exec :empno := 7934; :ename := 'MILLER'; :sal := 1300;

执行完上面命令后，会根据屏幕上输入的数据锁定一行，不允许其他会话更新。这种方法称为悲观锁定（pessimistic locking）。在试图更新之前我们就把行锁住了，因为我们很悲观，对于这一行能不能保持未改变很是怀疑。

（4）乐观锁定

第二种方法称为乐观锁定（optimistic locking），即把所有锁定都延迟到即将执行更新之前才做。我们很乐观，认为数据不会被其他用户修改，因此会等到最后一刻才去看我们的想法对不对。

两种方式实现乐观锁定并发控制：使用版本列、散列/校验和。

（5）死锁

只需要对外键加索引，一般都能避免死锁。使用以下sql查看是否存在未加索引的外键：

select table_name, constraint_name,
     cname1 || nvl2(cname2,','||cname2,null) ||
     nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
     nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
     nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
            columns
  from ( select b.table_name,
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
           from (select substr(table_name,1,30) table_name,
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                   from user_cons_columns ) a,
                user_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'
          group by b.table_name, b.constraint_name
       ) cons
 where col_cnt > ALL
         ( select count(*)
             from user_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4,
                                    cname5, cname6, cname7, cname8 )
              and i.column_position <= cons.col_cnt
            group by i.index_name
         );

2、锁类型

（1）Oracle主要有3类锁。

• DML锁（DML lock）：DML代表数据操纵语言。一般来讲，这表示select、insert、update、merge、delete语句。DML锁机制允许并发执行数据修改。例如，DML锁可能是特定数据行上的锁，或者是锁定表中所有行的表级锁。
• DDL锁（DDL lock）：DDL代表数据定义语言，如create和alter语句等。DDL锁可以保护对象结构定义。
• 内部锁和闩：Oracle使用这些锁来保护其内部数据结构。闩（latch）是Oracle采用的一种轻量级的低级串行化设备，功能类似于锁。

（2）DML锁：用于确保一次只有一个人能修改某一行，而且你正在处理一个表时别人不能删除这个表。

• TX锁：事务发起第一个修改时会得到TX锁（事务锁），而且还会一直持有这个锁，直到事务执行提交（commit）或回滚（rollback）。事务中修改或通过select for update选择的每一行都会“指向”该事务的一个相关TX锁。几个有用的V$_视图：

--V$_视图
select l.lmode,l.request, l.* from v$lock l;
select * from v$session;
select XIDUSN, XIDSLOT, XIDSQN from v$transaction;
--查询锁情况，rbs、slot、seq分别与v$transaction中的XIDUSN, XIDSLOT, XIDSQN对应
select username,
       v$lock.sid,
       trunc(id1/power(2,16)) rbs,
       bitand(id1,to_number('ffff','xxxx'))+0 slot,
       id2 seq,
       lmode,
       request
from v$lock, v$session
where v$lock.type = 'TX'
  and v$lock.sid = v$session.sid
  and v$session.username = USER;
--查询阻塞会话
select
      (select username from v$session where sid=a.sid) blocker,
       a.sid,
      ' is blocking ',
       (select username from v$session where sid=b.sid) blockee,
           b.sid
  from v$lock a, v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;

上述脚本中，v$lock表的lmode字段值如为6表示一个排它锁，request为0则意味着你没有发出请求，也就是说，你拥有这个锁。

• TM（DML Enqueue）锁：TM锁用于确保在修改表的内容时，表的结构不会改变。例如，如果已经更新了一个表，会得到这个表的一个TM锁。这会防止另一个用户在该表上执行drop或alter命令。如果有表的一个TM锁，而另一个用户试图在这个表上执行DDL，他就会得到以下错误信息：

（3）DDL锁

（4）闩

闩（latch）是轻量级的串行化设备，用于协调对共享数据结构、对象和文件的多用户访问。闩是一种锁，设计为只保持极短的一段时间（例如，修改一个内存中数据结构所需的时间）。闩用于保护某些内存结构，如数据库块缓冲区缓存或共享池中的库缓存。为减少闩，建议使用绑定变量。对于同样的sql，如使用绑定变量，可以减少CPU占用时间，减少执行时间，随着增加更多的用户（并发），这种现象越发明显。