結論

1,oracle11g測試環境
2,產生事務時會同時在表上加2個鎖，分別為事務鎖tx鎖以及表鎖tm鎖
3,表鎖tm鎖又從鎖定模式分為7個不同值
  本文僅測試了其中5個不同值
  分別為：
   2 - row-S (SS)
   3 - row-X (SX)
   4 - share (S)
   5 - S/Row-X (SSX)
   6 - exclusive (X)


4，上述的鎖定模式數字越大，持鎖規模越大，越影響並行操作，即:
   6 - exclusive (X) >  5 - S/Row-X (SSX)  >  4 - share (S) > 3 - row-X (SX) > 2 - row-S (SS)


5,關於這些持鎖模式相互相容性，請見下述的具體測試
6，關於如下2種持鎖模式在另一則文章進行測試 
   0 - none
   1 - null (NULL)

測試

SQL> create table t_lock(a int);


Table created.


SQL> insert into t_lock values(1);


1 row created.




commit;




SQL> update t_lock set a=999;


1 row updated.




數字1的十六進位制（在資料塊以16進位制儲存）
SQL> select dump(1,16) from dual;


DUMP(1,16)
----------------------------------
Typ=2 Len=2: c1,2


可見新事務會產生行鎖（TX鎖）及表鎖(TM鎖)
SQL> select sid,type,id1,id2,lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM','TX');


       SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
        37 TM        74639          0          3          0        261          0
        37 TX     10289154       4472          6          0        261          0




SQL> select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) from t_lock;


DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------ ------------------------------------
                                   4                                98894
測試下tm表鎖不同鎖模式的適用場景


先摘錄下ORACLE V$LOCK關於LMODE的含義
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)




先看  2 - row-S (SS)
會話1
SQL> lock table t_testlock in row share mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-s               0          4          0


 會話2
 SQL> insert into t_testlock values(2);


1 row created.




SQL> delete from t_testlock;


2 rows deleted.




SQL> update t_testlock set a=33;


1 row updated.


SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> drop table t_testlock purge;
drop table t_testlock purge
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;


Table(s) Locked.


SQL> lock table t_testlock in share  mode;


Table(s) Locked.


SQL> lock table t_testlock in share row exclusive mode;


Table(s) Locked.


SQL> lock table t_testlock in share update mode;


Table(s) Locked.


SQL> lock table t_testlock in exclusive mode;


lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可見row-s行級共享鎖不會阻塞其它會話的任何DML操作，但會阻塞ddl操作
即會阻塞表級排它鎖操作，但不會阻塞行級排它鎖




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


會話1
SQL> create table t_testlock(a int);


Table created.


SQL> create table t_testlock(a int);


Table created.


SQL> insert into t_testlock values(999);


1 row created.


可見insert獲取行級排它鎖row-x
SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-x               0        394          0


或者採用如下方式
會話1
SQL> lock table t_testlock in   row exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 row-x               0          2          0       


會話2
SQL> insert into t_testlock values(2);


1 row created.


SQL>  drop table t_testlock purge;
 drop table t_testlock purge
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;


Table(s) Locked.


SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
           *
ERROR at line 1:
ORA-01013: user requested cancel of current operation


可見row-x行級排它鎖不會阻塞其它會話的DML操作，但會阻塞ddl操作
同時會阻塞行級或表級排它鎖以及表級共享鎖




---------------------------------------------------------------
會話1
SQL> lock table t_testlock in  share mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 s                   0          5          0


 會話2
 SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock purge;


SQL> drop table t_testlock purge;
drop table t_testlock purge
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> 


SQL> select * from t_testlock for update;
select * from t_testlock for update
              *
ERROR at line 1:
ORA-01013: user requested cancel of current operation




SQL> select * from t_testlock;


         A
----------
        33


SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;


Table(s) Locked.


SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可見s鎖即表級共享鎖，鎖定級別最高，會阻塞其它會話的DML操作以及DDL操作，但不阻塞其它會話的SELECT查詢操作
會陰塞其它會話的行級及表級排它鎖




---------------------------------------------------------------
會話1
SQL> lock table t_testlock in  share row exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 ssx                 0          5          0


會話2
SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
       *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> select * from t_testlock for update;
select * from t_testlock for update
              *
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock;
drop table t_testlock
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> select * from t_testlock;


         A
----------
        33


SQL> 






SQL> lock table t_testlock in row share  mode;


Table(s) Locked.


SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation




可見ssx鎖即共享行排它表級，鎖定級別要比s鎖即共享表鎖更高，會阻塞其它會話的DML操作以及DDL操作，但不阻塞其它會話的SELECT查詢操作
而且其它會話不能再持有ssx鎖,且僅不會阻塞行級共享鎖
而


---------------------------------------------------------------
會話1
SQL> lock table t_testlock in   exclusive mode;


Table(s) Locked.


SQL> select sid,type,id1,id2,decode(lmode,0,'none',1,'null',2,'row-s',3,'row-x',4,'s',5,'ssx',6,'x') lmode,request,ctime,block   from v$lock where sid in (select sid from v$mystat where rownum=1) and type in ('TM');


       SID TYPE        ID1        ID2 LMODE         REQUEST      CTIME      BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
       245 TM        74659          0 x                   0          3          0




會話2
SQL> insert into t_testlock values(1);
insert into t_testlock values(1)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> delete from t_testlock;
delete from t_testlock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> update t_testlock set a=3;
update t_testlock set a=3
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> truncate table t_testlock;
truncate table t_testlock
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> drop table t_testlock;
drop table t_testlock
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired




SQL> select * from t_testlock for update;
select * from t_testlock for update
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


SQL> lock table t_testlock in row share  mode;
lock table t_testlock in row share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in row exclusive  mode;
lock table t_testlock in row exclusive  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share  mode;
lock table t_testlock in share  mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in share row exclusive mode;
lock table t_testlock in share row exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> lock table t_testlock in exclusive mode;
lock table t_testlock in exclusive mode
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation






SQL> 


SQL> select * from t_testlock;


         A
----------
        33




x鎖即表級排它鎖，是鎖定級別最高的鎖模式，它會阻塞其它會話的任何DML操作以及任何型別的加鎖操作 
可見x鎖最牛比，鎖定級別最高，會阻塞其它會話的任何鎖模式

個人簡介：