Write deadlocks on federated users

tldr; Simultaneous calls to query for federated users roles are ultimately causing a write deadlock on the user entity in the Keycloak database

My Setup

I have a Keycloak 12.0.2 distribution running in standalone mode, backed by a single MySQL database. Further I am using a User Storage SPI to provide users from a second external database. Login for those users works as expected, so does querying those users in the admin console.

What I want to do

The users provided by my User Storage SPI are purely identities without any preexisting authorities. I want to manage my authorization (role management, groups, etc.) completely in Keycloak, through the admin console. Functionally that is also possible, but…

My problem

When I try to edit user roles - in the users ‘Role Mappings’ tab for example - I get error messages in backend and frontend.

What happens

The client sends a bunch of requests to query for existing roles on the user:

http://<host>:<port>/auth/admin/realms/<realm>/users/f:0deb06e1-54ee-4a41-85c6-28d8a2bef3cc:02DJUNGD80000000VUM14007VTFEC6JF/role-mappings/realm
http://<host>:<port>/auth/admin/realms/<realm>/users/f:0deb06e1-54ee-4a41-85c6-28d8a2bef3cc:02DJUNGD80000000VUM14007VTFEC6JF/role-mappings/realm/available
http://<host>:<port>/auth/admin/realms/<realm>/users/f:0deb06e1-54ee-4a41-85c6-28d8a2bef3cc:02DJUNGD80000000VUM14007VTFEC6JF/role-mappings/realm/composite
http://<host>:<port>/auth/admin/realms/<realm>/users/f:0deb06e1-54ee-4a41-85c6-28d8a2bef3cc:02DJUNGD80000000VUM14007VTFEC6JF/role-mappings/clients/28ee65f8-dd0e-43f4-bdf2-39305fa859b9

These are processed in parallel, all “resetting” the user in the Keycloak database. As they are all writing attributes on that user simultaneously, I will get a deadlock error basically guaranteed.

18:42:28,879 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-9) SQL Error: 1213, SQLState: 40001
18:42:28,879 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-9) Deadlock found when trying to get lock; try restarting transaction
18:42:28,887 ERROR [org.keycloak.services.error.KeycloakErrorHandler] (default task-9) Uncaught server error: javax.persistence.OptimisticLockException: org.hibernate.exception.LockAcquisitionException: could not execute statement
        at org.hibernate.internal.ExceptionConverterImpl.wrapLockException(ExceptionConverterImpl.java:277)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:98)
        at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
        at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1594)
        at org.keycloak.storage.jpa.JpaUserFederatedStorageProvider.deleteAttribute(JpaUserFederatedStorageProvider.java:121)
        at org.keycloak.storage.jpa.JpaUserFederatedStorageProvider.setSingleAttribute(JpaUserFederatedStorageProvider.java:138)
        at org.keycloak.storage.adapter.AbstractUserAdapterFederatedStorage.setSingleAttribute(AbstractUserAdapterFederatedStorage.java:344)
        at de.dfb.keycloak.spi.userstorage.oauth.provider.DfbOAuthUser.setSingleAttribute(DfbOAuthUser.java:59)
        at org.keycloak.models.UserModelDefaultMethods.setFirstName(UserModelDefaultMethods.java:33)
        at de.dfb.keycloak.spi.userstorage.oauth.provider.DfbOAuthUser.<init>(DfbOAuthUser.java:25)
        at de.dfb.keycloak.spi.userstorage.oauth.provider.DfbOAuthUserStorageProvider.cacheBuildUser(DfbOAuthUserStorageProvider.java:45)
        at de.dfb.keycloak.spi.userstorage.oauth.provider.DfbOAuthUserStorageProvider.getUserById(DfbOAuthUserStorageProvider.java:99)
        at org.keycloak.storage.UserStorageManager.getUserById(UserStorageManager.java:388)
        at org.keycloak.models.cache.infinispan.UserAdapter.getUserModel(UserAdapter.java:403)
        at org.keycloak.models.cache.infinispan.DefaultLazyLoader.get(DefaultLazyLoader.java:42)
        at org.keycloak.models.cache.infinispan.entities.CachedUser.getRoleMappings(CachedUser.java:104)
        at org.keycloak.models.cache.infinispan.UserAdapter.getRoleMappings(UserAdapter.java:331)
        at org.keycloak.models.cache.infinispan.UserAdapter.getRealmRoleMappings(UserAdapter.java:280)
        at org.keycloak.services.resources.admin.RoleMapperResource.getRealmRoleMappings(RoleMapperResource.java:165)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:138)
        at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:543)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:432)
        at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$0(ResourceMethodInvoker.java:393)
        at org.jboss.resteasy.core.interception.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:358)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:395)
        at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:364)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:150)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:110)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:141)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:110)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:141)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:110)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:141)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:110)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:141)
        at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:104)
        at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:440)
        at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:229)
        at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:135)
        at org.jboss.resteasy.core.interception.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:358)
        at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:138)
        at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:215)
        at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:245)
        at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:61)
        at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)
        at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:74)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:129)
        at org.keycloak.provider.wildfly.WildFlyRequestFilter.lambda$doFilter$0(WildFlyRequestFilter.java:41)
        at org.keycloak.services.filters.AbstractRequestFilter.filter(AbstractRequestFilter.java:43)
        at org.keycloak.provider.wildfly.WildFlyRequestFilter.doFilter(WildFlyRequestFilter.java:39)
        at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:61)
        at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:131)
        at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:84)
        at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
        at io.undertow.servlet.handlers.ServletChain$1.handleRequest(ServletChain.java:68)
        at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
        at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.RedirectDirHandler.handleRequest(RedirectDirHandler.java:68)
        at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:132)
        at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
        at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
        at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:60)
        at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:77)
        at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
        at io.undertow.security.handlers.AbstractSecurityContextAssociationHandler.handleRequest(AbstractSecurityContextAssociationHandler.java:43)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at org.wildfly.extension.undertow.deployment.GlobalRequestControllerHandler.handleRequest(GlobalRequestControllerHandler.java:68)
        at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
        at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:269)
        at io.undertow.servlet.handlers.ServletInitialHandler.access$100(ServletInitialHandler.java:78)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:133)
        at io.undertow.servlet.handlers.ServletInitialHandler$2.call(ServletInitialHandler.java:130)
        at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:48)
        at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43)
        at org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105)
        at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
        at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
        at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
        at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1530)
        at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:249)
        at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:78)
        at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:99)
        at io.undertow.server.Connectors.executeRootHandler(Connectors.java:370)
        at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:830)
        at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
        at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1982)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.LockAcquisitionException: could not execute statement
        at org.hibernate.dialect.MySQLDialect$3.convert(MySQLDialect.java:547)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:100)
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:59)
        at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:109)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:453)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:378)
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1550)
        at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1603)
        at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1585)
        ... 95 more
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
        ... 103 more

I would expect that the user cache to be hit, instead of reloading the user multiple times. But that is evidently not the case, while - as far as I can tell - other calls would hit the cache.
Side note: Changing from MySQL to a different provider does not change the behavior.

The question

My question is basically: Why does this happen and how to get rid of it?
My vague hope is that this is something that can be fixed by smartly implementing the OnUserCache interface in my User Storage SPI, but I don’t quite understand how from just the available documentation - if that is at all a solution.

Just replying to note I think it’s a more elemental problem of using the same DB to fix synchronization issues. I have seem the same kind of error using an identity provider with forced update.