| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: seen a Select (just a select no select for update) take out a Table Exclusive lock (TX)
There really aren't any Oracle level distributed transactions in SAP...
Thanks!
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfson Larry -
lwolfs
Sent: Thursday, January 26, 2006 3:12 PM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org
Subject: RE: seen a Select (just a select no select for update) take out
a Table Exclusive lock (TX)
Waiting to hear more but he did say it's 9206 & SAP 4.6B and it wasn't a distributed tranasaction.
Thanks
Larry
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, January 26, 2006 4:04 PM
To: oracle-l_at_freelists.org
Subject: Re: seen a Select (just a select no select for update) take out
a Table Exclusive lock (TX)
We need more details about the TX enqueue - it's a transaction enqueue, not a table enqueue by the way, and although they most commonly appear because you have changed data, assuming that this is the case may take you looking in the wrong direction.
One reason why a session executing a select state might attempt to acquire a TX enqueue is if the table in question is subject to distributed transactions, hence two-phase commit.
Between the prepare and commit of a two phase commit, the state of a data block should be treated as indeterminate, so any query that starts in that time-window should not be allowed to see the old version of the block in case the distributed transaction has successfully committed, nor should it be allowed to see the new version of the block in case the distributed transaction has rolled back - so it has to acquire a TX mode 4 (share) lock on the TX slot of the distributed transaction and wait for the two-phase commit to complete.
NB Prior to about 9.2.0.6, this was the behaviour exhibited by Oracle in all cases of distributed transactions. In 9.2.0.6, Oracle rewrote the code, and a query against a block modified by an incoming XA transaction gets to see the earlier version of the block, although an Oracle-only incoming transaction still results in the enqueue appearing.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 10th Jan 2006
From one of the other DBAs here:
"Have any of you ever seen a Select (just a select no select for update) take out a Table Exclusive lock (TX) ? we had one in SAP this morning on ZPICK01 and the developers assure me that the transaction doesn't try to update the table. The only thing I can think of, is that ZPICK01 is huge and that Oracle tried to ensure some form of Read Consistency by making the table TX. That said I'm still not certain that some switch in the transaction isn't set to lock the table. Thoughts ?"
Gee, I haven't worked much on SAP lately, but I believe tables prefixed Y and Z are custom tables, although I don't see why that would make a difference.
Thanks
Larry
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
Thank you.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 26 2006 - 17:41:00 CST
 |
 |