Race condition in ingestion stored procedures when a resource is erased

[punktilious]

Describe the bug Before the $erase operation was implemented there was no way a logical resource record could be deleted by the server. The create/update locking mechanism in the add_any_resource stored procedures relies on this fact to simplify getting the logical resource lock which is required to protect the various updates to the other tables.

Now that $erase has been implemented, it is possible for a logical resource record to be deleted, so the the create/update logic needs to be more sophisticated to take this into account. To be 100% effective, a loop must be implemented to guarantee that a lock can be obtained in all the scenarios.

The probability of hitting this issue is low, and the impact would be a an exception causing the transaction to be rolled back.

Environment Which version of IBM FHIR Server? 4.11.0 main

To Reproduce

1. Run concurrent create/update/erase for the same resource

Expected behavior Erase should not trigger any failures of create/update calls.

Additional context Needs to be implemented for Derby, PostgreSQL, PostgreSQL (no proc version).