[keycloak-user] DB deadlock for concurrent logins

[Bill Burke]

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