Thank you for reviewing.

DELETE statement would use the index just the same as the newly introduced SELECT, and would perform the job in a single shot instead of two separate statements,

The DELETE statement can cause deadlock even if target records don't exist. I added the SELECT query (findExpiredUserSessions) because deadlock never (0%) occurs if the DELETE statement is not issued. In addtion, I added query.setMaxResults(1); (it means limit 1 in SQL) to the SELECT statement since RDBMS quickly returns the result of the SELECT statement (=whether target records exist or not) just after RDBMS found a target record. So the SELECT statement positively affects the performance of the scheduled task in total.

hence the benefit of changing transaction isolation level also diminishes.

I have confirmed that the possibility of deadlock is decreased by changing transaction isolation level. There are no disadvantages of the changes because the priority of the scheduled task is lower than online threads. Even if the task fails to delete all expired offline records, there are no problems because the task is repeatedly executed.

As for the index, this is the crucial improvement that should get in, but only after https://issues.jboss.org/browse/KEYCLOAK-11908 is resolved

I understand. This PR suspends until KEYCLOAK-11908 is resolved.

Postpone this to "Hold" as it seems that we won't have time to look at KEYCLOAK-11908 before Keycloak 9.0. @hmlnarik are you ok with that?

@hmlnarik Feel free to correct me, but it looks that https://issues.redhat.com/browse/KEYCLOAK-11908 won't happen anytime soon? Hence I am adding this PR to the "Hold" state.

If we have contribution for the KEYCLOAK-11908, it will likely help with this PR as well. But I think the plan for this might need to be discussed on the ML first.

@k-tamura Thanks for the contribution. I am closing this PR due the fact that the PR probably won't happen anytime soon as the KEYCLOAK-11908 won't happen anytime soon as well. And we want to reduce the amount of pull requests, which is very big :) Feel free to re-open once we have KEYCLOAK-11908 or feel free to start discussion on keycloak-dev if you think that something should be improved in different manner. Sorry for late notice and Thanks for understanding.

Hi @hmlnarik , @stianst , @mposolda

I would like to re-open this ticket because KEYCLOAK-11908 has been merged. Could you review again?