ora-08177 on selects with isolation level serializable
Date: Wed, 3 Apr 2013 10:25:49 +0530
Message-ID: <CAB8JqBYEXicf6otfrBd9i=BTRk1+PRL-+v0ut1XjTsqPe4dM2A_at_mail.gmail.com>
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-lReceived on Wed Apr 03 2013 - 06:55:49 CEST