SELECTs across DBLinks locking rollback segs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SELECTs across DBLinks locking rollback segs

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    SELECTs across DBLinks locking rollback segs

    Came across this is a Distributed Systems Doc:

    When you issue a SELECT statement across a database link, a transaction lock is placed on the rollback segment. To release the segment you must issue a COMMIT or ROLLBACK.

    e.g.

    SELECT a, b, c
    FROM table1@link;

    COMMIT;
    Why? I can understand how, maybe, a 'SELECT FOR UPDATE' might lock rollback, but why a 'simple' SELECT?

    BTW - The quote is from the Ora9.2 guide to 'Developing Applications for a Distributed System'.

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    To provide a consistant image in case of network glitches?

    I am guessing.
    I remember when this place was cool.

  3. #3
    Join Date
    Dec 1999
    Posts
    217
    Your guess is correct. It is to maintain the read consistency aspect.

    Chintz

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    *Locking* of rollback segments in distributed selects has nothing to do with read consistency. Just think about it, how would it serve for that purpose? For mantaining read consistency, Oracle has to obtain a before immage of the data that has been changed since the query began. It finds those information in rollback segments. But in which one? It can't know that in advance! It can be just any or all of them. So would it lock all the rollback/undo segments? In which database? Local? Remote? Both?

    It locks only one undo segment, not all of them. On local database, of course. And it's not for the support of read consistency, it's required for the purpose of two-phase commit (even on selects, not only for distributed changes).

    Read the explanation here:
    http://www.jlcomp.demon.co.uk/faq/dblink_commit.html
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Good stuff Jm, but couldn't a slow network cause the same problem, waiting on a commit?
    I remember when this place was cool.

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Sure enough the ALERT log shows Undo segs being onlined and offlined to cope with all this. Good call JM - the link explained it perfectly and the example works like a dream.

    It doesn't lock existing UNDO, rather creating new segments every time a SELECT opens a query over the link, for the duration of that 'transaction'.

    Who'd a thunk it, eh?

    Do you think 99.999999999% of developers (or even 90% of DBA's!!!) would know about this?

    Let's just shoot everyone.
    Last edited by JMac; 01-20-2005 at 06:30 AM.

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Originally posted by JMac

    Let's just shoot everyone.
    I like the way you think.
    I remember when this place was cool.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width