Oracle LOCK內部機制及最佳實踐系列(四)深入分析mode 2-6 的TM鎖相互間的互斥關係

leonarding發表於2012-11-28
Oracle
深入分析mode 2-6 的TM鎖相互間的互斥關係

Oracle TM鎖的型別

鎖模式

鎖描述

含義

鎖定表的SQL


0

None

1

Null

空，本模式是oracle預留模式

2

Row Share(RS)

又叫(SS）

行級共享鎖，是限制最少的TM鎖，可以提供最高程度的併發性。其他會話可以對鎖定的表進行任何型別的DML操作，還可以與其他會話鎖並存

Lock table t in row share mode;


3

Row Exclusive Table Lock(RX)

又叫(SX)

行級排他鎖，通常已經有事務在修改行或者select…for update 修改結果集。允許其他事務對鎖定的表進行select  insert  update  delete 或 lock table 同時鎖定一張表

Lock table t in row exclusive mode;


4

Share Table Lock(S)

共享鎖，其他事務可以查詢鎖定的表但不能修改，只允許當前事務修改，但可以多個事務持有它

Lock table t in share mode;


5

Share Row Exclusive Table Lock(SRX)

又叫SSX

共享行級排他鎖，同一時間只允許一個事務持有和修改鎖定的表，其他事務可以查詢但不能修改

Lock table t in share row exclusive mode;


6

Exclusive Table Lock (X)

排他鎖，是限制最高的TM鎖，禁止其他事務執行任何型別的DML語句或者鎖表

一個表一般只能有一個6號鎖

Lock table t in exclusive mode;


Oracle鎖模式互斥關係圖

鎖模式

鎖名稱

允許級別

互斥級別


2

行級共享鎖

2 3 4 5


6


3

行級排他鎖

2 3


4 5 6


4

共享鎖

2 4


3 5 6


5

共享行級排他鎖

2

3 4 5 6


6

排他鎖

2 3 4 5 6


實驗鎖互斥

準備工作

LEO1@LEO1> select distinct sid from v$mystat;           LEO1使用者的會話id=138

       SID

----------------------------

       138

LEO2@LEO1> select distinct sid from v$mystat;           LEO2使用者的會話id=156

       SID

----------------------------

       156

LEO1@LEO1> create table lock1 (x int primary key);    建立lock1表，設定x列為主鍵

Table created.

LEO1@LEO1> insert into lock1 values(1);                     我們插入1

1 row created.

LEO1@LEO1> commit;                         提交

Commit complete.

LEO1@LEO1> select * from lock1;      現在只有一條記錄，並且沒有鎖

         X

----------------------------

         1

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

no rows selected


行級共享鎖 Row Share(RS)  2



LEO1@LEO1> lock table lock1 in row share mode;        把lock1表設定為行級共享鎖模式

Table(s) Locked.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- --

       138 TM              73472          0          2          0          0

模式標識：2

LEO1@LEO1> select object_name from dba_objects where object_id=73472;      lock1表物件id為73472

OBJECT_NAME

----------------------------------------------------------------------------------------------------

LOCK1


LEO2@LEO1> insert into leo1.lock1 values(2);

1 row created.

LEO2@LEO1> select * from leo1.lock1;

         X

----------

         1

         2

LEO2@LEO1> delete from leo1.lock1 where x=1;

1 row deleted.

LEO2@LEO1> select * from leo1.lock1;

         X

----------

         2

LEO2@LEO1> update leo1.lock1 set x=10 where x=2;

1 row updated.

LEO2@LEO1> select * from leo1.lock1;

         X

----------

        10

LEO2@LEO1> select * from leo1.lock1 for update;

         X

----------

        10

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- -----------------

       138 TM              73472          0          2          0          0

       156 TM              73472          0          3          0          0

       156 TX              524321     936          6          0          0

小結：行級共享鎖，是限制最少的TM鎖，可以提供最高程度的併發性。其他會話可以對鎖定的表進行任何型別的DML操作，還可以與其他會話鎖並存。


行級排他鎖  Row Exclusive Table Lock(RX)  3



LEO1@LEO1> lock table leo1.lock1 in row exclusive mode;              把lock1表設定為行級排他鎖

Table(s) Locked.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- --------------------

       138 TM              73472          0          3          0          0

模式標識：3

3級鎖是一個靈活性比較大的鎖，insert delete update 都可以產生一個3級鎖，也允許其他事務來修改鎖定的表

LEO1@LEO1> select * from leo1.lock1;

         X

-----------------

        10

        20

        30

        40

        50

會話27

LEO1@LEO1> update leo1.lock1 set x=100 where x=10;

1 row updated.

LEO1@LEO1> select * from leo1.lock1;              已經被修改了

         X

------------------

        20

        30

        40

        50

       100

會話156

LEO2@LEO1> insert into leo1.lock1 values(60);  也已經插入了，注意沒有提交的事務對別人是不可見的

1 row created.

LEO2@LEO1> select * from leo1.lock1;

         X

-----------------

        10

        20

        30

        40

        50

        60

會話146

LEO2@LEO1> delete from leo1.lock1 where x=20;   已經刪除

1 row deleted.

LEO2@LEO1> select * from leo1.lock1;

         X

--------------------

        10

        30

        40

        50

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------

        27 TM              73472          0          3          0          0

        27 TX              327698    1144          6          0          0

       138 TM              73472          0          3          0          0

       146 TM              73472          0          3          0          0

       146 TX              196626      975          6          0          0

       156 TM              73472          0          3          0          0

       156 TX              262163      782          6          0          0

大家都是共存的，誰也沒有阻塞誰，block列全部是0

小結：行級排他鎖，通常已經有事務在修改行或者select…for update 修改結果集。允許其他事務對鎖定的表進行select  insert  update  delete 或 lock table 同時鎖定一張表。


共享鎖  Share Table Lock(S)  4


LEO1@LEO1> select * from leo1.lock1;                              還是有5條記錄

         X

-----------------

        10

        20

        30

        40

        50

LEO1@LEO1> lock table leo1.lock1 in share mode;           把lock1表設定為共享鎖

Table(s) Locked.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- ---------- -----

       138 TM              73472          0          4          0          0

模式標識：4

4級鎖禁止其他會話對鎖定的表進行DML操作但可以select查詢，還允許多個事物一起持有

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ------------------ ---------- ---------- ------------------ ----

        13 TM             73472          0          0          4          0

       27 TM              73472          0          0          3          0

       138 TM            73472          0          4          0          1

       146 TM            73472          0          0          3          0

       156 TM            73472          0          0          3          0

27 146 156 會話都已經被阻塞了，有的朋友會問4級鎖可以和4級鎖共存這裡為什麼不行呢。呵呵因為有3級鎖在搗亂，4級和3級是不能共存的，所以我們把所有的3級鎖都釋放就可以了，我們來看一下

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ----------

        13 TM              73472          0          4          0          0

        27 TM              73472          0          2          0          0

       138 TM             73472          0          4          0          0

現在4級鎖 2級鎖都可以同時存在了

小結：共享鎖，其他事務可以查詢鎖定的表但不能修改，只允許當前事務修改，但可以多個事務持有它。


共享行級排他鎖  Share Row Exclusive Table Lock(SRX)  5


LEO1@LEO1> lock table leo1.lock1 in share row exclusive mode;   把lock1表設定為共享行級排他鎖

Table(s) Locked.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ------------ -----

       138 TM              73472          0          5          0          0

模式標識：5

LEO1@LEO1> select object_name from dba_objects where object_id=73472;  現在鎖定的表就是lock1

OBJECT_NAME

--------------------------------------------------------------------------------

LOCK1

會話138

LEO1@LEO1> select * from leo1.lock1;    現在表裡有5條記錄，我們看看其他會話能不能修改表

         X

--------------------

        10

        20

        30

        40

        50

會話156

LEO2@LEO1> insert into leo1.lock1 values(60);                  hang住了不能夠前進說明被阻塞

會話27

LEO1@LEO1> update leo1.lock1 set x=100 where x=10;     hang住了不能夠前進說明被阻塞

會話146

LEO2@LEO1> delete from leo1.lock1 where x=20;              hang住了不能夠前進說明被阻塞

那麼select… for update 當然也會被阻塞的，我們來看看其他事務能不能查詢呢！

會話23

LEO2@LEO1> select * from leo1.lock1;

         X

-------------------

        10

        20

        30

        40

        50

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ------------------- ---------- ------------------- ---------- -

        27 TM               73472          0          0          3          0

       138 TM              73472          0          5          0          1

       146 TM              73472          0          0          3          0

       156 TM              73472          0          0          3          0

會話23沒有被阻塞可以正常查詢耶，這也驗證了我們的觀點，讀操作不會被任何事務阻塞，也不會加鎖，27  146  156 會話3個行級排他鎖都在等待著138會話釋放鎖

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ----------

        13 TM              73472          0          2          0          0

      138 TM              73472          0          5          0          0

會話13的2級鎖和會話138的5級鎖可以共存符合我們的鎖互斥關係

小結：共享行級排他鎖，同一時間只允許一個事務持有和修改鎖定的表，其他事務可以查詢但不能修改。


排他鎖  Exclusive Table Lock (X)  6


LEO1@LEO1> lock table leo1.lock1 in exclusive mode;       把lock1表設定為排他鎖，等級最高的鎖

Table(s) Locked.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- -------------- ----------

       138 TM              73472          0          6          0          0

模式標識：6

會話156

LEO2@LEO1> insert into leo1.lock1 values(60);            

會話27

LEO1@LEO1> update leo1.lock1 set x=100 where x=10;     

會話146

LEO2@LEO1> delete from leo1.lock1 where x=20;         

會話13

LEO2@LEO1> lock table leo1.lock1 in row share mode;       測試鎖互斥

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where type in ('TM','TX') order by 1,2;

       SID TYPE              ID1        ID2      LMODE    REQUEST      BLOCK

---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- -------

        13 TM              73472          0          0          2          0        

        27 TM              73472          0          0          3          0

      138 TM              73472          0          6          0          1

      146 TM              73472          0          0          3          0

      156 TM              73472          0          0          3          0

6級鎖會阻塞除了自己外的所有會話的事務並且排斥其他的所有鎖模式連2級鎖都不行，是最高限制的TM鎖，當然select還是沒有問題的。

小結：排他鎖，是限制最高的TM鎖，禁止其他事務執行任何型別的DML語句或者鎖表。一個表一般只能有一個6號鎖。



2012.11.28
天津&winter
分享技術~成就夢想
Blog：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-750182/，如需轉載，請註明出處，否則將追究法律責任。

相關文章