An TX lock implies that the session have an slot in
the rollback segment. TX are usually exclusive lock: 6
code.
Regards.
- "Adams, Matthew (GEA, 088130)"
<MATT.ADAMS_at_APPL.GE.COM> wrote:
> Is the TX lock against the local rollback segment
> a shared or exclusive lock?
>
> Matt
>
> ----
> Matt Adams - GE Appliances - matt.adams_at_appl.ge.com
> "Doing linear scans over an associative array is
> like
> trying to club someone to death with a loaded Uzi."
> - Larry Wall (creator of Perl)
>
> > -----Original Message-----
> > From: Jared.Still_at_radisys.com
> [mailto:Jared.Still_at_radisys.com]
> > Sent: Thursday, November 08, 2001 12:08 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: blowing out rollbacks on select
> statements?
> >
> >
> >
> > Christian,
> >
> > I read the note on MetaLink. Is it just me, or
> does the explanation
> > given just not make any sense?
> >
> > Jared
> >
> > Doc ID:
> > Note:74811.1
> >
> > PURPOSE
> > To explain why a distributed query uses local
> rollback segments.
> >
> > SCOPE & APPLICATION
> > For DBA's and Oracle Support Analysts.
> >
> > RELATED DOCUMENTS
> > [BUG:702754]
> > [BUG:262277]
> >
> > When a select from a remote table is issued a TX
> lock out is taken
> > out on a local rollback segment. This lock is
> only released
> > by issuing a COMMIT, ROLLBACK or logoff.
> >
> > For example:
> >
> > select empno,ename from emp_at_remotedb;
> >
> > This is documented behaviour in [BUG:262277].
> >
> > The explanation given is:
> >
> > "When we start a transaction which is to be
> executed remotely, since
> > at that time we are not sure what is going to
> happen locally while
> > the query is being executed remotely, a TX lock
> is taken out on
> > a local rollback segment even if the query is
> only a select."
> >
> > The reason for this is that a remote select is
> like a two
> > phase commit
> > in that it sends out the query over the network
> and waits for it to
> > return a successful result. If you check
> v$transaction during the
> > transaction, you will see used_ublk show 1 block
> of rollback
> > being used
> > during this update and a TX lock against the
> rollback segment.
> >
> > The following is an example of a remote query
> using a local rollback
> > segment:
> >
> > SQL> SELECT t.xidusn "RBS Name",
> > t.used_ublk "Used Blocks",
> > s.username "User Name",
> > l.type "Lock Type"
> > FROM v$transaction t,
> > v$session s,
> > v$lock l
> > WHERE t.ses_addr=s.saddr
> > AND s.sid = l.sid
> > /
> >
> > no rows selected
> >
> > SQL> SELECT * FROM scott.emp;
> >
> > ENAME SALARY
> > ---------- ----------
> > Mike 1000
> > Nick 500
> > Pete 400
> > Dave 2000
> >
> > SQL> SELECT t.xidusn, t.used_ublk, s.username,
> l.type
> > 2> FROM v$transaction t, v$session s, v$lock l
> > 3 WHERE t.ses_addr=s.saddr AND s.sid = l.sid ;
> >
> > no rows selected
> >
> > SQL> SELECT * FROM scott.emp_at_suppaix1_v805;
> >
> > ENAME SALARY
> > ---------- ----------
> > Mike 1000
> > Nick 500
> > Pete 400
> > Dave 2000
> >
> > SQL> SELECT t.xidusn, t.used_ublk, s.username,
> l.type
> > 2> FROM v$transaction t, v$session s, v$lock l
> > 3> WHERE t.ses_addr=s.saddr AND s.sid = l.sid
> > 4 /
> >
> > XIDUSN USED_UBLK USERNAME
> TYPE
> > ---------- ---------- --------
> -----
> > 3 1 SCOTT
> TX
> >
> > SQL> COMMIT;
> >
> > Commit complete.
> >
> > SQL> SELECT t.xidusn, t.used_ublk
> > 2> FROM v$transaction t, v$session s
> > 3> WHERE t.ses_addr=s.saddr AND
> s.username='SYSTEM'
> > 4 /
> >
> > no rows selected
> >
> >
> >
> >
>
> >
>
> > Christian
>
> >
>
> > Trassens To:
> Multiple
> > recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>
> > <ctrassens_at_yah cc:
>
> >
>
> > oo.com> Subject:
> Re:
> > blowing out rollbacks on select statements?
>
> > Sent by:
>
> >
>
> > root_at_fatcity.c
>
> >
>
> > om
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > 11/08/01 08:10
>
> >
>
> > AM
>
> >
>
> > Please respond
>
> >
>
> > to ORACLE-L
>
> >
>
> >
>
> >
>
> >
>
> >
>
> >
> >
> >
> >
> > Distributed queries use rollback segment. Check
> this
> > note:
> >
> > Doc ID:
> > Note:74811.1
> >
> > Remote SELECT issues TX LOCK and uses Local
> Rollback
> >
> > Type:
> > BULLETIN
> >
> > Status:
> > PUBLISHED
> >
> > Content Type:
> > TEXT/PLAIN
> >
> > Creation Date:
> > 08-OCT-1999
> >
> > Last Revision Date:
> > 09-OCT-2000
> >
> >
> > PURPOSE
> > To explain why a distributed query uses local
> rollback
> > segments.
> >
> > SCOPE & APPLICATION
> > For DBA's and Oracle Support Analysts.
> >
> > RELATED DOCUMENTS
> > [BUG:702754]
> > [BUG:262277]
> >
> > When a select from a remote table is issued a TX
> lock
> > out is taken
> > out on a local rollback segment. This lock is only
> > released
> > by issuing a COMMIT, ROLLBACK or logoff.
> >
> > For example:
> >
> > select empno,ename from emp_at_remotedb;
> >
> > This is documented behaviour in [BUG:262277].
> >
> > The explanation given is:
> >
> > "When we start a transaction which is to be
> executed
> > remotely, since
> > at that time we are not sure what is going to
> happen
> > locally while
> > the query is being executed remotely, a TX lock is
> > taken out on
> > a local rollback segment even if the query is only
> a
> > select."
> >
> > The reason for this is that a remote select is
> like a
> > two phase commit
> > in that it sends out the query over the network
> and
> > waits for it to
> > return a successful result. If you check
> > v$transaction during the
> > transaction, you will see used_ublk show 1 block
> of
> > rollback being used
> > during this update and a TX lock against the
> rollback
> > segment.
> >
> > The following is an example of a remote query
> using a
> > local rollback
> > segment:
> >
> > SQL> SELECT t.xidusn "RBS Name",
> > t.used_ublk "Used Blocks",
> > s.username "User Name",
> > l.type "Lock Type"
> > FROM v$transaction t,
> > v$session s,
> > v$lock l
> > WHERE t.ses_addr=s.saddr
> > AND s.sid = l.sid
> > /
> >
> > no rows selected
> >
> > SQL> SELECT * FROM scott.emp;
> >
> > ENAME SALARY
> > ---------- ----------
> > Mike 1000
> > Nick 500
> > Pete 400
> > Dave 2000
> >
> > SQL> SELECT t.xidusn, t.used_ublk, s.username,
> l.type
> > 2> FROM v$transaction t, v$session s, v$lock l
> > 3 WHERE t.ses_addr=s.saddr AND s.sid = l.sid ;
> >
> > no rows selected
> >
> > SQL> SELECT * FROM scott.emp_at_suppaix1_v805;
> >
> > ENAME SALARY
> > ---------- ----------
> > Mike 1000
> > Nick 500
> > Pete 400
> > Dave 2000
> >
> > SQL> SELECT t.xidusn, t.used_ublk, s.username,
> l.type
> > 2> FROM v$transaction t, v$session s, v$lock l
> > 3> WHERE t.ses_addr=s.saddr AND s.sid = l.sid
> > 4 /
> >
> > XIDUSN USED_UBLK USERNAME
> > TYPE
> > ---------- ---------- --------
> > -----
> > 3 1 SCOTT
> > TX
> >
> > SQL> COMMIT;
> >
> > Commit complete.
> >
> > SQL> SELECT t.xidusn, t.used_ublk
> > 2> FROM v$transaction t, v$session s
> > 3> WHERE t.ses_addr=s.saddr AND
> s.username='SYSTEM'
> > 4 /
> >
> > no rows selected
> >
> >
> >
> > --- Doug C <dcowles_at_i84.net> wrote:
> > > This is Oracle 8.1.7 -
> > > Unless I'm getting bad information, a developer
> has
> > > passed me some queries
> > > that use a database link in them. They are
> select
> > > queries, albeit complicated
> > > ones with lot of outer joins and such. Both
> db's
> > > are 8i but I think the remote
> > > db is 8.1.6. Getting snapshot too old :
> roolback
> > > segment number whatever is
> > > too small. To me that means, that the select
> query
> > > is going after tables that
> > > are in the middle of being udpdated and losing
> there
> > > ability to find a
> > > consistent image before they are done. So,
> either
> > > the quries are just plum
> > > taking too much information and time and another
> > > transaction that I have no
> > > control over is busy updating the tables at the
> same
> > > time..
> > >
> > > Agree?
> > >
> > > - Doug
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: Doug C
> > > INET: dcowles_at_i84.net
> > >
> > > Fat City Network Services -- (858) 538-5051
> FAX:
> > > (858) 538-5051
> > > San Diego, California -- Public Internet
> > > access / Mailing Lists
> > >
> >
>
> > > To REMOVE yourself from this mailing list, send
> an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing:
> UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be
> removed
> > > from). You may
> > > also send the HELP command for other information
> > > (like subscribing).
> >
> >
> > =====
> > ENG. Christian Trassens
> > Senior DBA
> > ctrassens_at_yahoo.com
> > christian_trassens_at_yahoo.es
> > Phone : +34-699240979
> > +34-649824704
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Find a job, post your resume.
> > http://careers.yahoo.com
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Christian Trassens
> > INET: ctrassens_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author:
> > INET: Jared.Still_at_radisys.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
> >
>
ENG. Christian Trassens
Senior DBA
ctrassens_at_yahoo.com
christian_trassens_at_yahoo.es
Phone : +34-699240979
+34-649824704
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: ctrassens_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri Nov 09 2001 - 03:40:30 CST
