Bug Report: Duplicate Data can be added to the database

[markscamilleri]

Bug Discovered

The backend was not starting because in one of its initialization checks, it found multiple records for the role/user/permission that it selected.

More details

It turns out that when starting the kubernetes instances (n instances), k of n instances such that 1 <= k <= n start running concurrently, with n-k instances waiting to be run.

For these k instances, each running concurrently, reads the database to find there is no data. These k instances run the data initialization script to populate the database - resulting in repeated initial data.

Possible Ramifications

Considering that these are 5 servers accessing the same database:

• Sending CREATE requests multiple times at the same time could result in the same query being executed multiple times
• Sending UPDATE requests multiple times could result in a race condition (if updating the same record with different values).
• Sending DELETE requests multiple times should not be affected. But if combined with an UPDATE pr READ could create a race condition.
• Sending READ requests multiple times should not be affected - or it might show slightly older data.

Solution carried out:

The data was deleted and 1 server was started. Then, when the data was initialized, the other 4 were started.

Proposed Solution:

For initial data:

1. Enforce Database Constraints in the database itself - the best way to do that is to have a DDL script that we can run independently - will JPA allow this?
2. Initial data should not be added by the server but by an initialization .sql file which we run when we need to initialize the databases.

For concurrency:

1. Ensuring that requests are idempotent and use some form of nonce to only be handled once - this way, if a request is repeated with a nonce, we don't perform the request? (Open for discussion)