[keycloak-user] DB deadlock for concurrent logins

[Marek Posolda]

I've did some investigation and found that you're right and we update 
password on every login. I've created JIRA for it: 
https://issues.jboss.org/browse/KEYCLOAK-5282 .

Fortunately there is a workaround. If you setup password policy as 
described in the "Workaround" in KEYCLOAK-5282 the password won't be 
updated on every login. Note that you need to set "Hash Iterations" to 
some non-default value like 27600. If you keep default 27500, it won't 
work due to the other bug: https://issues.jboss.org/browse/KEYCLOAK-5281

So hopefully after this, you won't see an issue anymore. Still the 
concurrent update of password probably should work without DB locking 
issues. So keeping the original JIRA you created open: 
https://issues.jboss.org/browse/KEYCLOAK-5272

Marek

On 10/08/17 13:36, Vikrant Singh wrote:
> 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
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user