RE: ora-08177 on selects with isolation level serializable
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <amit.bansal82@xxxxxxxxx>, "'oracle-l L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Apr 2013 08:35:12 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
