Oracle鎖實驗

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 id=2 where id =1; 

1 row updated.

MASICONG@orcl> update test set id=2 where id =1;  另一回話處於Block狀態

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 id=10;

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 id =2 where id=1;

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 id =2;

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 id=10

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

 

本文出自 “無雙城” 部落格，謝絕轉載！