[keycloak-user] DB deadlock for concurrent logins

[Vikrant Singh]

Yes, but password migration should only be happening during first login. I
have checked the CREDENTIAL table in DB and user have already migrated to
new hashing algorithm and no of iterations, but still keycloak is trying to
run Update query for each login attempt.

-Vikrant

On Thu, Aug 10, 2017 at 4:18 PM, <keycloak-user-request at lists.jboss.org>
wrote:
>
>
> I believe that the default hashing algorithm has changed to SHA-256 as
> SHA-1 was recently in the news for being able to create hash
> collisions.  Because of this change, each login will update the password
> hash stored with the 1st login of the user.
>
> Not sure why MariaDB would deadlock.  Don't know enough about how that
> database performs locks.  It is trying to update a foreign key that has
> an index associated with it.  Maybe that has something to do with it.
>
>
>
> On 8/9/17 5:35 PM, Vikrant Singh wrote:
> > Hi,
> >
> > I am Running Keycloak 3.2.1.Final on openshift platform with MariaDB
> 10.2.7
> > for DB, recently upgraded from 3.1.0.Final.
> >
> > Deployment is consist of 3 keycloak servers along with 3 DB instances. As
> > part of kubernetes rediness check, a token is requested for a local user
> in
> > master realm every 10 sec. The concurrent token request for same user is
> > causing the deadlock exception in DB. Following is the exception being
> > logged in keycloak.
> >
> >
> > Caused by: java.sql.SQLException: Deadlock found when trying to get
> > lock; try restarting transaction
> >
> > Query is: select userentity0_.ID as ID1_71_,
> > userentity0_.CREATED_TIMESTAMP as CREATED_2_71_, userentity0_.EMAIL as
> > EMAIL3_71_, userentity0_.EMAIL_CONSTRAINT as EMAIL_CO4_71_,
> > userentity0_.EMAIL_VERIFIED as EMAIL_VE5_71_, userentity0_.ENABLED as
> > ENABLED6_71_, userentity0_.FEDERATION_LINK as FEDERATI7_71_,
> > userentity0_.FIRST_NAME as FIRST_NA8_71_, userentity0_.LAST_NAME as
> > LAST_NAM9_71_, userentity0_.REALM_ID as REALM_I10_71_,
> > userentity0_.SERVICE_ACCOUNT_CLIENT_LINK as SERVICE11_71_,
> > userentity0_.USERNAME as USERNAM12_71_ from USER_ENTITY userentity0_
> > where userentity0_.ID=? and userentity0_.REALM_ID=?, parameters
> > ['ddafa525-baae-4c40-98f8-08c25a23f2c6','master']
> >
> >       at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(
> LogQueryTool.java:146)
> >
> >       at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.
> executeQuery(AbstractQueryProtocol.java:221)
> >
> >       at org.mariadb.jdbc.MariaDbPreparedStatementClient
> .executeInternal(MariaDbPreparedStatementClient.java:218)
> >
> >       ... 76 more
> >
> >
> > Caused by: java.sql.SQLException: Lock wait timeout exceeded; try
> > restarting transaction
> >
> > Query is: update CREDENTIAL set ALGORITHM=?, COUNTER=?,
> > CREATED_DATE=?, DEVICE=?, DIGITS=?, HASH_ITERATIONS=?, PERIOD=?,
> > SALT=?, TYPE=?, USER_ID=?, VALUE=? where ID=?, parameters
> > ['pbkdf2-sha256',0,1501750736628,<null>,0,27500,0,<bytearray:???7'3^
> > .??LT???>,'password','ddafa525-baae-4c40-98f8-08c25a23f2c6','
> Hdpx8Zg5Ec8M9qVUp+Ylwlje+nhcGAzVPStF6/cvrqZghTeby048b8d3uqExfzS0of/
> 9Quwx9CROGKTC685Tpw==','5929a82b-542c-4597-b3eb-524d74e58919']
> >
> >       at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(
> LogQueryTool.java:146)
> >
> >       at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.
> executeQuery(AbstractQueryProtocol.java:221)
> >
> >       at org.mariadb.jdbc.MariaDbPreparedStatementClient
> .executeInternal(MariaDbPreparedStatementClient.java:218)
> >
> >       ... 78 more
> >
> >
> > Why keycloak is trying to update the user credential for every login.
> > and why is deadlock occurring? Any help truly appreciated.
> >
> >
> > Thanks,
> >
> > Vikrant
> > _______________________________________________
> > keycloak-user mailing list
> > keycloak-user at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/keycloak-user
>