JDBCTokenStore sometimes stores "too many" access tokens

Hi,

we have deployed an API of ours that uses spring security oauth 1.0.0RC2 and it's JDBCTokenStore. This API is used by various clients (some via spring security oauth's OAuth2Resttemplate, others via other HTTPClients like the .NET one).

Regardless of what client uses the provided Token Endpoint somehow sooner or later the situation arises where there are multiple access tokens in the database for the same user (or the same client when client-identifier scheme is used). This leads to random Error 500 codes and our main line of action is to manually delete all access tokens and request tokens from the database.

Is this a known bug or do we somehow miss-use the token store here?

Code:

---

2012-10-08 08:48:12.400::WARN:  /oauth/token
org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 4
        at org.springframework.dao.support.DataAccessUtils.requiredSingleResult(DataAccessUtils.java:74)
        at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:735)
        at org.springframework.security.oauth2.provider.token.JdbcTokenStore.getAccessToken(JdbcTokenStore.java:99)
        at org.springframework.security.oauth2.provider.token.DefaultTokenServices.createAccessToken(DefaultTokenServices.java:78)
        at org.springframework.security.oauth2.provider.token.AbstractTokenGranter.getAccessToken(AbstractTokenGranter.java:68)
        at org.springframework.security.oauth2.provider.token.AbstractTokenGranter.grant(AbstractTokenGranter.java:60)
        at org.springframework.security.oauth2.provider.CompositeTokenGranter.grant(CompositeTokenGranter.java:38)
        at org.springframework.security.oauth2.provider.endpoint.TokenEndpoint.getAccessToken(TokenEndpoint.java:93)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
        at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:147)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1148)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:322)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:184)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:155)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
        at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1148)
        at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:387)
        at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
        at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
        at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
        at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:417)
        at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
        at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
        at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
        at org.mortbay.jetty.Server.handle(Server.java:326)
        at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:534)
        at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:879)
        at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)
        at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
        at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
        at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
        at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:520)

---

On a side-note: Are there SQL-Scripts available to create the data scheme that is assumed by the token store? I manually analysed the default SQL-Statements and just created two tables that hold all the used columns but there are no keys or other constraints in use.

I haven't actually used the JdbcTokenStore much, but I never saw that error before. I guess there might be a race condition? You could protect against it (at the cost of an error in a different place) by adding a uniqueness constraint in your database of course.

The unit tests have SQL scripts for creating the tables. You can pull those out of the source code, but they are not in published jars.

If i get this right this means that JDBCTokenStore does not care for synchronization. If that's the case what guarantees are made by spring security oauth regarding synchronization in the token stores? I guess to get this right someone would have to synchronize in the database. I did such things in the past by use of statements like

Code:

---

select ... for update

---

.

Can you tell me how the logical flow is that uses the TokenStore so that I may be able to try an synchronizing implementation?

The only client of the TokenStore is the DefaultTokenServices. I guess it might be enough to declare the DefaultTokenServices to be transactional with a high isolation level - that would provide the database lock, and I think its interface ensures that concurrent accessors would just wait a bit longer and get the existing token.

If I understand this right Spring transactions can result in database locks? This would be nice. As to your suggestion I came up with this XML-Directives in the affected application context. Is that correct? Will it work?

Code:

---

<bean id="oauthTXManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="oauthDataSource"/>
    </bean>

    <tx:advice id="oauthTXAdvice" transaction-manager="oauthTXManager">
        <tx:attributes>
            <tx:method name="*" isolation="SERIALIZABLE"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="oauthTokenServiceOperations"
                      expression="execution(* org.springframework.security.oauth2.provider.token.DefaultTokenServices.*(..))"/>
        <aop:advisor advice-ref="oauthTXAdvice" pointcut-ref="oauthTokenServiceOperations"/>
    </aop:config>

    <bean id="oauthTokenServices" class="org.springframework.security.oauth2.provider.token.DefaultTokenServices">
        <property name="tokenStore" ref="jdbcTokenStore"/>
        <property name="supportRefreshToken" value="true"/>
    </bean>

    <bean id="jdbcTokenStore" class="org.springframework.security.oauth2.provider.token.JdbcTokenStore">
        <constructor-arg ref="oauthDataSource"/>
    </bean>

---

Yes, that should do it. Whether or not you get a lock depends on the RDBMS and driver (i.e. nothing to do with Spring).

I know this is no concern of spring security oauth but could you please point me to some documentation/keywords on the prerequisites for this to work? We are using the official mysql jdbc driver via commons-dbcp basic datasource.

Is the isolation level of "SERIALIZABLE" the right one?

-edit: OK I verified the behaviour with the advice present and without. Without the advice 5 concurrent requests for a new access token lead to 5 different inserted access tokens. With the advice 5 concurrent requests lead to only one access token being inserted. So your suggestion works.

I read the mysql manual regarding transaction isolation and SERIALIZE is the right value because the default is REPEATABLE_READ and this is only one level below SERIALIZE (as far as mysql is concerned). SERIALIZE seems to work because it modifies every select statement to result in a table level lock. Of course this has performance costs...

@Dave: Thanks for suggesting that the root issue might be race conditions and for telling me about Springs transaction isolation. Users of the JDBCTokenstore should be informed of the non-synchronization in a default setup though.

Would it be sufficient to only advise DefaultTokenServices#createAccessToken() with this high transaction isolation level? As far as I understand the code this method both checks for the existence of an access token and creates it if there is none found. This would free all other (read only) calls from unnecessary locks, right?

Yes, that makes sense. Good suggestion. Feel free to add a few lines to the wiki.