Home » Server Options » Replication » Locks Generating during Two Phase Commits
| Locks Generating during Two Phase Commits [message #75403] |
Mon, 03 November 2003 23:06  |
K.K. Raj kumar
Messages: 33
Registered: July 2002
|
Member |
|
|
Hello Everybody,
We have 2 Oracle Datbase Servers. Data Entry will be done in First Server and after completion of the Data Entry, the final data will be inserted in the Second Server and deleted in the First Server. (We are using Database link for this purpose). Now, the problem what we are facing is some times LOCKs are being generated and the process just hangs.
I have used the following script to see the locks in the database of the First Server:
/* This script is an essential tool for any DBA when it comes to identifying
locking problems. It displays the sessions that are currently holding locks
and sessions that are waiting for a lock.
Locks are identified as either transaction (TX), data manipulation (DML),
user-defined (USR), or system locks (SYS). The script indicates which
objects are affected by DML locks and identifies the blocking session
when a session is waiting for a lock.
The script will generate a report for a specified username or report on
all sessions. It has been regularly used on Oracle 7.1.6 and 7.3 databases.
*/
REM Purpose
REM -------
REM Display locks currently held and requested. Displays which session a
REM blocked lock is waiting for.
REM
REM Ver Who When What
REM --- --- ---- ----
REM 1.0 DrB 12-Dec-97 Initial version
col uname head "Username" form a12
col sid head "SID" form 999
col ltype head "Type" form a4
col lmode head "Mode" form a10
col blocked head "Wait" form a4
col details head "Details" form a40
set verify off
accept user prompt "Username [[%]]: "
select s.sid sid, s.username uname, 'DML' ltype,
decode (l.lmode,1,'Null',
2,'Row-S',
3,'Row-X',
4,'Share',
5,'S/Row-X',
6,'Exclusive') lmode,
decode (l.request,0,'No','Yes') blocked,
u.name||'.'||o.name details
from v$session s, v$lock l, sys.obj$ o, sys.user$ u
where s.username like nvl(upper('&user'||and s.sid = l.sid
and l.id1 = o.obj#
and l.type = 'TM'
and o.owner# = u.user#(+)
union all
select s.sid sid, s.username uname,
decode (l.type,'TX','TX',
'UL','USR',
'SYS') ltype,
decode (l.lmode,1,'Null',
2,'Row-S',
3,'Row-X',
4,'Share',
5,'S/Row-X',
6,'Exclusive') lmode,
decode (l.request,0,'No','Yes') blocked,
decode (l.request,0,null,'Waiting on session '||to_char(b.sid)) details
from v$session s, v$lock l, v$lock b
where s.username like nvl(upper('&user'||'%'),'%')
and s.sid = l.sid
and l.type != 'TM'
and l.id1 = b.id1(+)
and b.request(+) = 0
'%'),'%') order by 5 desc,3 desc,2,1;
set verify on
REM End of file
and the Output of the above script is :
SQL> /
old 11: where s.username like nvl(upper('&user'||'%'),'%')
new 11: where s.username like nvl(upper(''||'%'),'%')
old 30: where s.username like nvl(upper('&user'||'%'),'%')
new 30: where s.username like nvl(upper(''||'%'),'%')
SID Username Type Mode Wait Details
---- ------------ ---- ---------- ---- ----------------------------------------
24 OPS$S_DEO05 TX Exclusive No
45 OPS$S_DEO07 TX Exclusive No
22 OPS$S_DEO08 TX Exclusive No
44 OPS$S_DEO09 TX Exclusive No
20 OPS$S_DEO10 TX Exclusive No
43 OPS$S_DEO11 TX Exclusive No
19 OPS$S_DEO13 TX Exclusive No
31 OPS$S_DEO14 TX Exclusive No
26 OPS$S_DEO16 TX Exclusive No
16 SIMP01 TX Exclusive No
35 SIMP05 TX Exclusive No
39 SIMP07 TX Exclusive No
27 SIMP08 TX Exclusive No
18 SIMP18 TX Exclusive No
25 SIMP22 TX Exclusive No 33 SUBI02 TX Exclusive No
15 SYSTEM TX Exclusive No
25 SIMP22 SYS Null No
33 SUBI02 SYS Null No
SID Username Type Mode Wait Details
---- ------------ ---- ---------- ---- ----------------------------------------
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_CONTAINER
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_DBK
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_DEEC
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_DEPB
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_DEPB_PARENT
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_DFRC
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_EOU_REP
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_EXCHANGE
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_INVOICE
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_ITEMS
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_ITEM_RAW_MTRL
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_JOB_WORK
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_PCKT_LIST
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_SB
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_SB_ROTN 24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_VOYAGE
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_CONTAINER
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_EOU_REP
SID Username Type Mode Wait Details
---- ------------ ---- ---------- ---- ----------------------------------------
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_PCKT_LIST
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_SB
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_SB_ROTN
22 OPS$S_DEO08 DML Row-X No OPS$EXP.S_VOYAGE
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_CONTAINER
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_EOU_REP
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_PCKT_LIST
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_SB
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_SB_ROTN
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_SB_STAT
44 OPS$S_DEO09 DML Row-X No OPS$EXP.S_VOYAGE
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_CONTAINER
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_EOU_REP
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_EXCHANGE
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_INVOICE
20 OPS$S_DEO10 20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_SB_ROTN
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_SB_STAT
SID Username Type Mode Wait Details
---- ------------ ---- ---------- ---- ----------------------------------------
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_VOYAGE
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_CONTAINER
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_EOU_REP
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_PCKT_LIST
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_SB
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_SB_ROTN
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_SB_STAT
19 OPS$S_DEO13 DML Row-X No OPS$EXP.S_VOYAGE
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_CONTAINER
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_EOU_REP
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_EXCHANGE
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_INVOICE
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_PCKT_LIST
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_SB
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_SB_ROTN
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_SB_STAT
31 OPS$S_DEO14 DML Row-X No OPS$EXP.S_VOYAGE
16 SIMP01 DML Row-S No OPS$IMPORTS.SITEM_DET DML Row-X No OPS$EXP.S_PCKT_LIST
20 OPS$S_DEO10 DML Row-X No OPS$EXP.S_SB
24
24 OPS$S_DEO05 DML Row-X No OPS$EXP.S_SB_STAT
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD
SID Username Type Mode Wait Details
---- ------------ ---- ---------- ---- ----------------------------------------
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD_CONDON
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD_IGM
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD_ITEM
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD_STAT
25 SIMP22 DML Row-X No OPS$IMPORTS.S_BD_VISIT
33 SUBI02 DML Row-X No OPS$IMPORTS.PEXCHANGE
33 SUBI02 DML Row-X No OPS$IMPORTS.SBE
33 SUBI02 DML Row-X No OPS$IMPORTS.SBE_DEPB
33 SUBI02 DML Row-X No OPS$IMPORTS.SINV
33 SUBI02 DML Row-X No OPS$IMPORTS.SITEM_DET
33 SUBI02 DML Row-X No OPS$IMPORTS.SLIC
You can see that SYS Null type of lock is being generated.
Any guidance to solve this problem would be appreciated.
With Advance Thanks
Regards
K.K. Raj kumar
|
|
|
|
| Re: Locks Generating during Two Phase Commits [message #75409 is a reply to message #75403] |
Thu, 13 November 2003 07:24  |
James
Messages: 120
Registered: June 2000
|
Senior Member |
|
|
K.K.
The two phase commit, has to do with the fact that the original transaction on the first site, can not commit until the transaction on the second side commit, but it will not commit unless a commit is done on the first site. This puts the transaction into a indoubt state, and will cause the table on the first site to be unable for accessing. This problem exists in 8i and 9i, but is fixed in 10g...
One idea, is to place a staging table that contains the commit record rowid from the first servers transaction (the rowids can be inserted into the staging table with an after trigger). Then write a stored proc to read this table, and apply the transaction to the second site. This would separate the transactions and avoid the 2pc issue. A DBMS_JOB entry could be scheduled to run every so often (every 3 seconds for example) to execute the push proc.
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 18:42:47 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
