RE: ora-08177 on selects with isolation level serializable

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 3 Apr 2013 08:35:12 -0400
Message-ID: <035201ce3067$b079cc60$116d6520$_at_rsiz.com>

I think that implicit in what you have written is a claim that the
"metadata_table" has not been updated in a time relevant to things that are
being changed in various transactions taking place.

IF that is true, the only thing that occurs to me is delayed block cleanout. IF that, in turn, is correct, and the tablespace(s) containing
"metatdata_table" are not read only, then reading all the blocks of
"metadata_table" into the buffer cache should perform such cleaning.

Now, I do not know whether there is an index on the column "id", but if there is not, or if despite there being such an index the optimizer is selecting a full table scan, there is a possibility you are getting adaptive direct reads, bypassing the buffer cache, so the cleaning of the block then takes place in the pga only (for read consistency) and the cleaned out block is not written back (that is, it remains needing delayed block cleanout.)

SO, IF (and when I write "IF" in all caps, I'm trying to indicate that I'm way out on a limb entering into surmise and speculation with little evidence - call it a hunch) that is the case, then you need, prior to your whole test, to read blocks into the buffer cache (not just some session's pga).

Recreating the table might also leave you will all the blocks cleaned out.

You also wrote "this block" apparently regarding select * from metadata_table where id>0, so is metadata_table really only one block? I must repeat that I'm a bit unclear about your situation from your description of it. It seems strange to me that Oracle would embark on adaptive direct read for a table of only one block.

Regards,

Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of amit bansal
Sent: Wednesday, April 03, 2013 12:56 AM To: oracle-l L
Subject: ora-08177 on selects with isolation level serializable

hi All,
We are facing issues during our regression testing where in selects are failing with ORA-08177 (11.2.0.3 single instance on x86_64 bit)

08177, 00000, "can't serialize access for this transaction"

// *Cause:   Encountered data changed by an operation that occurred after
//           the start of this serializable transaction.
// *Action:  In read/write transactions, retry the intended operation or
//           transaction.

So our application launches test and executes following sql

 Set transaction isolation level serializable; select * from metadata_table where id>0;

We saw there were 169 ITL transactions in this block. Since this was metadata table, we were sure that no updates were happening. we also enabled auditing to verify the fact. There is oracle SR opened for this issue but Oracle is saying this is not bug and have asked to reproduce this issue by recreating table with initrans of 255 for this table.

Questions

---

a)Shouldn't we be getting enq: TX - allocate ITL entry wait event if this issue was due to inittrans.
Anyone has any idea what is going on?
b)Any tool (free) which can help me fire concurrent session with same sql to reproduce it.

Btw deferred segment creation bug is ruled out in this case as we have disabled the feature.

Regards
Amit

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 03 2013 - 14:35:12 CEST