关于oracle锁的笔记

是看TOM的oracle10g编程艺术时边看边写的,写下来提醒一下自个儿

1.什么是锁?
  锁机制用于对于共享资源的并发访问,在数据库中还提供数据的完整性和一致性.
2.丢失更新问题?
  悲观锁定
  乐观锁定

对于悲观锁定,利用for update nowait来防止其它的用户来更新锁定的数据,这时在for update nowait中的where条件与另一用户update的语句的where 条件应该没有被更改,否则仍会更新记录,如下:
第一种情况:
用户1-SQLPLUS
SQL>  select empno,ename,sal
  2   from emp
  3   where empno=:empno
  4   and ename=:ename
  5   and sal=:sal
  6   for update nowait
  7  /

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1400

SQL> update emp set sal=1300 where empno=7934;

已更新 1 行。

SQL> commit; --当提交后,用户2的操作也会继续执行,否则一直在等待.

提交完成。

SQL>
用户2-SQLPLUS
SQL> update emp set sal=1350 where empno=7934 and ename='MILLER' and sal=1400
  2  /

已更新0行。
第二种情况
用户1-SQLPLUS
SQL>  select empno,ename,sal
  2   from emp
  3   where empno=:empno
  4   and ename=:ename
  5   for update nowait;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7934 MILLER           1300

SQL> commit;
用户2-SQLPLUS
SQL> update emp set sal=1350 where empno=7934 and ename='MILLER' and sal=1300;

已更新0行。


乐观锁定:三种方法
1.采用一个时间戳即版本列
SQL>  create table detp1
  2   (deptno number(2),
  3   dname varchar2(14),
  4   loc varchar2(13),
  5   last_mod timestamp with time zone
  6   default systimestamp
  7   not null,
  8   constraint dept1_pk primary key(deptno)
  9  )
 10  /

SQL> variable deptno number
SQL> variable dname varchar2(14)
SQL> variable loc varchar2(13)
SQL> variable last_mod varchar2(50)
SQL> begin
  2    :deptno:=10;
  3    select dname,loc,last_mod
  4    into :dname,:loc,:last_mod
  5    from detp1
  6    where deptno=:deptno;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select :deptno dno,:dname dname,:loc loc,:last_mod lm
  2  from dual;

       DNO DNAME                            LOC                              LM
---------- -------------------------------- -------------------------------- -----------------------
        10 ACCOUNTING                       NEW YORK                         10-SEP-08 10.53.51.406000 AM +08:00

SQL> update detp1
  2  set dname=initcap(:dname),
  3  last_mod=systimestamp
  4  where deptno=:deptno
  5  and last_mod=to_timestamp_tz(:last_mod);

1 row updated.

SQL>  update detp1
  2   set dname=initcap(:dname),
  3   last_mod=systimestamp
  4   where deptno=:deptno
  5   and last_mod=to_timestamp_tz(:last_mod);

0 rows updated.
2.使用校验和或散列函数
需要注意，单向散列函数只能在一个方向上应用。从输入数据计算散列值很容易，但是要生成能散列为某个特定值的数据却很难

3.利用ORA_ROWSCN来实现

一个数据块上的ORA_ROWSCN可能是相同的.
SQL>  create table dept1
  2   (deptno,dname,loc,data,
  3    constraint dept1_pk primary key(deptno)
  4   )
  5   as
  6   select deptno,dname,loc,rpad('*',3500,'*')
  7   from dept;

Table created.
以上可以看来,这些数据都在不同的块上,但ora_rowscn却相同
SQL> select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn
  4  from dept1;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING           6484   12189086
        20 RESEARCH             6484   12189086
        30 SALES                6485   12189086
        40 OPERATIONS           6485   12189086

下面更新一个块上的数据,则此块上的其它数据的ora_rowscn也是变化的
SQL> update dept1 set dname=lower(dname)
  2  where deptno=10;

1 row updated.

SQL> commit;

Commit complete.

SQL> select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn
  4  from dept1;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting           6484   12189236
        20 RESEARCH             6484   12189236
        30 SALES                6485   12189086
        40 OPERATIONS           6485   12189086

启用属性rowdependencies.
SQL> create table dept1
  2  (deptno,dname,loc,data,
  3  constraint dept1_pk primary key(deptno)
  4  )
  5  rowdependencies
  6  as
  7  select deptno,dname,loc,rpad('*',3500,'*')
  8  from dept;

