[20180222]V$LOCK视图显示大量TX锁.txt

ゝ一世哀愁。 2021-09-19 04:18 132阅读 0赞

\[20180222\]V$LOCK视图显示大量TX锁.txt

\--//一般应用程序许多情况下一个TX锁,对应一个TM锁(一般DML都是单表,然后提交).生产系统存在大量的有TX锁,而没有TM锁.  
\--//实际上很容易发现问题在于应用存在大量的通过dblink访问的情况,通过例子说明:

1.环境:  
SCOTT@book> @ ver1  
PORT\_STRING                    VERSION        BANNER  
\------------------------------ -------------- --------------------------------------------------------------------------------  
x86\_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE PUBLIC DATABASE LINK LOOPBACK  
CONNECT TO SCOTT  
IDENTIFIED BY <PWD>  
USING 'localhost:1521/book:DEDICATED';

2.测试一:  
SCOTT@book> create table empx as select \* from emp;  
Table created.

SCOTT@book> @ &r/spid

SID    SERIAL\# PROCESS                  SERVER    SPID       PID  P\_SERIAL\# C50  
\---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------  
       274         97 38697                    DEDICATED 38698       21         47 alter system kill session '274,97' immediate;

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------

no rows selected

SCOTT@book> select \* from empx@loopback ;

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------  
10.15.22681

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
\---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------  
0000000081BE6218 0000000081BE6290        274 TX       655375      22681          6          0         22          0

\--//你可以发现当前会话仅仅存在tx锁.id1前16位,后16位分别表示事务的XIDUSN,XIDSLOT.id2表示XIDSQN.  
SCOTT@book> @ &r/tx1 655375 16  
select 655375,trunc(655375/power(2,16)) XIDUSN,mod(655375,power(2,16))  XIDSLOT from dual  
    655375     XIDUSN    XIDSLOT  
\---------- ---------- ----------  
    655375         10         15

\--//可以发现如果大量的dblink访问就会出现这样的情况.如果通过dblink执行dml语句,这样另外的数据库就存在大量TM,TX锁.继续测试:

SCOTT@book> commit ;  
Commit complete.

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------

no rows selected

SCOTT@book> create table t (a int);  
Table created.

SCOTT@book> select data\_object\_id,object\_id from dba\_objects where object\_name='T' and owner=user;  
DATA\_OBJECT\_ID  OBJECT\_ID  
\-------------- ----------  
         92614      92614

SCOTT@book> insert into t@loopback values (1);  
1 row created.

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------  
10.5.22687

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
ADDR             KADDR               SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME    BLOCK  
\---------------- ---------------- ------ ---- ---------- ---------- ---------- ---------- ---------- --------  
0000000081BE6218 0000000081BE6290    274 TX       655365      22687          6          0         40        0

SCOTT@book> select \* from v$lock where id1= 92614;  
ADDR             KADDR               SID TYPE   ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
\---------------- ---------------- ------ ---- ------ ---------- ---------- ---------- --------- ----------  
00007F4DC6FE3D68 00007F4DC6FE3DC8     80 TM   92614          0          3          0        153          0

SCOTT@book> @ &r/viewlock  
   SID    SERIAL\# USERNAME   OSUSER     MACHINE    MODULE       LOCK\_TYPE       MODE\_HELD  MODE\_REQUE LOCK\_ID1   LOCK\_ID2   OWNER  OBJECT\_TYP OBJECT\_NAME BLOCK LOCKWAIT  
\------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- ---------  
    80        181 SCOTT      oracle     gxqyydg4   oracle@gxqyy TM DML(TM)      Row-X (SX) None       92614      0          SCOTT  TABLE      T           No  
   274         97 SCOTT      oracle     gxqyydg4   SQL\*Plus     TX Transaction  Exclusive  None       655365     22687                                    No

\--//因为我的测试在单机环境模拟dblink,出现TM锁也在这台服务器.

3.测试二:

\--//还有什么情况会出现这样情况呢?设置表属性disable table lock,这样就没有TM锁存在,一般应用很少这样的情况.

SCOTT@book> alter table t disable table lock;  
Table altered.

SCOTT@book> insert into t values(2);  
1 row created.

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------  
10.32.22661

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
ADDR             KADDR               SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME    BLOCK  
\---------------- ---------------- ------ ---- ---------- ---------- ---------- ---------- ---------- --------  
0000000081BE6218 0000000081BE6290    274 TX       655392      22661          6          0         23        0

\--//注意这样的表不能删除,truncate,增加修改字段类型等操作.  
SCOTT@book> truncate table t ;  
truncate table t  
               \*  
ERROR at line 1:  
ORA-00069: cannot acquire lock -- table locks disabled for T

SCOTT@book> drop table t ;  
drop table t  
           \*  
ERROR at line 1:  
ORA-00069: cannot acquire lock -- table locks disabled for T

4.测试3:  
\--//在事务中使用savepoint也会出现,因为执行这个命令就在之前建立事务.  
SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------

no rows selected

SCOTT@book> savepoint a;  
Savepoint created.

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------  
10.13.22684

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
ADDR             KADDR               SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME    BLOCK  
\---------------- ---------------- ------ ---- ---------- ---------- ---------- ---------- ---------- --------  
0000000081B361C8 0000000081B36240    274 TX       655373      22684          6          0         14        0

\--//这样就仅仅存在tx锁,而没有TM锁.注意如果回滚到a,事务依旧存在.

SCOTT@book> rollback to a;  
Rollback complete.

SCOTT@book> @ &r/xid  
XIDUSN\_XIDSLOT\_XIDSQN  
\------------------------------  
10.13.22684

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
ADDR             KADDR               SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME    BLOCK  
\---------------- ---------------- ------ ---- ---------- ---------- ---------- ---------- ---------- --------  
0000000081B361C8 0000000081B36240    274 TX       655373      22684          6          0        158        0

SCOTT@book> commit ;  
Commit complete.

SCOTT@book> select \* from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');  
no rows selected

\--//还有那种情况会出现这样的情况,我就不知道了...也许还存在许多可能,我想到就这些.

4.总结:  
\--//dblink  
\--//表disable table lock;  
\--//建立savepoint.