[JIRA] (IDP-1951) Add optional (per node_ table locking for the JDBCPairwiseIdStore

[Rod Widdowson (Jira)]

Rod Widdowson ( https://shibboleth.atlassian.net/secure/ViewProfile.jspa?accountId=59fb32bbc24efb3c4ed3c977 ) *created* an issue

Identity Provider ( https://shibboleth.atlassian.net/browse/IDP?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 ) / Task ( https://shibboleth.atlassian.net/browse/IDP-1951?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 ) IDP-1951 ( https://shibboleth.atlassian.net/browse/IDP-1951?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 ) Add optional (per node_ table locking for the JDBCPairwiseIdStore ( https://shibboleth.atlassian.net/browse/IDP-1951?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 )

Issue Type: Task Affects Versions: 4.1.0, 4.2.0 Assignee: Rod Widdowson ( https://shibboleth.atlassian.net/secure/ViewProfile.jspa?accountId=59fb32bbc24efb3c4ed3c977 ) Components: Attribute Resolver Created: 24/May/22 9:13 AM Fix Versions: 4.2.2, 5.0.0 Priority: Trivial Reporter: Rod Widdowson ( https://shibboleth.atlassian.net/secure/ViewProfile.jspa?accountId=59fb32bbc24efb3c4ed3c977 )

During development of the JDBC Storage service we hit issues with transactional retry limits for both the un memory database (HSQLDB) and SQLServer.

It appears that these days it is a better solution to fail a request because you couldn’t get the lock you needed and force the application to retry rather than always grab the locks you need before you start (things get faster but every application in the world now has to code up a retry). Both the JDBCStorageService and the JDBCPairwiseIdStore use this (alongside a reuest for ful transactional isolation) but it doesn’t take too much load before we have to have retry counts in the 10s.

To alleviate this we added an optional ReadWriteLock to the system. On a single node IdP this means that the database doesn't need to worry about transactional isolation and on a multi node implementation the contention will be much reduced.

Against this is the fear that if an SQL operation just hangs we have a table lock left dangling. There are reasonably contorted solutions to this (involving locks with timeouts and separate threads) but for now the idea is to add this lock as an option for the end user.

( https://shibboleth.atlassian.net/browse/IDP-1951#add-comment?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 ) Add Comment ( https://shibboleth.atlassian.net/browse/IDP-1951#add-comment?atlOrigin=eyJpIjoiNjc4ODNlOGNiYTEwNDJjMjgyYTUxZGUyOGNlOTQ2MzQiLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100198- sha1:faaaa62 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://shibboleth.net/pipermail/commits/attachments/20220524/8a7c84fe/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-comment-icon-ed7f25c7-e33d-4ecc-88b8-a03124ede4b7
Type: image/png
Size: 1084 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220524/8a7c84fe/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-trivial-f07c2624-4813-4e96-8f70-46ccd6670dee
Type: image/png
Size: 563 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220524/8a7c84fe/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-static-footer-desktop-logo-835ddae3-68ff-40a1-8c5c-895aeb61287f
Type: image/png
Size: 10805 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220524/8a7c84fe/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: jira-generated-image-avatar-ef08e96c-ca62-40a9-93bb-bfe15caf0c3a
Type: image/png
Size: 468 bytes
Desc: not available
URL: <http://shibboleth.net/pipermail/commits/attachments/20220524/8a7c84fe/attachment-0007.png>