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'.
*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).
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?
Reply With Quote
Bookmarks