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
Distributed Database, Transactions and Two Phase Commit [ID 13229.1]
What is the Distributed Database Option?
—————————————-
A distributed system is one in which both data and transaction processing
are divided between one or more computers connected by a network, each
computer playing a specific role in the system.
This configuration has multiple databases, each of which is accessed
directly by a single server and can be accessed indirectly by other
instances through server/server cooperation. Distributed systems
allow you to have data physically located at several sites, and
each site can transparently access all of the data.
Each node can be used for database processing, but the data is
permanently partitioned among the nodes. Several smaller server
machines can be cheaper and more flexible than one large,
centrally located server.
The key goals of a distributed database system are availability,
accuracy, concurrency, and recoverability.
The Client-Server Model and Distributed Systems
———————————————–
The client-server model is basic to distributed systems. It is a
response to the limitations presented by the traditional mainframe
client-host model, in which a single mainframe provides shared data
access to many dumb terminals. The client-server model is also a
response to the local area network (LAN) model, in which many isolated
systems access a file server that provides no processing power.
Client-server architecture provides integration of data and services
and allows clients to be isolated from inherent complexities, such as
communication protocols. The simplicity of the client-server architecture
allows clients to make requests that are routed to the appropriate server.
These requests are made in the form of transactions. Client transactions
are often SQL or PL/SQL procedures and functions that access individual
databases and services.
Distributed Database Characteristics
————————————
This article describes the twelve specifications for the ideal distributed
database management system and how ORACLE conforms to these specifications.
Oracle’s distributed architecture, comprising SQL*Net, Open Gateway and the
Oracle Server, provides an effective solution to the challenge of sharing
data in a networked environment.
The Oracle Server’s distributed architecture provides effective data sharing
in a networked environment using both client-server and distributed database
architectures.
In a client-server environment, communication takes place between two
processes that reside on different machines. The client executes the
application or application tool and sends requests to the server for data.
The received data is processed at the client machine. This is known as
distributed processing. The ideal distributed system should look like a
non-distributed system. Twelve specifications for the ideal distributed
database were developed by C.J. Date.
The Oracle Server supports most of the ideal distributed features.
1. Site Autonomy
Site autonomy means that each server participating in a distributed database is
administered independently from all other databases.
The data is owned and managed locally. Local operations remain purely local.
One site (node) in the distributed system does not depend on another site to
function successfully.
2. No reliance on a central site
All sites are treated as equals. Each site has its own data dictionary.
3. Continuous Operation
Incorporating a new site has no effect on existing applications and does not
disrupt service.
4. Location Independence
Users can retrieve and update data independent of the site.
5. Fragmentation Independence
Users can store parts of a table at different locations. Both horizontal and
vertical partitioning of data is possible.
6. Replication Independence
Stored copies of data can be located at multiple sites. Read-only snapshots
and updatable snapshots provide read-only and updatable copies of tables,
respectively. Symmetric Replication using triggers make readable and
writable replication possible.
7. Distributed Query Processing
Users can query a database residing on another node. The query is executed
at the node where the data is located.
8. Distributed Transaction Management
A transaction can update, insert or delete data from multiple databases.
The two-phase commit mechanism in Oracle ensures the integrity of distributed
transactions. Row level locking ensures a high level of data concurrency.
9. Hardware Independence
Oracle runs on all major hardware platforms.
10. Operating System Independence
A specific operating system is not required. Oracle runs under a variety
of operating systems.
11. Network Independence
The Oracle Server’s SQL*Net supports most popular networking software.
Network independence allows communication across homogeneous and heterogenous
networks. Oracle’s MultiProtocol Interchange enables applications to
communicate with databases across multiple network protocols.
12. DBMS Independence
DBMS Independence is the ability to integrate different databases. Open
Gateway supports connections to non-Oracle databases.
13. Distributed Database Security
The database supports all of the security features that are available with a
non-distributed database environment for distributed database systems, including:
Password authentication for users and roles
Some types of external authentication for users and roles including:
Kerberos version 5 for connected user links
DCE for connected user links
DISTRIBURTED TRANSACTIONS AND THE TWO PHASE COMMIT
==================================================
Two phase commit only comes in play during a commit of a distributed
transaction. The whole purpose is to maintain the integrity of the
“global” database. In other words, two phase commit guarantees that
everything will either commit or rollback.
TRANSACTION TYPES:
LOCAL TRANSACTION contains ONLY statements on the local node.
REMOTE TRANSACTION contains one or more statements which ALL
reference the same remote node.
DISTRIBUTED
TRANSACTION contains statements that modify data in
two or more distinct nodes.
* The only place where 2-PHASE COMMIT comes
into play.
TERMS:
CLIENTS are nodes that references information from another database
(C) server in a distributed transaction.
SERVERS are nodes that are directly referenced in a distributed
(S) transaction, or is requested to participate in a transaction
because another node requires data from it.
GLOBAL
COORDINATOR is the node in which the distributed transaction
(GC) originates.
LOCAL
COORDINATOR is the node that references data on other nodes to
(LC) complete its part in the distributed transaction.
COMMIT POINT
SITE is the site with the highest commit point strength
(CPS) “init.ora” parameter. It is usually the most critical
site that can not afford collisions in case of an
in-doubt transaction.
SCN is the system commit number.
SCN is essentially an internal database clock.
This is a monotonically increasing an unique number
for each transaction.
COMMIT_POINT_STRENGTH is the init.ora parameter that determines the
(CPstr) COMMIT POINT SITE.
When you attempt to commit a distributed transaction, you will enter
ORACLE’S TWO PHASE COMMIT MECHANISM.
TWO-PHASE COMMIT:
PREPARE PHASE:
1) Commit point site is determined.
2) Global coordinator asks all participating nodes
(except commit point site) to promise to COMMIT or
ROLLBACK the transaction regardless of failure).
This information is propagated by the local
coordinators. The servers have to be prepared before
the local or global coordinators (except the commit
point site). The local coordinator is responsible
for asking dependant nodes to prepare.
POSSIBLE RESPONSES FROM NODES:
* PREPARED
* ABORT
* READ-ONLY NODES
Locks obtained during the distributed transaction
will continue to be held.
Redo is flushed to the local redo logs.
3) Each node will pass back the SCN for his node.
4) Global Coordinator determines the max SCN.
After all the nodes have prepared successfully, we enter the commit phase.
All transactions except those found in the commit point site are “in-doubt”
until the commit point phase completes successfully.
COMMIT PHASE:
1) Global coordinator sends the max SCN to the commit point
site and asks it to commit.
2) Commit Point Site will try to commit or everything is
rolled back.
The locks are released in the commit point site first.
3) If committed/rolled back, the Commit Point Site will
inform the Global Coordinator which will commit/rolled back
at that time.
4) The information will propogate down to its clients/local
coordinators and they will commit/roll back and propagate
the information down to their servers until there are no
more servers.
(note: READ ONLY nodes do not participate in 2-phase commit.)
EXAMPLE:
UPDATE EMP SET SAL= SAL * 1.10 WHERE DEPTNO=10;
UPDATE EMP@HAWAII SET SAL = SAL*1.20 WHERE DEPNO=20;
BEFORE UPDATE TRIGGER Fires and updates
dept@newyork.
another trigger fires and inserts
dept_audit@paranoid.
after update trigger fires and inserts into the global
audit table, emp_audit@headquarters
INSERT INTO VACATION_TIME SELECT * FROM DAYS_OFF@HOME
COMMIT; (TWO-PHASE COMMIT BEGINS HERE)
_______________
| |
select days_off | HOME | SERVER /*READ-ONLY*/
|_____________| SCN=20000
| CPstr=250
|
_______________
| | GC/LC/CLIENT
update emp | LOCAL | SCN=100
|_____________| CPstr=125
/ \
/ \
____________ _____________
update emp | | | | insert emp_audit
SERVER/LC/CLIENT | HAWAII | | HQ | SERVER
SCN=250 |___________| |____________| SCN =1000
CPstr=95 / CPstr=10
/
/
————
| | inserts dept_audit
| PARANOID | SERVER
|__________| SCN=50
CPstr=205
PREPARE PHASE:
1) COMMIT POINT SITE IS PARANOID
The global coordinator will already know what the
commit point strength of each node prior to the commit.
Read-only nodes are not included.
2) All nodes except for PARANOID is asked to prepare.
3) HAWAII, the local coordinator, is responsible to ask her
dependent nodes to prepare before she prepares. In this
case, PARANOID is a commit point site; thus, it is ignored.
4) The highest SCN is sent to LOCAL node via the
local coordinators. The highest SCN is 1000.
5) All nodes which PREPARED will flush entries of the
transaction to the redo logs if not already done.
If any of the nodes send an “ABORT” message back, then the transaction is
rolled back at this time. Any failure after the PREPARE phase will result
with “in-doubt” transactions.
COMMIT PHASE:
1) PARANOID IS ASKED TO COMMIT OR ROLLBACK BY THE LOCAL (GC).
2) PARANOID commits at a SCN greater than 1000.
a) Redo is flushed.
b) Locks are released.
c) outcome is relayed back to the LOCAL node (GC).
Assume success:
3) AFter receiving the information, GC will commit at the
same SCN and pass the information to its dependents.
a) commit flushed to redo logs.
b) data locks are released.
c) GC will pass the information to HAWAII and HQ.
(1) They, in turn, will commit and HAWAII
will pass the information to PARANOID.
If all is successful, every statement will commit with the same SCN and then
RECO will delete the entries from “dba_2pc_pending” and “dba_2pc_neighbors”
tables. Afterwards, the nodes will “forget” the transaction.
Forget Phase
1) After the participating nodes notify the commit point site
that they have committed, the commit point site can forget
about the transaction. The following steps occur:
2) After receiving notice from the global coordinator that all
nodes have committed, the commit point site erases status
information about this transaction.
3)The commit point site informs the global coordinator that
it has erased the status information.
4)The global coordinator erases its own information about the
transaction.
TWO-PHASE COMMIT QUIZ/ANSWERS
=============================
1) What is the difference between remote transaction and distributed
transaction?
REMOTE TRANSACTION contains one or more statements which ALL
reference the same remote node.
DISTRIBUTED TRANSACTION contains statements that modify data in
two or more distinct nodes.
* The only place where 2-PHASE COMMIT comes into play.
2) When does two-phase commit come in play?
Two phase commit only comes in play during a commit of a distributed
transaction. The whole purpose is to maintain the integrity of the
“global” database. In other words, two phase commit guarantees that
everything will either commit or rollback.
3) Define distributed database.
An environment that includes multiple servers where users manipulate
data as if there is one “global database”. With the widening use of
heterogeneous hardware environments and multiple database servers, it has
become essential to maintain the integrity of this “global database”. Of
course, the distributed concept is usually transparent to the end user and
the application programmers.
Another defintion could be:
A distributed database is a database that is not stored in its entirety at
a single physical location. Rather, a distributed database is a database that
is stored across a network of locations that are connected via communication
links. A distributed database consists of a collection of sites or nodes
connected together into a communication network.
4) When is a transaction considered “in-doubt”?
After all the nodes have prepared successfully, we enter the commit phase.
All transactions except those found in the commit point site are “in-doubt”
until the commit point phase completes successfully.
5) Define the following terms: CLIENTS, DATABASE SERVERS, GLOBAL COORDINATOR,
LOCAL COORDINATOR, COMMIT POINT SITE
CLIENTS are nodes that references information from another database
(C) server in a distributed transaction.
SERVERS are nodes that are directly referenced in a distributed
(S) transaction, or is requested to participate in a transaction
because another node requires data from it.
GLOBAL COORDINATOR is the node in which the distributed transaction
(GC) originates.
LOCAL COORDINATOR is the node that references data on other nodes to
(LC) complete its part in the distributed transaction.
COMMIT POINT SITE is the site with the highest commit point strength
(CPS) init.ora parameter. It is usually the most critical
site that can not afford collisions in case of an
in-doubt transaction.
SCN is the system commit number.
SCN is essentially an internal database clock.
This is a monotonically increasing an unique number for
each transaction.
COMMIT_POINT_STRENGTH is the init.ora parameter that determines the
(CPstr) COMMIT POINT SITE.
6) Can the global coordinator be the commit point site as well?
Yes, the only prerequisites are that the node is part of the distributed
transaction (not read-only) and it has the highest commit point strength
parameter.
7) What distinguishes the commit point site?
COMMIT POINT SITE is the site with the highest commit point strength
(CPS) “init.ora” parameter. It is usually the most critical
site that can not afford collisions in case of an
in-doubt transaction.
8) What does RECO do?
RECO PROCESS
* gets information from the “DBA_2PC_PENDING” and
“DBA_2PC_NEIGHBORS” views.
* executes as the global or local coordinator
* tries to communicate wit other nodes in the in-doubt transaction
If it cannot connect, it will continue to try at exponential
intervals.
* resolves in-doubt transactions if the connection is up.
* uses the same DBLINK as the transaction
* removes resolved transactions from the pending transaction table.
9) What happens during the PREPARE phase? Please be as detailed as possible?
PREPARE PHASE:
1) Commit point site is determined.
2) Global coordinator asks all participating nodes
(except commit point site) to promise to COMMIT or
ROLLBACK the transaction regardless of failure).
This information is propagated by the local
coordinators. The servers have to be prepared before
the local or global coordinators (except the commit
point site). The local coordinator is responsible
for asking dependant nodes to prepare.
POSSIBLE RESPONSES FROM NODES:
* PREPARED
* ABORT
* READ-ONLY NODES
Locks obtained during the distributed transaction
will continue to be held.
Redo is flushed to the local redo logs.
3) Each node will pass back the SCN for his node.
4) Global Coordinator determines the max SCN.
After all the nodes have prepared successfully, we enter the commit phase.
All transactions except those found in the commit point site are “in-doubt”
until the commit point phase completes successfully.
10) What happens during the COMMIT phase? Please be as detailed as possible?
COMMIT PHASE:
1) Global coordinator sends the max SCN to the commit point
site and asks it to commit.
2) Commit Point Site will try to commit or everything is
rolled back.
The locks are released in the commit point site first.
3) If committed/rolled back, the Commit Point Site will
inform the Global Coordinator which will commit/rolled back
at that time.
4) The information will propogate down to its clients/local
coordinators and they will commit/roll back and propagate
the information down to their servers until there are no
more servers.
(NOTE: READ ONLY nodes do not participate in 2-phase commit.)
11) What is SCN and how is it significant in two-phase commit?
SCN is the system commit number.
SCN is essentially an internal database clock.
This is a monotonically increasing an unique number for
each transaction.
The entire distributed transaction will be committed at the same SCN.
This will allow some type of consistency during distributed recovery.
12) Which two views are helpful during MANUAL distributed recovery?
They are “dba_2pc_pending” and “dba_2pc_neighbors”.
13) When does RECO remove the entries from those views?
If all is successful, every statement will commit with the same SCN and then
RECO will delete the entries from “dba_2pc_pending” and “dba_2pc_neighbors”
tables. Afterwards, the nodes will “forget” the transaction.
14) List some cases where you may do MANUAL RECOVERY?
A dba may do some manual recovery if there was a network or system or
database crash and the “in-doubt” transactions are holding locks that
are crucial to other applications.
15) How should you pick your COMMIT POINT SITE?
COMMIT POINT SITE is the site with the highest commit point strength
(CPS) “init.ora” parameter. It is usually the most critical
site that can not afford collisions in case of a doubted transaction.
You need to coordinate with the other dba adminstrators to make that
decision.
Use this command to wake RECO up after that:
How to Troubleshoot Distributed Transactions [ID 100664.1]
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.5.0 to 10.2.0.1
Information in this document applies to any platform.
Checked for relevance on 13-June-2008 by Faye Todd.
Appearance updated, no changes to technical content 19-Feb-2007
Purpose
You need to resolve a distributed transaction error because of errors received.
But before you try to implement any other steps, please diagnose whether it is
or it is not a REAL distributed transaction.
Last Review Date
June 13, 2008
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
Steps to Troubleshoot Distributed Transactions
1. Check the alert.log for errors, like:
ORA-1591 “lock held by in-doubt distributed transaction %s”
ORA-2062 “distributed recovery received dbid x, expected y”
ORA-2068 “following severe error from %s%s”
2. Make sure that the network is up and all db_links are valid and operational.
V$DBLINK AND GV$DBLINK show the db_links currently used by distributed
transactions:
SVRMGR> desc v$dblink
Column Name Null? Type
—————————— ——– —-
DB_LINK VARCHAR2(128)
OWNER_ID NUMBER
LOGGED_ON VARCHAR2(3)
HETEROGENEOUS VARCHAR2(3)
PROTOCOL VARCHAR2(6)
OPEN_CURSORS NUMBER
IN_TRANSACTION VARCHAR2(3)
UPDATE_SENT VARCHAR2(3)
COMMIT_POINT_STRENGTH NUMBER
SVRMGR> desc gv$dblink
Column Name Null? Type
—————————— ——– —-
INST_ID NUMBER
DB_LINK VARCHAR2(128)
OWNER_ID NUMBER
LOGGED_ON VARCHAR2(3)
HETEROGENEOUS VARCHAR2(3)
PROTOCOL VARCHAR2(6)
OPEN_CURSORS NUMBER
IN_TRANSACTION VARCHAR2(3)
UPDATE_SENT VARCHAR2(3)
COMMIT_POINT_STRENGTH NUMBER
3. Run the following query against DBA_2PC_PENDING:
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
FROM DBA_2PC_PENDING
WHERE LOCAL_TRAN_ID = ‘??.’; [Insert code here
LOCAL_TRAN_ID is the Local transactionID (number that is given when error
is reported). If LOCAL_TRAN_ID = GLOBAL_TRAN_ID, it means that this site
is the Global Coordinator, i.e. where the transaction originated from.
You can also get LOCAL_TRAN_ID from the alert.log.
4. Run the following query against DBA_2PC_NEIGHBORS view on all Nodes:
SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE
FROM DBA_2PC_NEIGHBORS;
5. Get the values for COMMIT_POINT_STRENGTH init.ora parameter on all Nodes.
If you are not aware of what other Nodes are participating in the distributed transaction, query the DBA_2PC_NEIGHBORS view. The Node with COMMIT_POINT_STRENGTH that has highest value in init.ora has to commit first.
6. Check the STATE column from DBA_2PC_PENDING.
If STATE column states COMMIT, it means the local database has successfully committed.
This also means you most likely do not have to force anything on that Node.
Or if other Nodes have not committed and this particular Node happens to be
a Global Coordinator – you need to note COMMIT# i.e. SCN number from
DBA_2PC_PENDING view, as this is THE SCN# you will need to use if you need
to perform COMMIT FORCE.
7. Take the GLOBAL_TRAN_ID and COMMIT# (SCN number from DBA_2PC_PENDING) and
compare it to the value on the other nodes.
On Nodes where there are no entries for this COMMIT# and transactionID in DBA_2PC_PENDING, RECO process has resolved the problem and we don’t have to do anything.
Note: RECO is always automatically started whenever Distributed Transactions are enabled.
If you issue a UNIX command : ps – ef | grep -i reco and see RECO running, distributed transactions are correctly enabled.
If you combine this output with values of the init.ora parameter DISTRIBUTED_TRANSACTONS (which should be higher than 0 for distributed environment), you may safely say that the customer is running in a distributed environment even if this particular customer has not set it up.
8. If the STATE (from DBA_2PC_PENDING view) is PREPARED, then this Node has not completed the transaction.
Take GLOBAL_TRAN_ID (from DBA_2PC_PENDING) and COMMIT# (SCN number) and compare it to the value on the other nodes.
You need to also check DBA_2PC_NEIGHBORS and see whether there are any other
Nodes that reference the same transactionID in their DBA_2PC_PENDING. If you
see it referenced, it means there are children and they need to be resolved
(either committed or rollback) as well. Please take ‘children’ i.e. Nodes
that other Nodes (not Global Coordinator) into account.
Note: for clarification on terminology please see Note 13229.1
9. If there are no children, it is safe to manually interfere and either commit force or rollback force.
For global integrity, use the same COMMIT# (SCN) when you force the transaction. To force the transaction please see SYNTAX NOTES below.
10. After the transaction is forced, make sure that both DBA_2PC_PENDING and DBA_2PC_NEIGHBORS data dictionary views are empty.
If for some reason it is necessary to clean those views, use packaged procedure DBMS_TRANSACTION.purge_lost_db_entry.
Note 1012842.102 ORA-2019 ORA-2058 ORA-2068 ORA-2050:
FAILED DISTRIBUTED TRANSACTIONS
_$#$_
Note: Be very careful with this procedure as it is always more reliable and more consistent to let RECO resolve distributed transaction conflicts. Please attempt to use DBMS_TRANSACTION.purge_lost_db_entry only as a
last resort after every other measure was implemented including restarting the database to resolve the locking conflict.
11. If even then there are entries in the views and errors still appear, please check for Bug 684157. This is fixed in 8.0.6 and higher.
Syntax Notes
COMMIT FORCE command
Example of syntax where highest committed SCN is 88123887 (from dba_2pc_pending) and local transaction ID is 1.13.5197 (from either dba_2pc_pending or alert.log):
SVRMGR> COMMIT FORCE ‘your local transactionID on this node’, ‘highest SCN from already committed site’;
SVRMGR> COMMIT FORCE ‘1.13.5197’, ‘88123887’;
ROLLBACK FORCE command
Example of syntax where local transactionID is 1.13.5197 (from either dba_2pc_pending or alert.log):
SVRMGR> ROLLBACK FORCE ‘your local transactionID on this node’;
SVRMGR> ROLLBACK FORCE ‘1.13.5197’;
PURGING VIEWS:
*
Example of syntax for lost local transactionID 1.13.5197 (from either dba_2pc_pending or alert.log).
NOTE: Run as SYS where 1.13.5197 is the local_tran_id
SVRMGR> Execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘1.13.5197’);
COMMIT;
*
Example of syntax for mixed local transaction ID 1.13.5197 (from either dba_2pc_pending or alert.log). Note, Run as SYS:
SVRMGR> Execute DBMS_TRANSACTION.PURGE_MIXED (‘1.13.5197’);
COMMIT;
Run PURGE_MIXED Procedure only if significant reconfiguration has occurred so that automatic recover (RECO) cannot resolve the transaction.
Example of appropriate cases when it can be used:
– Total loss of the remote database, or reconfiguration of software resulting in loss of two-phase commit capability.
NOTE: A ‘commit;’ should be issued after each DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY
and DBMS_TRANSACTION.PURGE_MIXED otherwise errors are given.
SCN Recovery Steps
Complete Recovery:
1. At the down site recover completely if possible (treat as regular recovery).
2. SCN will appear in the alert.log after recover is done on the crashed Node.
Incomplete Recovery:
1. If time-based or cancel-based recovery was used on the crashed node, other sites must be placed back to the same point in time for global consistency. Get last SCN from the alert.log of the crashed node.
2. At each node perform a shutdown normal or immediate.
3. Take a cold backup.
4. Restore the control file if necessary.
5. Restore the last backup of all datafiles along with archived redo logs.
6. Choose which tool to use to perform SCN recovery – either Server Manager line mode or RMAN.
7. Connect internal, startup mount, check status from v$datafile to make sure all datafiles are online. Issue ‘Alter database datafile ‘?/?/?’ online;’, for each datafile with status offline, to bring it online.
8. Issue the following command using the latest SCN from alert.log on the Node that had to be recovered:
RECOVER DATABASE UNTIL CHANGE ‘1.13.5197’;
NOTE: If for some reason (e.g.when issuing commit force command) automatic recovery (RECO process) needs to be disabled, use the following command to put RECO to sleep:
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
Comment