Bug 83 - ServerIndex shall implement retries for DB temporary errors
Summary: ServerIndex shall implement retries for DB temporary errors
Status: RESOLVED FIXED
Alias: None
Product: Orthanc
Classification: Unclassified
Component: Orthanc Core (show other bugs)
Version: unspecified
Hardware: All All
: --- normal
Assignee: Sébastien Jodogne
URL:
Depends on:
Blocks:
 
Reported: 2020-06-29 15:13 CEST by Sébastien Jodogne
Modified: 2021-04-21 12:03 CEST (History)
0 users

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Sébastien Jodogne 2020-06-29 15:13:35 CEST
[BitBucket user: Alain Mazy]
[BitBucket date: 2018-03-08.11:32:04]

this should be useful to handle transient errors from PostgreSQL and, also to handle transaction failure that appeared since we have set PostgreSQL transaction SERIALIZABLE.

Here are the error we get:

```
E0308 12:18:56.723180 PluginsManager.cpp:164] Exception in database back-end: Error in PostgreSQL: ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during conflict in checking.
HINT:  The transaction might succeed if retried.

E0308 12:18:56.724748 ServerIndex.cpp:834] EXCEPTION [The plugin implementing a custom database back-end does not fulfill the proper interface]
E0308 12:18:56.730711 ServerContext.cpp:283] Store failure
```

To reproduce the above from above:

- configure 2 orthancs on the same PG DB
- add a nginx load balancer in front of them
- upload multiple files through the web interface of the load-balanced orthancs

This can be reproduced with a setup similar with https://bitbucket.org/osimis/orthanc-setup-samples/commits/9be3fcd76d02dc5d9d1dd327f96d3d3aa3ac3632 (just remove the GET limitations on the load balancer)


Note that, another alternative to retrying transactions that failed because they were SERIALIZED is to make the SQL code more robust by using "insert or update" pattern.  I.e, with PG, we may use the `ON CONFLICT` action (https://www.postgresql.org/docs/9.5/static/sql-insert.html)
Comment 1 Sébastien Jodogne 2020-06-29 15:23:27 CEST
[BitBucket user: Alain Mazy]
[BitBucket date: 2019-01-08.16:38:25]

related issue: bug 121
Comment 2 Sébastien Jodogne 2021-04-21 12:03:28 CEST
Handling of collisions by replaying database transactions is implemented by the "Orthanc::StatelessDatabaseOperations" class since Orthanc 1.9.2.