Error obtaining persistent identifier (ORA-08177: can't serialize access for this transaction)

[Chris Reeves]

On Wed 07 Sep 2016 at 19:19:26 +0100, Cantor, Scott wrote:
> On 9/7/16, 1:48 PM, Chris Reeves wrote:
> >
> > Caused by: java.io.IOException: java.sql.SQLException: ORA-08177: can't
> > serialize access for this transaction
> 
> Is that error code in your "retry" list? Other than forcing retries, there's
> nothing we can do to make transactions work, and that's the only allowance
> we can make. You can set any retryable errors you need to. Should be covered
> in the documentation, but...

Hi Scott,

Thanks for the pointer to this. For the record, we managed to resolve this by
adding SQLState 72000 to the list of retryable errors. Unfortunately that
SQLState ('SQL execute phase errors') corresponds to a large range of ORA-
error codes[1][2] so we're potentially retrying errors we don't need (or want)
to, however we decided we can live with that.

We now see the following in the our logs:
--------
2016-09-08T18:05:44.368+01:00 - WARN [net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx:616] - Stored Id Store: Caught retryable SQL exception
java.sql.SQLException: ORA-08177: can't serialize access for this transaction

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
2016-09-08T18:05:44.369+01:00 - INFO [net.shibboleth.idp.saml.nameid.impl.JDBCPersistentIdStoreEx:626] - Stored Id Store: Retrying persistent ID lookup/create operation
--------
and attribute resolution continues as normal.

I've added the above in a comment on the wiki page for the StoredIdConnector.

Thanks again for your help,
    Chris

> > We've configured a StoredId data connector in attribute-resolver.xml (we
> > also need the StoredId for eduPersonTargetedID)
> 
> I don't know if we have fully implemented the range of needed options in the
> resolver version, as that's deprecated (and it's the one "really deprecated"
> thing we have). I think some of the options were added but buggy, but I
> don't remember which ones. If you can't implement the retryable error list
> there, there's really nothing to be done about it but not using Oracle.
> 
> > There's no funky transaction isolation configured on the connector - we're
> > just taking whatever the defaults are.
> 
> That's the problem, Oracle's portable SQL defaults violate the spec for
> locking assumptions (and increasingly it appears every database may be in
> that category).
> 
> The other options around this (e.g. customizing the SQL to use "select for
> update") only work with the NameID Generation component, not the deprecated
> connector.
> 
> > We only have one active server at a time (the other is a hot standby which
> > our load balancer will fail over to if necessary) so there shouldn't be
> > any conflict between the two servers. I should also note that this is not
> > something we saw on v2.
> 
> V2's database code there didn't work across a cluster (which is probably why
> you didn't notice, you only had one hot node).

[1] https://docs.oracle.com/cd/B28359_01/appdev.111/b28428/pco08err.htm
[2] https://docs.oracle.com/cd/E15817_01/appdev.111/b31228/appd.htm