[keycloak-user] DB deadlock for concurrent logins

[Vikrant Singh]

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