Oracle锁实验

lzf79

　　MASICONG@orcl> select sid from v$mystat where rownum=1;
　　SID
　　----------
　　35
　　MASICONG@orcl> select sid from v$mystat where rownum=1;
　　SID
　　----------
　　1
　　1.分别模拟insert，update和delete造成阻塞的示例，并对v$lock中的相应的信息进行说明，给出SQL演示。
　　MASICONG@orcl> create table test (id varchar(2) primary key);
　　Table created.
　　INsert阻塞
　　MASICONG@orcl> insert into test values (10);
　　1 row created.
　　MASICONG@orcl> insert into test values(10);  另一个用户提交同样的请求就会卡住
　　MASICONG@orcl>  select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　1 TX     131090        674          0          4          0
　　35 TM      74571          0          3          0          0
　　1 TM      74571          0          3          0          0
　　1 TX      65540        508          6          0          0
　　35 TX     131090        674          6          0          1
　　说明1会话需要一个4级锁，但是35已经加了一个6级的锁阻塞了会话。因为会话1已经插入了一条记录，所以又一个TX锁可以通过。
　　MASICONG@orcl> select object_name from dba_objects where object_id=74571;
　　OBJECT_NAME
　　--------------------------------------------------------------------------------
　　TEST
　　通过TM知道目前是在一个表上加的锁，所有通过ID可以查到具体操作的是哪个表。
　　MASICONG@orcl> commit;
　　Commit complete.
　　MASICONG@orcl> insert into test values(10); 当一个会话提交后，另一个会话才能访问
　　*
　　ERROR at line 1:
　　ORA-00001: unique constraint (MASICONG.SYS_C0011055) violated
　　Update阻塞
　　MASICONG@orcl> commit
　　2  ;
　　Commit complete.
　　MASICONG@orcl> select * from test;
　　ID
　　--
　　1
　　10

　　MASICONG@orcl> update test set>　　1 row updated.

　　MASICONG@orcl> update test set>　　MASICONG@orcl>  select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　1 TX     458775        552          0          6          0
　　1 TM      74571          0          3          0          0
　　35 TM      74571          0          3          0          0
　　35 TX     458775        552          6          0          1
　　显示出现了阻塞信息，会话在等待一个6级的锁
　　MASICONG@orcl> select sid,event from v$session_wait where sid in (1,35);
　　SID EVENT
　　---------- ----------------------------------------------------------------
　　1 enq: TX - row lock contention
　　35 SQL*Net message from client
　　上面标示1会话需要TX锁
　　DELETE操作

　　MASICONG@orcl> delete from test where>　　1 row deleted.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') ;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　1 TX     524289        908          0          6          0
　　35 TM      74571          0          3          0          0
　　1 TM      74571          0          3          0          0
　　35 TX     524289        908          6          0          1
　　说明1会话需要一个6级别的锁，但是被35会话的6级别的锁所阻塞。
　　2.模拟RI锁定导致阻塞的场景，并分析v$lock相应的锁定信息，给出SQL演示。
　　MASICONG@orcl> create table zhu (id number primary key);
　　Table created.
　　MASICONG@orcl> create table cong (id references zhu(id));
　　Table created.
　　MASICONG@orcl> insert into zhu values (1);
　　1 row created.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　35 TM      74573          0          3          0          0
　　35 TM      74575          0          3          0          0
　　35 TX     131087        676          6          0          0
　　这时候已经在主表和从表上都加了一个3级锁防止DDL操作，同时加了一个6级锁，防止DML操作。
　　MASICONG@orcl> insert into zhu values (1);  另一个会话也执行就会造成阻塞。
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　1 TM      74573          0          3          0          0
　　1 TM      74575          0          3          0          0
　　1 TX     262165        551          0          4          0
　　1 TX     589835        690          6          0          0
　　35 TM      74575          0          3          0          0
　　35 TM      74573          0          3          0          0
　　35 TX     262165        551          6          0          1
　　上面的实例说明有一行TX加了6级的锁没有阻塞，已经成功执行。主从表都加了3级的表锁。有一个因为要加一个4级锁而别6级锁阻塞，造成了等待。

　　MASICONG@orcl> update zhu set>　　1 row updated.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　35 TM      74573          0          3          0          0
　　35 TX     327699        721          6          0          0
　　当更新主表的记录时候，只对主表上加了TM锁和TX锁。

　　MASICONG@orcl> delete from zhu where>　　1 row deleted.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　35 TM      74573          0          3          0          0
　　35 TX     393248        700          6          0          0
　　当删除主表内容时，只对主表加上了TM锁和TX锁
　　MASICONG@orcl> insert into zhu  values (10);
　　1 row created.
　　MASICONG@orcl> insert into cong values (10);
　　1 row created.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　35 TM      74573          0          3          0          0
　　35 TM      74575          0          3          0          0
　　35 TX     393244        700          6          0          0
　　当从表插入的时候主表和从表都有3级的表锁还有一个6级的TX锁。

　　ASICONG@orcl> delete from cong where>　　1 row deleted.
　　MASICONG@orcl> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;
　　SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
　　---------- -- ---------- ---------- ---------- ---------- ----------
　　35 TM      74573          0          3          0          0
　　35 TM      74575          0          3          0          0
　　35 TX     262165        551          6          0          0
　　当从表删除时，主表也有一个3级的锁在上面。
　　5.给出一个导致死锁的SQL示例。
　　场景：当同一张表，有两个会话，一个23，一个35，当23会话对表进行插入操作，插入数据1，35会话对表进行操作，插入数据2。之后23由对数据插入2,35会话插入数据1，就会造成阻塞，最后产生死锁。
　　MASICONG@orcl> select sid from v$mystat where rownum =1 ;
　　SID
　　----------
　　23
　　MASICONG@orcl> insert into test values (1);
　　1 row created.
　　MASICONG@orcl> insert into test values (2);
　　insert into test values (2)
　　*
　　ERROR at line 1:
　　ORA-00060: deadlock detected while waiting for resource
　　MASICONG@orcl>  select sid from v$mystat where rownum =1 ;
　　SID
　　----------
　　37
　　MASICONG@orcl> insert into test values (2);
　　1 row created.
　　MASICONG@orcl> insert into test values (1);
　　insert into test values (1)
　　*
　　ERROR at line 1:
　　ORA-01013: user requested cancel of current operation