Bug 83

Summary: ServerIndex shall implement retries for DB temporary errors
Product: Orthanc Reporter: Sébastien Jodogne <s.jodogne>
Component: Orthanc CoreAssignee: Sébastien Jodogne <s.jodogne>
Status: RESOLVED FIXED    
Severity: normal    
Priority: ---    
Version: unspecified   
Hardware: All   
OS: All   

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.