Here I have used Label RETRY at the beginning of both the transactions. PRINT ' Rollback Transaction' ROLLBACK TRANSACTION IF ERROR_NUMBER() = 1205 - Deadlock Error Number BEGIN WAITFOR DELAY ' 00:00:00.05' - Wait for 5 ms GOTO RETRY - Go to Label RETRY END END CATCH UPDATE Orders SET ShippingId = 12 Where OrderId = 221 WAITFOR DELAY ' 00:00:05' - Wait for 5 ms UPDATE Customer SET FirstName = ' Mike' WHERE CustomerId=111 RETRY: - Label RETRY BEGIN TRANSACTION BEGIN TRY The below example shows the deadlock situation between the two transactions. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa neither transaction can complete until the other transaction releases locks. Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. This article will explain how to handle deadlocks in a user-friendly way. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. Or simply the usage of another DB.A deadlock is a situation wherein two transactions wait for each other to give up their respective locks. To solve this, there could be more fault tolerance or store-retries in Hibernate/KC when writing to the DB. 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.Īll 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. 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). We observed this only when creating user accounts in parallel with users having automatically added roles, groups and federated IdP links. 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). But Hibernate seems to expect the database to solve the problem. The SQL Server then returns the error to the client, expecting the client to handle the exception (that means: restart the transaction). SQL server has a deadlock monitor which detects them and rolls back the transactions. The main problem lies on the behaviour of the database and the hibernate/KC client: Deadlocks are normal, every DB can have them. 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. For the KC database itself, we enabled snapshot isolation. This setting is much more suitable than the old default low value, which parallelizes almost every transaction which inceases the risk of deadlock. No docker.)Īfter 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. (Keycloak is running on Open JDK 11 on Ubuntu 18, 2 instances behind an Apache AJP proxy. The exception stack traces show that the problem occurs when setting the default roles or updating user attributes into the database.Īre there any tips or best paractices how to get rid of this problem? 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. I tried to set the isolation level in the standalone-ha.xml with transaction-isolation TRANSACTION_READ_UNCOMMITTED, it did not help. 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. Users are created only at login from a brokered SAML IdP. I am running Keycloak 10.0.2 with MS SQL Server 2019.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |