I am running Keycloak 10.0.2 with MS SQL Server 2019. Users are created only at login from a brokered SAML IdP. When creating users in parallel (via Admin REST API or when happening parrallel logins of federated users from SAMl IdP), there are deadlocks such as: “Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
I tried to set the isolation level in the standalone-ha.xml with transaction-isolation TRANSACTION_READ_UNCOMMITTED, it did not help. I also tested all standard isloation levels in the SQL Server configuration, from “read uncommitted” up to “serializable”, nothing helped, the behaviour is always the same: When creating users in 2 or more parallel threads, users are not created; or users are created, but with incomplete attributes.
The exception stack traces show that the problem occurs when setting the default roles or updating user attributes into the database.
Are there any tips or best paractices how to get rid of this problem?
(Keycloak is running on Open JDK 11 on Ubuntu 18, 2 instances behind an Apache AJP proxy. No docker.)
After some investigation and some tests we set the SQL-Server parameter for “cost threshold for parallelism” to 50, which enforces SQL server to parallelize only really expensive transactions. This setting is much more suitable than the old default low value, which parallelizes almost every transaction which inceases the risk of deadlock. For the KC database itself, we enabled snapshot isolation. Both options lead to less locks on the tables (due to normalization, the keycloak user data model is split over many many different tables with n:m relations for roles, groups and IdP-links…) which reduces deadlock risk.
The main problem lies on the behaviour of the database and the hibernate/KC client: Deadlocks are normal, every DB can have them. SQL server has a deadlock monitor which detects them and rolls back the transactions. The SQL Server then returns the error to the client, expecting the client to handle the exception (that means: restart the transaction). But Hibernate seems to expect the database to solve the problem. This disagreement on how or where to handle deadlocks leads to the problems of inclomplete account creations (missing attributes, missing roles, missing IdP links,… => unusable accounts).
We observed this only when creating user accounts in parallel with users having automatically added roles, groups and federated IdP links. We solved this by tweaking the DB params mentioned above and by removing automated role adding to users (via an SAML attribute-to-role mapper).
The last step to deal with missing IdP links was to change the first-broker-login authenticator to an autolink behaviour: Using only “Create User If Unique” and “Automatically Set Existing User” (both as alternatives) in combination with allowed email duplicates on realm level did the trick.
All these things helped in combination, but the core problem lies in the different expectations on how to handle DB deadlocks, and who has to handle them. To solve this, there could be more fault tolerance or store-retries in Hibernate/KC when writing to the DB. Or simply the usage of another DB. Which may leads to other problems…
