Conflicts on frequent conditional update

[pavlushkin]

Question

Aidbox Version: stable v:2302-lts.829cd38d
AWS RDS Engine version 14.8

Scenario:

• load several thousand resources from an external EHR system
• use conditional update to create or update resources by an identifier and prevent duplications PUT /Encounter?identifier=abc
• run these tasks concurrently for different patients

Result:

• while concurrent run a significant count of errors are thrown:

{
  "resourceType": "OperationOutcome",
  "id": "resource-has-been-changed",
  "text": {
    "status": "generated",
    "div": "Requested resource has been changed since the start of transaction"
  },
  "issue": [
    {
      "severity": "fatal",
      "code": "conflict",
      "diagnostics": "Requested resource has been changed since the start of transaction"
    }
  ]
}

I am struggle to identify an exact bottleneck here.
Do you have ideas how to fix this?

[milaryz]

This problem was fixed in 2304. If you don't use multi-instance Aidbox, then set the

BOX_CACHE_REPLICATION_DISABLE=true

environment variable (or the equivalent option in the Aidbox configuration project).

Details:
in Aidbox prior to 2304 there was a _cache_bigint table, which was mostly used for the Changes API. This table stored latest version id per resource type. To maintain this table, Aidbox updated it during every PUT/POST/DELETE query. For example, if you had created two Patient resources, Aidbox would have updated the single row in the _cache_bigint table two times.

For conditional operations and transaction Aidbox uses SERIALIZABLE transaction isolation level. This is essential to enforce the absence of duplicates. In this level Postgres can reject transactions to prevent serialization anomalies. https://www.postgresql.org/docs/current/transaction-iso.html

Concurrent updates of the same row of the _cache_bigint table were often the reason for Postgres to reject transactions.

In older versions of Aidbox BOX_CACHE_REPLICATION_DISABLE=true disabled use of the _cache_bigint table (it was only needed for the multi-instance scenario). From 2304 Aidbox does not use the _cache_bigint table.

---

Regarding queries, Aidbox shows queries only for Search API when you use AIdbox format or _explain parameter. You can enable log_statement=all Postgres option to see full SQL queries in the Postgres logs. But this option could significantly reduce Postgres performance.

[milaryz]

The issue has been resolved.