JDBC plugin and localLocking

[Nathan C Lewan]

hello all,

We are experiencing some issues in our environment revolving around a
postgres database used for CAS session information. We recently
switched from Hibernate to the JDBCStorageService, and are seeing many
retry exceptions. We have at least two load balanced Shibboleth 4 IdP
instances writing to the postgres db at any time.

Here is the error:

**********
ERROR
[net.shibboleth.idp.cas.flow.impl.GrantServiceTicketAction:227]
Profile Action GrantServiceTicketAction: Failed granting service ticket due
to error.
java.lang.RuntimeException: Failed to store ticket ST-<ticket> at
net.shibboleth.idp.cas.ticket.impl.AbstractTicketService.store(AbstractTicketService.java:189)
Caused by:
java.io.IOException: org.postgresql.util.PSQLException: ERROR: could not
serialize access due to read/write dependencies among transactions Detail:
Reason code: Canceled on identification as a pivot, during commit attempt.
Hint: The transaction might succeed if retried. at
net.shibboleth.plugin.storage.jdbc.impl.JDBCStorageService.create(JDBCStorageService.java:595)
Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize
access due to read/write dependencies among transactions Detail: Reason
code: Canceled on identification as a pivot, during commit attempt. Hint:
The transaction might succeed if retried. at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
******

We are considering turning on localLocking in the JDBCStorage service, as
well as configuringing the maxConnLifetimeMillis (change setting to 30min)
and validationQueryTimeout (change setting to 5 sec) properties in the
Commons DBCP Pool configuration.

Has anyone had any experience with enabling any of these properties? Any
performance/other issues, etc?

here is our JDBCStorageService configuration

***********
<bean id="custom.JDBCStorageService"
      parent="shibboleth.JDBCStorageService"
      p:cleanupInterval="%{idp.storage.cleanupInterval:PT10M}"
      p:dataSource-ref="umd.JDBCStorageService.DataSource"/>

<bean id="custom.JDBCStorageService.DataSource"
      class="org.apache.commons.dbcp2.BasicDataSource"
destroy-method="close" lazy-init="true"
      p:driverClassName="org.postgresql.Driver"
      p:url="<url>"
      p:password="<pass>" />
***********

Thanks for any insights
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shibboleth.net/pipermail/users/attachments/20230103/b1c5ec51/attachment.htm>