Table created.
SQL> select deptno,dname,
  2  dbms_rowid.rowid_block_number(rowid) blockno,
  3  ora_rowscn
  4  from dept1;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 ACCOUNTING           6500   12189466
        20 RESEARCH             6500   12189466
        30 SALES                6501   12189466
        40 OPERATIONS           6501   12189466

SQL> update dept1 set dname=lower(dname) where deptno=10;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select deptno,dname,
  2   dbms_rowid.rowid_block_number(rowid) blockno,
  3   ora_rowscn
  4   from dept1;

    DEPTNO DNAME             BLOCKNO ORA_ROWSCN
---------- -------------- ---------- ----------
        10 accounting           6500   12189534
        20 RESEARCH             6500   12189466
        30 SALES                6501   12189466
        40 OPERATIONS           6501   12189466
函数:SCN_TO_TIMESTAMP

SQL> select scn_to_timestamp(ora_rowscn) ts from dept1;

TS
---------------------------------------------------------------
10-SEP-08 03.25.50.000000000 PM
10-SEP-08 03.23.59.000000000 PM
10-SEP-08 03.23.59.000000000 PM
10-SEP-08 03.23.59.000000000 PM

3.阻塞
数据库中有5条常见的DML语句可能会阻塞:
insert,update,delete,merge,select for update

对于insert型阻塞,可以利用一个触发器来防止长时间的等待,如下:
SQL> create table demo(x int primary key);

Table created.

触发器脚本:
create or replace trigger demo_bifer
before insert on demo
for each row
declare
  l_lock_id number;
  resource_busy exception;
  pragma exception_init(resource_busy,-54);
begin
  l_lock_id := dbms_utility.get_hash_value(to_char(:new.x),0,1024);
  if (dbms_lock.request(id=>l_lock_id,
                        lockmode=>dbms_lock.x_mode,
                        timeout=>0,
                        release_on_commit=>TRUE)<>0)
  then
    raise resource_busy;
  end if;
end;

4.死锁 deadlock ----该死的死锁
SQL> update a set b=b where id=1;
update a set b=b where id=1
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Oracle认为死锁很少见,而且由于如此少见,所以每次出现死锁时它都会在服务器上创建一个跟踪文件.
文件内容如下:
*** 2008-09-10 16:28:01.921
*** ACTION NAME:() 2008-09-10 16:28:01.906
*** MODULE NAME:(SQL*Plus) 2008-09-10 16:28:01.906
*** SERVICE NAME:(SYS$USERS) 2008-09-10 16:28:01.906
*** SESSION ID:(142.9385) 2008-09-10 16:28:01.906
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update a set b=b where id=1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
            ...................................

导致死锁的原因:
(1)外键未加索引
(2)位图的争用

----------------查找外键未加索引的表--------------
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
)
group by table_name,constraint_name,cname1,cname2,cname3,cname4,cname5,cname6,cname7,cname8
/

TABLE_NAME                     CONSTRAINT_NAME COLUMNS
------------------------------ --------------- -----------------------------
TEACHER                        FK_LESSONNAME   LESSON_NAME
EMP                            EMP_FK_DEPT     DEPTNO
EMP                            EMP_FK_EMP      MGR
C                              SYS_C007744     X

锁类型
1.DML锁
2.DDL锁
3.内部锁和闩

SQL> update dept1 set deptno=deptno+10;

已更新7行。
查询 'TX'事务锁的语句
 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

USERNAME                              SID        RBS       SLOT        SEQ      LMODE    REQUEST
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
FTTEST                                125          4          5       5394          6          0

通过自连接来查询
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

TM锁
SQL> create table t1(x int);

表已创建。

SQL> create table t2(x int);

表已创建。

SQL> insert into t1 values(1);

已创建 1 行。

SQL> insert into t2 values(1);

已创建 1 行。

SQL> select
  2  (select username from v$session
  3  where sid=v$lock.sid) username,
  4  sid,id1,id2,lmode,request,block,v$lock.type
  5  from v$lock
  6  where sid=(select sid from v$mystat where rownum=1)
  7  /

USERNAME                              SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- --
FTTEST                                125      57698          0          3          0          0 TM
FTTEST                                125      57699          0          3          0          0 TM
FTTEST                                125     196652       7072          6          0          0 TX

TX锁是事务锁,如果开启一个事务则会创建一个TX锁,直到commit或rollback才会释放这个锁
而TM锁(DML锁)则会在一个事务中有多个,而且ID1会和object_id相同
select object_name,object_id from user_objects
where object_name in ('T1','T2');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67668/viewspace-448701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67668/viewspace-448701/