Failed WB upload with auto-numbering when multiple users active in database

[bronwyncombs]

Describe the bug
From Robyn:

We have noticed a change in the behaviour of the workbench tool. We are now finding that we frequently get an error where certain rows can’t be successfully validated as the catalogNumber is not unique (error below).

('Collectionobject must have unique catalognumber in collection', {'table': 'Collectionobject', 'localizationKey': 'childFieldNotUnique', 'fieldName': 'catalognumber', 'fieldData': {'catalognumber': '01485878'}, 'parentField': 'collection', 'parentData': {'collection': 'Collection object (4)'}, 'conflicting': [1485878]})

We are finding that instead of being able to validate and import records during the work day we are now having to wait until almost all staff are not working in the system.

This seems to be a new behaviour, as until recently we were able to work in the workbench whilst we had other users in the system, if the data set was relatively small (we still have issues with large datasets tying up the system and causing errors for other users). Is this something that has been introduced in an update? Ideally we would like to be able to at least successfully validate sets whilst we have staff in the system.

From what I can tell, the improvements to business rules have caused the worker to disallow concurrent catalog number creations with WB and Data Entry. For RBGE, this means uploading must wait until after business hours which is not ideal. I had a successful upload during testing using edge, but there was still an error received. Once they are updated to 7.9.4, I will get an update on the behavior they experience.

Also, while Robyn did not report any errors or issues for the other users, I did encounter an error and the inability to save new forms in testing. (see additional context for my testing notes)

To Reproduce
Steps to reproduce the behavior:

1. Go to WorkBench data set with auto-numbered column
2. validate/upload
3. While this is running, log in to the database with another user
4. With the second user, attempt to create a CO and save
5. See error for one/both users

Reported By
Robyn @ RBGE

Additional context
I tested this on test panel with v7 and edge on herb_rbge_20240419
Using sp user Robyn as uploader, and spadmin as secondary user in database

production:

• upload pauses after spadmin clicks save and until spadmin receives a 502 bad gateway
• upload resumes after the error is received for spadmin, uploader receives 421 but upload appears to continue, however, the countdown starts to go up and it is clear the upload will not progress.

edge:

• upload pauses after spadmin clicks save, both freeze for a few minutes before getting 421 failed to fetch error
• spadmin form is not saved, uploader has a successful upload despite the crash

[emenslin]

Can recreate in edge (7.9.6)

[grantfitzsimmons]

From Robyn on Oct 7:

When validating an import it will fail if there are other users in the system creating new records as a catalog number is allocated during the validation process, which can match one created by a user. This is causing us a lot of issues getting data imported in to Specify, as if there are active users a dataset that is good to be imported with fail on the catalog number. We are struggling to get our data in during working hours, in part due to this.

This should be resolved as soon as we can.