Re: RE: Deceptive commit after select from dblink.
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: Sayan Sergeevich Malakshinov <malakshinovss@xxxxxxxxx>
- Date: Fri, 19 Oct 2012 00:54:20 -0700 (PDT)
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@xxxxxxxxx>
To: saibabu_d@xxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx>          Â
                         Â
               |
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Ð?омÑ?:   |
|------------>
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
 |free <oracle-l@xxxxxxxxxxxxx>              Â
                         Â
                 |
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Ð?аÑ?а:   |
|------------>
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
 |16.10.2012 10:15                   Â
                         Â
                   |
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Тема:   |
|------------>
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
 |RE: Deceptive commit after select from dblink.         Â
                         Â
              |
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Ð?Ñ?:Â Â Â Â |
|------------>
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
 |oracle-l-bounce@xxxxxxxxxxxxx              Â
                         Â
                 |
Â
>--------------------------------------------------------------------------------------------------------------------------------------------------|
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
