[keycloak-user] DB deadlock for concurrent logins

[Vikrant Singh]

Thanks a lot, will try the workaround and see if it helps.

On Thu, Aug 10, 2017 at 7:48 PM, Marek Posolda <mposolda at redhat.com> wrote:

> 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.exceptionWithQue
>>>> ry(
>>>>
>>> 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.exceptionWithQue
>>>> ry(
>>>>
>>> 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
>>
>
>
>