<div dir="ltr">Another common source of table locks are delete operations in tables with foreign keys without index.<br></div><br><div class="gmail_quote">On Tue, May 5, 2015 at 2:49 PM Bill Burke <<a href="mailto:bburke@redhat.com">bburke@redhat.com</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
<br>
On 5/5/2015 1:31 PM, Marek Posolda wrote:<br>
> Unfortunately the test is failing on all RDBMS, not just H2 :-(<br>
><br>
> I did debugging with MySQL yesterday and saw that UserSessionEntity was<br>
> successfully deleted in chained backchannel logout request, but<br>
> transaction in original request failed due to foreign key<br>
> (UserSessionNote couldn't be added to already deleted UserSession)<br>
><br>
<br>
I have fixed this problem in my local repo. For OIDC I was checking the<br>
state of the UserSession to see if it was in a LOGGING_OUT state. If it<br>
was, I wouldn't delete the user session. I forgot to do this for SAML...<br>
<br>
Unfortunately, once I fixed this problem I ran into table locks:<br>
<br>
1. Application initiates logout<br>
2. Keycloak receives app request, adds a UserSession note (this locks<br>
the UserSessionEntity table)<br>
3. In same request, keycloak invokes a backchannel broker logout. Note,<br>
this request has not committed the current KeycloakSession and the<br>
UserSEssionEntity table is still locked<br>
4. Keycloak receives the broker backchannel logout request. Tries to<br>
delete the broker's UserSession which tries to delete all<br>
UserSessionNotes. UserSessionEntity table is already locked...BANG!<br>
Deadlock! fail!<br>
<br>
Table locks scare the shit out of me. They are a nightmare to fix and<br>
often can't be fixed without massive rearchitecture.<br>
<br>
In this situation though I believe I can fix the problem by committing<br>
the transaction in Step #3 before invoking the backchannel broker logout<br>
request. Then, restarting the session thereafter.<br>
<br>
<br>
--<br>
Bill Burke<br>
JBoss, a division of Red Hat<br>
<a href="http://bill.burkecentral.com" target="_blank">http://bill.burkecentral.com</a><br>
_______________________________________________<br>
keycloak-dev mailing list<br>
<a href="mailto:keycloak-dev@lists.jboss.org" target="_blank">keycloak-dev@lists.jboss.org</a><br>
<a href="https://lists.jboss.org/mailman/listinfo/keycloak-dev" target="_blank">https://lists.jboss.org/mailman/listinfo/keycloak-dev</a><br>
</blockquote></div>