Multiple requisitions for same facility/period created if users initiate a requisition simultaneously

Description

It is possible to create two or more regular requisitions for the same program, facility and period if users initiate a requisition in the same time. It is caused by a delay between the check for already existing requisition for the given program/facility/period and creation of the requisition in the database.

One possible solution would be to raise the transaction isolation level for the requisition initiate, such that all transactions for initiate happen in order. This may however affect performance a lot. Another solution would be introduce an additional constraint on the database level, which may be a better solution, based on the fact that the described scenario is an edge case.

Acceptance criteria:

  • when users click initiate for the same period/facility/program at the same time only one requisition is created

  • appropriate repository integration tests (if DB constraint used)

  • if the DB changes, any built reports should be updated / notified that they might break with this change

Environment

None

Activity

Show:
Josh Zamor
August 15, 2017, 8:02 PM

: FYI when this is done, Malawi reports could very well break. If you could coordinate the investigation of weather this is true for Malawi, and your acceptance testing of 3.2, that'd be very helpful. Read my proposal above on how this should be fixed. From that the field you'll need to check Malawi report usage for is the one titled "emergency" in the requisitions table.

Ben Leibert
August 15, 2017, 9:12 PM

Will do. Thank you very much, , for highlighting the potential schema change.

Paweł Gesek
August 17, 2017, 10:08 AM
Edited

pretty sure most mainstream databases work like that, I'm pretty sure MySQL does. If we actually want to take advantage of PostgreSQL, how about a unique index with a condition? https://stackoverflow.com/questions/16236365/postgresql-conditionally-unique-constraint

This would allows us to avoid any db changes which I think is a big plus for us and Malawi, but it's not the prettiest thing. But hey, always using null instead of false is not that pretty as well.

Paweł Gesek
August 17, 2017, 11:15 AM

Note: this is estimated at 1, but if we have to change the column name and do migrations and update reports etc. then this probably goes up to a 3

Paweł Gesek
August 17, 2017, 5:14 PM

Root cause: we never had this check - we should pay more attention to our db definitions. In the end, the db must force its own integrity.

Done

Assignee

Paweł Gesek

Reporter

Sebastian Brudziński

Labels

None

Story Points

1

Time tracking

0m

Time remaining

0m

Components

Sprint

None

Fix versions

Priority

Critical