[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.
