v$lock视图是Oracle中经典的锁检测接口之一,该视图列出了当前实例中被持有的锁信息;其中TYPE列指出了该持有锁的类型,比较常见的TX锁即事务队列锁(Transaction enqueue)和TM锁即DML enqueue锁(俗称表锁);一般某个会话在其事务(transaction)结束前总是持有一个TX锁,并一个或多个TM ROW-X(SX)锁 (一个事务中可能对多个表或对象进行了更新). 但偶尔我们会发现某个会话持有一个TX锁,却没有对应的TM锁的情况. 第一次接触到该问题,可能会觉得有些不可思议,但这种情况却的确存在:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where type in ('TM','TX');
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
2090 TM 112504 0 3 0 0 2
2090 TM 47529 0 3 0 0 2
2090 TM 47530 0 3 0 0 2
2090 TM 57497 0 3 0 0 2
2090 TM 109826 0 3 0 0 2
1137 TM 46920 0 3 0 891 2
2090 TM 49919 0 3 0 0 2
1501 TX 3538968 635593 6 0 273274 2
586 TX 8650772 560279 6 0 88978 2
1112 TX 7929874 781313 6 0 1291 2
970 TX 4128815 688150 6 0 238769 2
2175 TX 2555916 738283 6 0 273630 2
2016 TX 1966099 673442 6 0 6164 2
1933 TX 1769516 589203 6 0 792823 2
2206 TX 13434902 679577 6 0 237904 2
943 TX 16384041 282250 6 0 27004 2
1207 TX 7077931 494826 6 0 587548 2
374 TX 14286860 599261 6 0 279997 2
2404 TX 4325408 713583 6 0 0 2
2145 TX 9109533 592942 6 0 26868 2
2296 TX 1835016 631599 6 0 78593 2
1967 TX 14876690 484837 6 0 288239 2
3276 TX 1572869 721894 6 0 115 2
3232 TX 4915206 704681 6 0 332428 2
1374 TX 14417924 546699 6 0 263 2
635 TX 3473435 799864 6 0 4 2
1179 TX 10289168 644315 6 0 8679 2
3034 TX 14221320 556459 6 0 23417 2
1403 TX 6553606 666507 6 0 7949 2
2872 TX 131116 637111 6 0 112979 2
1316 TX 8519697 598740 6 0 5 2
1264 TX 11534348 794295 6 0 3062 2
1793 TX 5505026 613635 6 0 114 2
2689 TX 10878991 511962 6 0 792824 2
743 TX 393235 630546 6 0 721 2
2744 TX 4784159 615312 6 0 20424 2
1959 TX 2424878 763163 6 0 201334 2
1468 TX 2293801 918876 6 0 1 2
1980 TX 65555 776770 6 0 6463 2
2038 TX 9699339 600600 6 0 1 2
1551 TX 3080213 560800 6 0 587549 2
2835 TX 7733272 885787 6 0 173 2
2246 TX 11665419 657752 6 0 17988 2
1229 TX 11010083 528844 6 0 792824 2
1476 TX 12320806 581063 6 0 792823 2
3254 TX 10813472 551889 6 0 9643 2
1794 TX 3014673 556138 6 0 21 2
1682 TX 6291465 661166 6 0 10960 2
998 TX 3670027 717078 6 0 114 2
2989 TX 2031638 672709 6 0 1233 2
2090 TX 13107209 436774 6 0 1 2
3203 TX 5373978 731263 6 0 4982 2
1676 TX 13172747 565610 6 0 273811 2
2096 TX 1900577 665848 6 0 6464 2
1563 TX 6357010 556097 6 0 587549 2
1899 TX 2621459 746534 6 0 279634 2
1722 TX 9764875 567516 6 0 14502 2
2907 TX 8585236 553757 6 0 225941 2
2386 TX 6225966 675153 6 0 855 2
2238 TX 12189735 554114 6 0 6121 2
1118 TX 15663139 468328 6 0 237037 2
2280 TX 8716326 593341 6 0 792824 2
2222 TX 524309 678972 6 0 1 2
1230 TX 15794181 512612 6 0 792823 2
2593 TX 16711683 230810 6 0 201661 2
2261 TX 4849676 681767 6 0 18159 2
1069 TX 12451855 465400 6 0 587549 2
1203 TX 8388613 602148 6 0 587549 2
2829 TX 589835 571720 6 0 5143 2
988 TX 3997708 645741 6 0 28958 2
1771 TX 13631511 521392 6 0 2 2
2601 TX 1638423 605864 6 0 5 2
2319 TX 655374 738909 6 0 3 2
2963 TX 2097153 637674 6 0 7640 2
3146 TX 11599895 699476 6 0 9561 2
377 TX 9043977 638698 6 0 181366 2
3166 TX 2490407 904504 6 0 6688 2
555 TX 7012358 590789 6 0 112970 2
3226 TX 5898270 519207 6 0 792823 2
1103 TX 9240589 672736 6 0 18184 2
3195 TX 10158108 504545 6 0 2576 2
2715 TX 15925274 428945 6 0 201522 2
2558 TX 1703943 643492 6 0 3047 2
3021 TX 12386346 494943 6 0 587549 2
2264 TX 1441795 692086 6 0 149 2
2416 TX 6946836 655343 6 0 261655 2
1397 TX 12517393 588260 6 0 1010345 2
1439 TX 16318509 299824 6 0 5011 2
869 TX 9437186 513497 6 0 2 2
2535 TX 15597580 456805 6 0 182254 2
2130 TX 4521998 674031 6 0 204313 2
2708 TX 7405570 536059 6 0 5836 2
2259 TX 15204353 467190 6 0 792823 2
429 TX 10485792 738387 6 0 278846 2
2031 TX 8912896 683349 6 0 273565 2
559 TX 10092545 566706 6 0 587548 2
3067 TX 13369376 673321 6 0 5 2
2290 TX 15990804 472707 6 0 792823 2
3208 TX 5439521 627538 6 0 156833 2
563 TX 7798805 741212 6 0 18417 2
2556 TX 5570567 483016 6 0 587549 2
1443 TX 13828137 590212 6 0 273603 2
1594 TX 9633796 613365 6 0 179342 2
500 TX 2162700 615179 6 0 3505 2
1458 TX 2752543 636530 6 0 587549 2
2294 TX 14811182 545859 6 0 1 2
2647 TX 262173 631006 6 0 376 2
2770 TX 8847365 665512 6 0 18 2
1881 TX 6815766 505031 6 0 17989 2
1733 TX 2228250 744841 6 0 89732 2
3253 TX 12124195 661906 6 0 4917 2
1171 TX 4390934 688053 6 0 952 2
575 TX 2359297 792394 6 0 932 2
1952 TX 7995429 778698 6 0 7377 2
1073 TX 10420261 676462 6 0 21 2
2976 TX 14483498 557853 6 0 332223 2
310 TX 8126473 611980 6 0 196341 2
2624 TX 12779564 585073 6 0 177140 2
1400 TX 8454154 548105 6 0 1 2
2541 TX 3342373 612450 6 0 154890 2
882 TX 11796503 502190 6 0 5987 2
1475 TX 327695 668278 6 0 0 2
1694 TX 7864342 655797 6 0 587549 2
1882 TX 6422539 647676 6 0 16739 2
3133 TX 13697064 591601 6 0 4 2
637 TX 12845067 554594 6 0 976 2
809 TX 11730960 737397 6 0 13 2
2799 TX 3604491 762060 6 0 4 2
1634 TX 8978449 564400 6 0 3 2
601 TX 15532043 430716 6 0 792823 2
2739 TX 9830436 585243 6 0 5955 2
1110 TX 12713992 705911 6 0 5057 2
1738 TX 16187423 344322 6 0 202512 2
3040 TX 9502753 586506 6 0 12690 2
1796 TX 7602221 697164 6 0 254457 2
1149 TX 7143454 442359 6 0 999 2
2940 TX 14352418 620297 6 0 204583 2
137 rows selected.
most tx lock ,no corresponding tm locks.
i have confirmed that there are no table lock disabled tables in database.
SQL> select * from dba_tables where table_lock!='ENABLED';
no rows selected
以上演示其实已经列出了引起该种现象的一种可能性,即某些表上是"disable table lock"的,即该表上不会出现任何类型的TM锁,这也就意味着该表在"Enable table lock"之前不能进行任何DDL修改;关于"disable table lock"的论述可以参见Steve Adams的<oracle8i internal services一书。 我在这里再列出几种可能的原因,权作抛砖引玉: 1. 事务中包括远程DML操作,最有可能的一种情况。 2. Remote select statement through dblink can also generate tx lock.远程select也会在本地产生TX锁. 3. 事务中使用savepoint :
SQL> drop table tv;
表已删除。
SQL> create table tv (t1 int);
表已创建。
SQL> savepoint a;
保存点已创建。
SQL> insert into tv values(1);
已创建 1 行。
SQL> rollback to a;
回退已完成。
SQL> select sid,type,lmode from v$lock where sid=(select distinct sid from v$my
tat) and type in ('TM','TX');
SID TY LMODE
---------- -- ----------
125 TX 6
SQL> commit;
提交完成。
SQL> select sid,type,lmode from v$lock where sid=(select distinct sid from v$mys
tat) and type in ('TM','TX');
未选定行
4.使用dbms_transaction包的相关函数,如:
SQL> select sid,type,lmode from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');
no rows selected
SQL> exec dbms_output.put_line( dbms_transaction.local_transaction_id(true) );
PL/SQL procedure successfully completed.
SQL> select sid,type,lmode from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');
SID TY LMODE
---------- -- ----------
159 TX 6
5. 事务中所涉及到的表启用了"disable table lock",如:
SQL> desc apps.tv;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 NUMBER(38)
SQL> alter table apps.tv disable table lock;
Table altered.
SQL> insert into apps.tv values(2);
1 row created.
SQL> select sid,type,lmode from v$lock where sid =(select distinct sid from v$mystat) and type in ('TX','TM');
SID TY LMODE
---------- -- ----------
159 TX 6
亦或者DML_LOCKS设置为0(DML_LOCKS参数设置为0,在9i以上版本会引起诸多问题,一般仅在测试或研究时修改该参数).
以下列出Metalink上,关于介绍如何troubleshooting 分布式事务的文档:
Script to report information about distributed transactions.
Product Name, Product Version Oracle Server, 7.3.x to 11.1 Platform Platform Independent Date Created 12-OCT-1999
Instructions
Execution Environment: SQL, SQL*Plus
Access Privileges: Must be run by SYS , or a user with privileges to select from x$k2gte , x$ktcxb , x$ksuse , and v$session_wait .
Usage: sqlplus /nolog SQL> connect sys/ SQL> @distri SQL> @distri_details
Instructions:
Copy the first query into a file named distri.sql. Execute the script from sqlplus connected to sys. If further information needs to be collected then copy the second query to a file named distri_details.sql and run it while connected as sys.
PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.
Description
This script includes two queries to show information about active distributed transactions. The structure of x$k2gte is slightly modified with Oracle8, so Oracle7 users should use the commented lines of the queries.
o First Query : distri.sql Is intended to show , for each active distributed transaction :
GTXID = Global Transaction ID LSESSION= Local Session , this is the session in your local database (ie: the database where you are connected and running this script) S = Status of the LSESSION, it has 1 letter with this meaning :
First ----------------- I (inactive) A (active) C (cached) S (sniped) K (killed)
WAITING = what LSESSION is waiting for
ORIGIN = Machine-PID , Machine where the Client Tool is running, PID is the Operating System Process Id of Client Process
You would want to run this SELECT to have a quick look at the active distributed transactions.
o Second Query : distri_details.sql Is intended to show more details about the active distributed transactions. See the "Sample Output" below to get details.
You would want to run this SELECT when you need to see all the information about a particular distributed transaction .
References
Note:62354.1 TX Transaction locks - Example wait scenarios
Script
REM distri.sql column origin format a13 column GTXID format a35 column LSESSION format a10 column s format a1 column waiting format a15 Select /*+ ORDERED */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN", substr(g.K2GTITID_ORA,1,35) "GTXID", substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" , substr(decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED'),1,1) "S", substr(event,1,10) "WAITING" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w -- where g.K2GTeXCB =t.ktcxbxba <= use this if running in Oracle7 where g.K2GTDXCB =t.ktcxbxba -- comment out if running in Oracle8 or later and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and w.sid=s.indx;
REM distri_details.sql set headin off select /*+ ORDERED */ '----------------------------------------'||' Curent Time : '|| substr(to_char(sysdate,'dd-Mon-YYYY HH24.MI.SS'),1,22) ||' '||'GTXID='||substr(g.K2GTITID_EXT,1,10) ||' '||'Ascii GTXID='||g.K2GTITID_ORA ||' '||'Branch= '||g.K2GTIBID ||' Client Process ID is '|| substr(s.ksusepid,1,10)||' running in machine : '||substr(s.ksusemnm,1,80)||' Local TX Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,10) ||' Local Session SID.SERIAL ='||substr(s.indx,1,4)||'.'|| s.ksuseser ||' is : '||decode(bitand(ksuseidl,11),1,'ACTIVE',0, decode(bitand(ksuseflg,4096),0,'INACTIVE','CACHED'), 2,'SNIPED',3,'SNIPED', 'KILLED') || ' and '|| substr(STATE,1,9)|| ' since '|| to_char(SECONDS_IN_WAIT,'9999')||' seconds' ||' Wait Event is :'||' '|| substr(event,1,30)||' '||p1text||'='||p1 ||','||p2text||'='||p2 ||','||p3text||'='||p3 ||' Waited '||to_char(SEQ#,'99999')||' times '||' Server for this session:' ||decode(s.ksspatyp,1,'Dedicated Server', 2,'Shared Server',3, 'PSE','None') "Server" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session_wait w -- where g.K2GTeXCB =t.ktcxbxba <= use this if running Oracle7 where g.K2GTDXCB =t.ktcxbxba -- comment out if running Oracle8 or later and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and w.sid=s.indx; set headin on -- end script
Sample Output
For all the samples, we have 2 machines :
esibm1 with a database V73_0.WORLD and esosf2 with a database V734.WORLD
In esibm1 we have a public database link V734 using 'V734.WORLD'; and in esosf2 we have a public database link V73_0 using 'V73_0.WORLD';
Sample-1 : ~~~~~~~~~~
-in esibm1: sqlplus scott/tiger
select * from dual@V734 for update ;
-in esibm1: sqlplus sys/manager @distri
ORIGIN
GTXID
LSESSION
S
WAITING
esibm1-23658
V73_0.WORLD.ea8e383e.2.45.4446
9.267
I
SQL*Net-m
ORIGIN= esibm1-23658 , this means the Client - sqlplus in this case - is running in machine esibm1 and it is the process with PID= 23658
GTXID = V73_0.WORLD.ea8e383e.2.45.4446 is the Global Transaction Identifier, it identifies the database where the distributed transaction started, it is the database where the Client is directly connected. V73_0 = db_name WORLD = db_domain ea8e383e = reverse Db Id ( DataBase ID, it is calculated when you create ^^^^^^^^ the database, you can see this DB Id in a dump of Control File, for example, for V73_0 it is :
DUMP OF CONTROL FILES, Seq # 7205 = 0x1c25 FILE HEADER: Software vsn=120586240=0x7300000, Compatibility Vsn=120586240=0x7300000 Db Id=3817072814=0xe383e8ae, Db Name='V73_0' ^^^^^^^^^^ 2.45.4446 = Local Transaction ID in esibm1 , This session ( LSESSION) will keep an eXclusive lock on a local enqueue "TX-2.45-4446" See Note-62354.1
LSESSION = 9.267 , this is the V$SESSION.ID and V$SESSION.SERIAL# of the session you are using in V73_0 database in esibm1. ( you can see all session details in esibm1 with : select * from v$session where sid=9 and serial#=267 ; )
S = I , this is the State, it is I for Inactive .
WAITING , this is what this LSESSION is waiting for, in this case it is waiting for 'SQL*Net message from client' , it is the standard wait when we are waiting for Client to give us work to do .
-in esosf2 : sqlplus sys/manager @distri
ORIGIN
GTID
LSESSION
S
WAITING
esibm1-23658
V73_0.WORLD.ea8e383e.2.45.4446
11.1013
I
SQL*Net m
LSESSION = 11.1013 , this is the V$SESSION.ID and V$SESSION.SERIAL# of the session you are using in V734 database in esosf2. ( you can see all session details with select * from v$session where sid=11 and serial#=1013 ; )
Sample-2 ~~~~~~~~~ -in esosf2 : sqlplus scott/tiger select * from dept for update ;
-in esibm1 : sqlplus scott/tiger select * from dept@V734 for update . In this case, the distributed transaction will have to wait because it tries to lock dept's rows which are locked by other transaction. We use the SELECT distri_details.sql to show what happens :
- in esibm1 : sqlplus sys/manager@distri_details
This shows the session in the database where the Client is directly connected, it is using a dblink, and it is waiting a message from dblink.
---------------------------------------- Curent Time : 06-Jun-2000 12.50.34 GTXID=5637335F30 Ascii GTXID=V73_0.WORLD.ea8e383e.2.21.4446 Branch= 0000 Client Process ID is 23658 running in machine : esibm1 Local TX Id =2.21.4446 Local Session SID.SERIAL =9.267 is : ACTIVE and WAITING since 0 seconds Wait Event is : SQL*Net message from dblink driver id=1446458164,#bytes=1,=0 Waited 207 times
Server for this session:Dedicated Server
- in esosf2 : sqlplus sys/manager @distri_details
This shows the session in the remote database, it is waiting to lock dept's rows ( waiting for an TX enqueue , see Note:62354.1 )
---------------------------------------- Curent Time : 06-Jun-2000 13.09.38 GTXID=5637335F30 Ascii GTXID=V73_0.WORLD.ea8e383e.2.21.4446 Branch= 020015005E1100000104 Client Process ID is 23658 running in machine : esibm1 Local TX Id =0.0.0 Local Session SID.SERIAL =11.1013 is : ACTIVE and WAITING since 6 seconds Wait Event is : enqueue name|mode=1415053318,id1=262158,id2=2611 Waited 122 times
Server for this session:Shared Server