Re: RE: Deceptive commit after select from dblink.

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Fri, 19 Oct 2012 00:54:20 -0700 (PDT)
Message-ID: <1350633260.91041.YahooMailNeo_at_web161301.mail.bf1.yahoo.com>

Sayan,
After the commit or rollback on the local database for select query over dblink, v$session.taddr is reset to null on the remote database session, this confirm that Oracle cleared transaction entry for the session, but I think the session->transaction association remains (and hence why you are seeing an entry in x$ktcxb) should the session need to create transaction again and in this case it will use the same transaction spot in the transaction array.

1. I think commit or rollback is needed on remote db to clear the transaction (even though there was no real DML happened) if the local session commit/rollback after the query over dblink. Another thing to remember is SCN resets to higher of the two databases during the first query execution over dblink, this SCN reset can't happen on the read only database or active data guard standby.
2. I don't think there is any difference between commit and rollback on the read only database.

Thanks,
 Sai
http://sai-oracle.blogspot.com

---

 From: Sayan Sergeevich Malakshinov <malakshinovss_at_psbank.ru> To: saibabu_d_at_yahoo.com
Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Thursday, October 18, 2012 7:01 AM Subject: RE: RE: Deceptive commit after select from dblink.  

Sai,
thanks for good point about active standby and read-only databases. And you are right: after "select from dblink" in x$ktcxb on remote i see row without slot(kxidusn=0,kxidslt=0,kxidsqn=0,BITAND(ktcxbflg,2)=0 ) and regardless of whether the commits/rollbacks, the row does not disappear until disconnect. Even if was commit after any dml(slot created, flag changed), row stil exists without slot again.

But i have doubts:
1. So why commit(rollback) is needed after simple query from remote, if there are no differences whether the commits/rollbacks or not? 2. What differences between commit and rollback on read-only db?

PS. Really "read-only rollbacks" have no effect in "user commits"/"user rollbacks" statistics. But the same can be on local only too: http://pastebin.com/NDxdssqn

--
Best regards,
Sayan Malakshinov
http://orasql.org 




|------------>

| От:        |


|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>                                                                                                        |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|


|------------>

| Кому:      |


|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |free <oracle-l_at_freelists.org>                                                                                                                     |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|


|------------>

| Дата:      |


|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |16.10.2012 10:15                                                                                                                                  |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|


|------------>

| Тема:      |


|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |RE: Deceptive commit after select from dblink.                                                                                                    |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|


|------------>

| От:        |


|------------>

  >--------------------------------------------------------------------------------------------------------------------------------------------------|
  |oracle-l-bounce_at_freelists.org                                                                                                                     |
  >--------------------------------------------------------------------------------------------------------------------------------------------------|





Sayan,
Here is what I guess explains what you observed:

Whether we call it distributed transaction or not, a simple query over
dblink will require transaction to be created on the local and the remote
database, this is needed for transaction recoverabilty if there is any
failure on local ore remote database,  but to make it work for read only
standby databases, I suspect Oracle changed remote database to only create
transaction in the transaction state array but no transaction will appear
in the undo segment transaction table unless if there is any DML.

I believe, it is for this reason, a commit on the local database trigger
rollback on the remote database first when there were no DML's on the
remote database since commit with session having an entry in transaction
state object probably require corresponding undo segment entries. By the
way, I didn't observe rollback or commit statistic values going up on the
remote database session statistics, it is weird that it appeared in the
trace file only.

Whenever any DML happens on the remote database over dblink, transaction
table in the undo segment will have an entry and hence commit or rollback
on the local database can have similar effect on the remote database too.

Thanks,
 Sai
http://sai-oracle.blogspot.com 

--
http://www.freelists.org/webpage/oracle-l 

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 19 2012 - 09:54:20 CEST